Skip to content

PL/pgSQL

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.

소개

언어의 기본 구조

PL/pgSQL은 블록구조의 언어다. 함수 본문 구조는 block 형태여야 한다. 블록은 아래처럼 정의된다. 

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

Dollar-Quoted String Constants

달러 쿼우팅 ($$ ... $$) 은 여러 줄에 걸친 코드나 쿼리를 작성할 수 있습니다.

$function$ ... $function$ 와 같이 시작과 끝은 동일한 라벨로 쿼우팅해야 한다.

중첩 쿼우팅의 예시: 

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

변수와 상수 

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

함수 예제 

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;

RETURNS 생략 방법

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;

테이블 반환 방법

RETURNS SETOF 테이블이름를 사용하면 된다.

INSERT RETURNING 

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

또는 

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

배열 병합: 

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

요소 분해: 

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;

함수 중간에서 즉시 종료 방법

해당하는 위치에서 return;를 사용하면 된다: 

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

Record 를 직접 INSERT 하는 방법 

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

작동 여부는 테스트 해봐야 한다.

반환할 컬럼을 정의하여 반환 방법 

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';

특정 테이블을 참조한 결과를 반환

다음과 같이 사용: 

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;

supabase-py에서 rpc 호출시 row 를 꼭 반환해야 한다.

  • returns setof progress - progress라는 테이블을 참조하여 반환 한다.
  • return query ... - 쿼리 결과를 반환한다.

다음과 같이 호출하면: 

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

update ... returning *;결과와 비슷한 결과가 출력된다.

레코드 반환 방법 

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;

다음과 같이 출력하면: 

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

다음과 같은 결과가 하나의 컬럼으로 반환된다: 

(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")

Answer의 관리자/그룹/프로젝트 조건에 맞춰 권한 반환 

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

a4y 장치 상태/차트 정보 저장 함수 

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

트랜잭션

plpgsql 함수 는 트랜잭션 내에서 자동으로 실행됩니다. 모두 성공하거나 모두 실패합니다. 설명서

함수와 트리거 프로시저는 항상 외부 쿼리에 의해 설정된 트랜잭션 내에서 실행됩니다. 실행할 컨텍스트가 없기 때문에 해당 트랜잭션을 시작하거나 커밋할 수 없습니다. 그러나 EXCEPTION절을 포함하는 블록은 효과적으로 하위 트랜잭션을 형성할 수 있습니다. 외부 트랜잭션에 영향을 주지 않고 롤백됩니다. 이에 대한 자세한 내용은섹션 42.6.6을 참조하십시오.

KLDP 설명서는 다음과 같이 설명되어 있다:

PL/pgSQL 의 BEGIN/END 와 Transaction(BEGIN; END;)을 위한 데이타베이스의 명령문과는 다르다는것을 이해해야 한다. 또한 Function과 Trigger Procedure 에서는 트랜잭션을 시작 하거나 commit 을 할 수 없고 Postgres는 중첩된 트랜잭션을 가질 수 없다.

See also

Favorite site

Tutorials

Guide