Skip to content

PostgreSQL:Function

NULLIF 

NULLIF(exp1, exp2)
  • exp1값과 exp2값이 동일하면 NULL을 그렇지 않으면 exp1을 반환
  • CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END

현재 접속한 Role/User 확인 

select current_role;
select current_user;

DO

Function 을 개발/테스트할때 Function 문법/기능을 확인하고 싶을때가 있다. Function 을 생성하고 테스트한 뒤 Function 을 삭제하는 것은 너무 비효율적이며 이런 상황에서 사용하는 것이 DO 기능/SQL명령어이다.

DO 는 Parameter가 없고 return void 방식의 임시 Function 이라고 생각하면 될 것 같다. 

do $$
begin
    raise notice 'NOW : %', current_timestamp; 
end $$

COALESCE

Null 체크할 때 사용한다. 

COALESCE(param1, param2)

대체 값은 컬럼이 null인 경우 대체 값으로 반환한다. (다른 컬럼으로 대체할 수 있다)

Example

컬럼 

coalesce(name, id) as name

지정 문자열 

coalesce(name, 'No Data') as name

null 

coalesce(name, null) as name

json 

coalesce(name, '{}')::json as name

Trigger Functions

CREATE FUNCTION

문법만 보면 뭔가 복잡하다: 

CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
    [ RETURNS rettype
      | RETURNS TABLE ( column_name column_type [, ...] ) ]
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, ... ]
    | WINDOW
    | { IMMUTABLE | STABLE | VOLATILE }
    | [ NOT ] LEAKPROOF
    | { CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT }
    | { [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER }
    | PARALLEL { UNSAFE | RESTRICTED | SAFE }
    | COST execution_cost
    | ROWS result_rows
    | SUPPORT support_function
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
    | sql_body
  } ...

Increment an integer, making use of an argument name, in PL/pgSQL: 

CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
        BEGIN
                RETURN i + 1;
        END;
$$ LANGUAGE plpgsql;

Return a record containing multiple output parameters: 

CREATE FUNCTION dup(in int, out f1 int, out f2 text)
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

SELECT * FROM dup(42);

security option

security 옵션은 다음과 같다.

  • 함수 생성자가 아닌 호출자 권한으로 수행 - security invoker (디폴트)
  • 함수 생성자 권한으로 수행 - security definer

Example: 

-- 3.  Create security definer function, which should be run as "postgres"
create function private.get_teams_for_authenticated_user()
returns setof bigint
language sql
security definer -- 실행 권한을 실행한 측이 아닌 함수 정의한 측(definer)로 지정.
set search_path = public -- 검색 경로 'public' schema 로 제한
stable -- 내용을 수정하지 않는다.
as $$
  select team_id
  from members
  where user_id = auth.uid()
$$;

See also

Favorite site