Skip to content
Search

PL/pgSQL

lastmod 2025-12-10created 2021-05-0133 edits

PL/pgSQL (Procedural Language/PostgreSQL) is a procedural programming language supported by the PostgreSQL ORDBMS. It closely resembles Oracle's PL/SQL language. Implemented by Jan Wieck, PL/pgSQL first appeared with PostgreSQL 6.4, released on October 30, 1998. Version 9 also implements some ISO SQL/PSM features, like overloading of SQL-invoked functions and procedures.

Overview

Basic structure of the language

PL/pgSQL is a block-structured language. The function body must be in the form of a block. A block is defined as follows. 

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
END [ label ];

Dollar-Quoted String Constants

Dollar quoting ($$ ... $$) lets you write code or queries that span multiple lines.

The start and end must be quoted with the same label, as in $function$ ... $function$.

Example of nested quoting: 

$function$
BEGIN
    RETURN ($1 ~ $q$[\t\r\n\v\\]$q$);
END;
$function$

Variables and constants 

DO $$ 
DECLARE
   age integer := 40;
   korean_name varchar(10) := '김형준';
   alias_name varchar(50) := 'Dip2K';
   weight numeric(3,1) := 65.5;
BEGIN 
   RAISE NOTICE '%의 별명은 % 이고 나이는 %이며 몸무게는 %입니다.', 
       korean_name, alias_name, age, weight;
END $$;

Conditional Expressions

Control Structures

RETURN

IF and CASE statements let you execute alternative commands based on certain conditions. PL/pgSQL has three forms of IF:

  • IF ... THEN ... END IF
  • IF ... THEN ... ELSE ... END IF
  • IF ... THEN ... ELSIF ... THEN ... ELSE ... END IF

and two forms of CASE:

  • CASE ... WHEN ... THEN ... ELSE ... END CASE
  • CASE WHEN ... THEN ... ELSE ... END CASE

Loops

  • LOOP, EXIT, CONTINUE, WHILE, FOR, and FOREACH

Data Types

Function example 

CREATE OR REPLACE FUNCTION get_age_level(part_name CHAR(20)) 
RETURNS CHAR(20) AS $$
DECLARE 
    the_age INTEGER;
BEGIN
    SELECT INTO the_age age
    FROM person
    WHERE name LIKE '%' || part_name || '%';

    CASE
        WHEN the_age < 10 THEN
            RETURN '어린아이';
        WHEN the_age >= 10 AND the_age < 20 THEN
            RETURN '10대 사춘기';
        WHEN the_age >= 20 AND the_age < 30 THEN
            RETURN '20대 취준생';
        WHEN the_age >= 30 AND the_age < 40 THEN
            RETURN '30대 청춘';        
        ELSE
            RETURN '불노장생';
    END CASE;
END; $$
LANGUAGE plpgsql;

How to omit RETURNS

Use RETURNS void. 

CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$
    #variable_conflict use_variable
    DECLARE
        curtime timestamp := now();
    BEGIN
        UPDATE users SET last_modified = curtime, comment = comment
          WHERE users.id = id;
    END;
$$ LANGUAGE plpgsql;

How to return a table

Use RETURNS SETOF 테이블이름.

INSERT RETURNING 

INSERT INTO table(id,field)
VALUES($id,$value)
RETURNING id into var;

Or 

var := (INSERT INTO table(id,field) VALUES($id,$value) RETURNING id);

FOREACH

Syntax and semantics: 

[ <<label>> ]
FOREACH var [ SLICE non_negative_integer_literal ] IN ARRAY expression LOOP
  statements
END LOOP [ label ];

Looping over the values in an array without the SLICE keyword

Concatenating arrays: 

do $body$
declare
  arr1 int[] := array[1, 2];
  arr2 int[] := array[3, 4, 5];
  var int;
begin
  <<"FOREACH eaxmple">>
  foreach var in array arr1||arr2 loop
    raise info '%', var;
  end loop "FOREACH eaxmple";
end;
$body$;

Decomposing elements: 

create type rt as (f1 int, f2 text);
-- ...
arr rt[] := array[a1, a2];
-- ...
  foreach f1, f2 in array arr loop
    raise info '% | %', f1::text, f2;
  end loop;

How to exit immediately in the middle of a function

Use return; at the position where you want to exit: 

create or replace function my_func()
returns setof int language plpgsql as $$
begin
    return query select generate_series(1,2);
    return;
    return query select generate_series(3,4);
end $$;

How to INSERT a record directly 

DO LANGUAGE PLPGSQL $$
DECLARE
data forall_data;  --- or data forall_data%rowtype 

BEGIN

FOR data IN SELECT * FROM forall_data
LOOP
INSERT INTO for_loop select (data).*;
END LOOP;
END;
$$

Whether this works still needs to be tested.

How to return with defined return columns 

CREATE OR REPLACE FUNCTION get_persons(v INT)
RETURNS TABLE (f_name CHAR(20), f_age INT)
AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN (SELECT name, age 
             FROM person
             WHERE age >= v)
     LOOP
         f_name := r.name || '(' || r.age || ')';
         f_age := r.age - 1;
         RETURN NEXT;
     END LOOP;
END; $$
LANGUAGE 'plpgsql';

Returning results that reference a specific table

Use it as follows: 

create function increase_progress_value(progress_id uuid, increase_value integer default 10)
returns setof progress as
$$
begin
  return query update progress set value = value + increase_value where id = progress_id returning *;
end;
$$
language plpgsql;

When calling rpc from supabase-py, a row must be returned.

  • returns setof progress - returns by referencing the table named progress.
  • return query ... - returns the query result.

If you call it as follows: 

select * from increase_progress_value('451c45d4-290c-4fe9-9a53-bf3355314067'::uuid, 1);

the output is similar to the result of update ... returning *;.

How to return a record 

create function increase_progress_value(progress_id uuid, increase_value integer default 10)
returns table as
$$
declare
  row record;
begin
  update progress set value = value + increase_value where id = progress_id returning * into row;
  return row;
end;
$$
language plpgsql;

If you output it as follows: 

select increase_progress_value('451c45d4-290c-4fe9-9a53-bf3355314067'::uuid);

the following result is returned as a single column: 

(451c45d4-290c-4fe9-9a53-bf3355314067,,65,"2024-02-08 12:42:43.503557+00","2024-02-07 12:42:43.503557+00","2024-02-08 09:56:08.025791+00")

Returning permissions according to Answer's administrator/group/project conditions 

CREATE OR REPLACE FUNCTION recc_appropriate_permission (
    u_uid INTEGER,
    g_uid INTEGER,
    p_uid INTEGER DEFAULT NULL
)
    RETURNS SETOF recc_permission
    LANGUAGE plpgsql
AS $function$
DECLARE
    r_uid INTEGER := NULL;
    is_admin BOOLEAN := FALSE;
BEGIN
    SELECT u.is_admin
    INTO is_admin
    FROM recc_user u
    WHERE u.uid=u_uid;

    IF is_admin THEN
        -- Administrator has full control.
        RETURN QUERY
        SELECT *
        FROM recc_permission;
        RETURN;
    END IF;

    IF NOT p_uid ISNULL THEN
        -- First make sure you are a member of the project.
        SELECT role_uid
        INTO r_uid
        FROM recc_project_member
        WHERE user_uid=u_uid AND project_uid=p_uid;
    END IF;

    IF r_uid ISNULL THEN
        -- If you are not a project member, check the group member.
        SELECT role_uid
        INTO r_uid
        FROM recc_group_member
        WHERE user_uid=u_uid AND group_uid=g_uid;
    END IF;

    RETURN QUERY
    SELECT *
    FROM recc_permission
    WHERE uid IN (
        SELECT permission_uid
        FROM recc_role_permission
        WHERE role_uid=r_uid
   );
END;
$function$;

Function to store a4y device state/chart information 

CREATE OR REPLACE FUNCTION add_a4y_state(
    state_time TIMESTAMPTZ,
    state_fw_ver INTEGER,
    state_uid INTEGER,
    state_pm10 INTEGER,
    state_pm2_5 INTEGER,
    state_co2 INTEGER,
    state_humidity INTEGER,
    state_temperature INTEGER,
    state_voc INTEGER,
    state_mode INTEGER,
    state_power_state INTEGER,
    state_fan_control INTEGER,
    state_lock INTEGER,
    state_filter INTEGER,
    state_filter_life INTEGER,
    state_uv_led INTEGER,
    state_time_reservation INTEGER,
    state_sleep_mode INTEGER
)
    RETURNS VOID
    LANGUAGE plpgsql
AS $function$
DECLARE
    current_days TIMESTAMPTZ;
    current_hours TIMESTAMPTZ;
    days RECORD;
    hours RECORD;
    days_alpha real;
   hours_alpha real;
  days_pm10 real;
begin
    current_days := date_trunc('day', state_time);
    current_hours := date_trunc('hour', state_time);

    select * into days
    FROM a4y_chart_days
    WHERE uid=state_uid and time=current_days;

    select * into hours
    FROM a4y_chart_hours
    WHERE uid=state_uid AND time=current_hours;

    -- RAISE NOTICE 'current_days: %', current_days;
    -- RAISE NOTICE 'current_hours: %', current_hours;
    RAISE NOTICE 'days: %', days;
    RAISE NOTICE 'hours: %', hours;
    -- RAISE NOTICE 'days_pm10: %', days_pm10;

    if days ISNULL then
        insert into a4y_chart_days(
            time,
            uid,
            pm10,
            pm2_5,
            co2,
            humidity,
            temperature,
            voc,
            n
        ) values (
            current_days,
            state_uid,
            state_pm10,
            state_pm2_5,
            state_co2,
            state_humidity,
            state_temperature,
            state_voc,
            1
        );
    else
        days_alpha := days.n / (days.n + 1.0);
        days.pm10 := (days_alpha * days.pm10) + ((1.0 - days_alpha) * state_pm10);
        days.pm2_5 := (days_alpha * days.pm2_5) + ((1.0 - days_alpha) * state_pm2_5);
        days.co2 := (days_alpha * days.co2) + ((1.0 - days_alpha) * state_co2);
        days.humidity := (days_alpha * days.humidity) + ((1.0 - days_alpha) * state_humidity);
        days.temperature := (days_alpha * days.temperature) + ((1.0 - days_alpha) * state_temperature);
        days.voc := (days_alpha * days.voc) + ((1.0 - days_alpha) * state_voc);
        days.n := days.n + 1;
        update
            a4y_chart_days
        set
            pm10=days.pm10,
            pm2_5=days.pm2_5,
            co2=days.co2,
            humidity=days.humidity,
            temperature=days.temperature,
            voc=days.voc,
            n=days.n
       where
            uid=state_uid
            and time=current_days;
    end if;

    if hours ISNULL then
        insert into a4y_chart_hours(
            time,
            uid,
            pm10,
            pm2_5,
            co2,
            humidity,
            temperature,
            voc,
            n
        ) values (
            current_hours,
            state_uid,
            state_pm10,
            state_pm2_5,
            state_co2,
            state_humidity,
            state_temperature,
            state_voc,
            1
        );
    else
        hours_alpha := hours.n / (hours.n + 1.0);
        hours.pm10 := (hours_alpha * hours.pm10) + ((1.0 - hours_alpha) * state_pm10);
        hours.pm2_5 := (hours_alpha * hours.pm2_5) + ((1.0 - hours_alpha) * state_pm2_5);
        hours.co2 := (hours_alpha * hours.co2) + ((1.0 - hours_alpha) * state_co2);
        hours.humidity := (hours_alpha * hours.humidity) + ((1.0 - hours_alpha) * state_humidity);
        hours.temperature := (hours_alpha * hours.temperature) + ((1.0 - hours_alpha) * state_temperature);
        hours.voc := (hours_alpha * hours.voc) + ((1.0 - hours_alpha) * state_voc);
        hours.n := hours.n + 1;
        update
            a4y_chart_hours
        set
            pm10=hours.pm10,
            pm2_5=hours.pm2_5,
            co2=hours.co2,
            humidity=hours.humidity,
            temperature=hours.temperature,
            voc=hours.voc,
            n=hours.n
       where
            uid=state_uid
            and time=current_hours;
    end if;

    RAISE NOTICE 'days: %', days;
    RAISE NOTICE 'hours: %', hours;

    if exists(select uid from a4y_state_last where uid=state_uid) then
        update
            a4y_state_last
        set
            time=state_time,
            fw_ver=state_fw_ver,
            pm10=state_pm10,
            pm2_5=state_pm2_5,
            co2=state_co2,
            humidity=state_humidity,
            temperature=state_temperature,
            voc=state_voc,
            mode=state_mode,
            power_state=state_power_state,
            fan_control=state_fan_control,
            lock=state_lock,
            filter=state_filter,
            filter_life=state_filter_life,
            uv_led=state_uv_led,
            time_reservation=state_time_reservation,
            sleep_mode=state_sleep_mode
        where
            uid=state_uid;
    else
        insert into a4y_state_last (
            time,
            fw_ver,
            uid,
            pm10,
            pm2_5,
            co2,
            humidity,
            temperature,
            voc,
            mode,
            power_state,
            fan_control,
            lock,
            filter,
            filter_life,
            uv_led,
            time_reservation,
            sleep_mode
        ) values (
            state_time,
            state_fw_ver,
            state_uid,
            state_pm10,
            state_pm2_5,
            state_co2,
            state_humidity,
            state_temperature,
            state_voc,
            state_mode,
            state_power_state,
            state_fan_control,
            state_lock,
            state_filter,
            state_filter_life,
            state_uv_led,
            state_time_reservation,
            state_sleep_mode
        );
    end if;
end;
$function$;

select add_a4y_state(timestamptz '2021-11-26 16:37:16.0+09:00', 0, 100, 8, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5);

Transactions

A plpgsql function runs automatically inside a transaction. Either everything succeeds or everything fails. Documentation

Functions and trigger procedures are always executed within a transaction established by an outer query. They cannot start or commit that transaction, since there is no context for them to execute in. However, a block containing an EXCEPTION clause effectively forms a subtransaction that can be rolled back without affecting the outer transaction. SeeSection 42.6.6for more details.

The KLDP documentation explains it as follows:

You must understand that PL/pgSQL's BEGIN/END is different from the database statements for transactions (BEGIN; END;). Also, functions and trigger procedures cannot start or commit a transaction, and Postgres cannot have nested transactions.

See also

Favorite site

Tutorials

Guide

author Zer0 sitemap