Skip to content
Search

PostgreSQL:Function

lastmod 2026-05-12created 2021-05-0220 edits

NULLIF 

NULLIF(exp1, exp2)
  • Returns NULL if exp1 and exp2 are equal, otherwise returns exp1
  • CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END

Checking the current Role/User 

select current_role;
select current_user;

DO

When developing/testing a Function, you sometimes want to check Function syntax/features. Creating a Function, testing it, and then deleting the Function is very inefficient; the DO feature/SQL command is what you use in this situation.

You can think of DO as a temporary Function with no Parameter that returns void. 

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

COALESCE

Used for Null checks. 

COALESCE(param1, param2)

If the column is null, the substitute value is returned instead. (It can be substituted with another column.)

Example

Column 

coalesce(name, id) as name

Specified string 

coalesce(name, 'No Data') as name

null 

coalesce(name, null) as name

json 

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

Trigger Functions

Trigger function security summary

Basic principle: Trigger functions run as SECURITY INVOKER by default. Not every trigger function needs SECURITY DEFINER.

When SECURITY DEFINER is needed

When the function must query/modify other tables or bypass RLS:

Function

Reason

handle_new_org()

INSERT into the org_members table (needs to bypass RLS)

prevent_last_owner_removal()

COUNT query on the org_members table

prevent_last_owner_downgrade()

COUNT query on the org_members table

In this case you must also set SET search_path = '' (to prevent search_path injection).

When SECURITY DEFINER is not needed

When the function only manipulates the NEW/OLD records of its own table:

Function

Reason

handle_orgs_update()

Uses only NEW/OLD (immutable protection)

handle_org_members_update()

Uses only NEW/OLD (immutable protection)

handle_sysconf_update()

Uses only NEW/OLD (immutable protection)

These functions do not access external tables, so the default SECURITY INVOKER is sufficient.

Calling trigger functions via Supabase RPC

Trigger functions cannot be called via RPC.

Reason: The return type of a trigger function is 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')) internally runs SELECT function_name(), but a RETURNS TRIGGER function can only run in a trigger context (NEW, OLD, TG_OP, etc.).

Functions callable via RPC: Only those with ordinary return types such as RETURNS void, RETURNS json, RETURNS setof record.

Therefore the trigger functions in the current project carry no risk of being called directly via RPC. Even functions with SECURITY DEFINER set run only through trigger events.

CREATE FUNCTION

The syntax alone looks rather complicated: 

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

The security options are as follows.

  • Runs with the privileges of the caller, not the function creator - security invoker (default)
  • Runs with the privileges of the function creator - 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()
$$;

Function volatility classification

See PostgreSQL:Volatility for details.

See also

Favorite site

author Zer0 sitemap