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

트리거 함수 보안 정리

기본 원칙: 트리거 함수는 기본적으로 SECURITY INVOKER로 실행됩니다. 모든 트리거 함수에 SECURITY DEFINER가 필요한 것은 아닙니다.

SECURITY DEFINER가 필요한 경우

다른 테이블을 조회/수정하거나 RLS를 우회해야 하는 경우:

함수

이유

handle_new_org()

org_members 테이블에 INSERT (RLS 우회 필요)

prevent_last_owner_removal()

org_members 테이블을 COUNT 조회

prevent_last_owner_downgrade()

org_members 테이블을 COUNT 조회

이 경우 반드시 SET search_path = ''을 함께 설정해야 합니다 (search_path injection 방지).

SECURITY DEFINER가 불필요한 경우

자기 자신 테이블의 NEW/OLD 레코드만 조작하는 경우:

함수

이유

handle_orgs_update()

NEW/OLD만 사용 (immutable 보호)

handle_org_members_update()

NEW/OLD만 사용 (immutable 보호)

handle_sysconf_update()

NEW/OLD만 사용 (immutable 보호)

이런 함수는 외부 테이블 접근이 없으므로 기본 SECURITY INVOKER로 충분합니다.

트리거 함수의 Supabase RPC 호출

트리거 함수는 RPC로 호출할 수 없습니다.

이유: 트리거 함수의 반환 타입이 TRIGGER이기 때문입니다. 

-- 트리거 함수 시그니처
CREATE FUNCTION handle_new_org() RETURNS TRIGGER ...

-- Supabase RPC 호출 시
SELECT public.handle_new_org();
-- ERROR: trigger functions can only be called as triggers

Supabase RPC(supabase.rpc('function_name'))는 내부적으로 SELECT function_name()을 실행하는데, RETURNS TRIGGER 함수는 트리거 컨텍스트(NEW, OLD, TG_OP 등)에서만 실행 가능합니다.

RPC로 호출 가능한 함수: RETURNS void, RETURNS json, RETURNS setof record 등 일반 반환 타입만 가능합니다.

따라서 현재 프로젝트의 트리거 함수들은 RPC를 통한 직접 호출 위험이 없습니다. SECURITY DEFINER가 설정된 함수라도 트리거 이벤트를 통해서만 실행됩니다.

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()
$$;

함수의 변동성(volatility) 분류

세 가지 분류:

  • VOLATILE - 호출할 때마다 결과가 달라질 수 있음 (기본값)
  • STABLE - 같은 트랜잭션 내에서 동일 인자 → 동일 결과 보장
  • IMMUTABLE - 언제나 동일 인자 → 동일 결과 보장

STABLE

  • 함수가 DB를 수정하지 않고, 같은 트랜잭션 안에서는 결과가 변하지 않음을 선언
  • 플래너가 이를 믿고 불필요한 재호출을 생략하는 최적화를 할 수 있음
  • 대표적 예: NOW(), current_user, 테이블을 SELECT만 하는 조회 함수 
CREATE FUNCTION get_org_name(org_id UUID)
  RETURNS TEXT
  LANGUAGE sql
  STABLE          -- 같은 트랜잭션 내에선 org 이름이 안 바뀜
AS $$
  SELECT name FROM orgs WHERE id = org_id;
$$;

주의:

  • STABLE 함수는 인덱스 표현식의 DEFAULT 값으로 사용 불가 (IMMUTABLE만 가능)
  • 잘못 분류하면 플래너가 캐싱된 결과를 재사용해서 틀린 결과를 줄 수 있으므로, 실제 동작에 맞게 선언해야 합니다.

See also

Favorite site