[PostgreSQL] PL/pgSQL - SQL Procedural Language ( Function )

PostgreSQL에서는 Procedural Language를  Function으로 사용한다.

PostgreSQL에서 PL/pgSQL의 기본 구조는 다음과 같다.

DECLARE : 변수 선언부

BEGIN ~ END : FUNCTION 내용

간단하다.

integer형 숫자를 반환하는 간단한 Function을 만들면 다음과 같다.

CREATE OR REPLACE FUNCTION "FN_Test1"()

  RETURNS integer AS

$BODY$

DECLARE

   temp_number integer;

BEGIN

   temp_number = 1;

   RETURN temp_number;

END

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

ALTER FUNCTION "FN_Test1"()

  OWNER TO postgres;

Function에서 반환값은 RETURNS에서 설정한다. 여러 반환 형식들이 있으며 반환값이 없을 경우에는 void를 쓰면 된다.

파라미터를 입력하고 싶으면 파라미터를 입력받을 수 있도록 하면 된다.

CREATE OR REPLACE FUNCTION "FN_Test2"(p_number integer DEFAULT 1)

  RETURNS integer AS

$BODY$

DECLARE

   temp_integer integer;

BEGIN

   temp_integer = p_number;

   RETURN temp_integer;

END

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

ALTER FUNCTION "FN_Test2"(integer)

  OWNER TO postgres;

위와 같은 방법은 단 하나의 결과값을 반환할 경우에 사용된다. 쿼리문의 결과를 반환하고 싶은 경우에는 SETOF를 사용해야 한다.

CREATE OR REPLACE FUNCTION "FN_SelectTestNumberTable1"()

  RETURNS SETOF integer AS

$BODY$

BEGIN

   RETURN QUERY SELECT "Number1" FROM "TestNumber";

END

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100

  ROWS 1000;

ALTER FUNCTION "FN_SelectTestNumberTable1"()

  OWNER TO postgres;

SETOF를 사용하여 integer형식의 쿼리문 결과를 반환하는 Function이다. RETURNS SETOF으로 반환 형식을 설정하고, RETURN QUERY를 사용하여 쿼리문 결과를 반환한다.

반환 형식은 테이블 형식으로도 설정할 수 있다.

CREATE OR REPLACE FUNCTION "FN_SelectTestNumberTable2"()

  RETURNS SETOF "TestNumber" AS

$BODY$

DECLARE

    r "TestNumber"%rowtype;

BEGIN

    FOR r IN SELECT * FROM "TestNumber"

    LOOP

        -- can do some processing here

        RETURN NEXT r; -- return current row of SELECT

    END LOOP;

    RETURN;

END

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100

  ROWS 1000;

ALTER FUNCTION "FN_SelectTestNumberTable2"()

  OWNER TO postgres;

CREATE OR REPLACE FUNCTION "FN_SelectTestNumberTable3"()

  RETURNS SETOF "TestNumber" AS

$BODY$

BEGIN

   RETURN QUERY SELECT * FROM "TestNumber";

END

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100

  ROWS 1000;

ALTER FUNCTION "FN_SelectTestNumberTable3"()

  OWNER TO postgres;

RETURNS SETOF가 테이블로 되어있다. 이렇게 하면 설정해 놓은 테이블 형식으로 결과가 반환된다.

위의 두개 Function은 같은 결과를 반환한다. 그냥 비교 차원에서 두개를 같이 올린다.

반환 형식을 테이블 형식으로 한다면 결과는 테이블 형식에 만족해야 한다. 만약 만족하지 못한다면 에러가 발생한다. 하지만... 원하는 필드의 데이터만 반환하고 싶은 경우나, 조인을 사용하여 테이블 형식을 만족할 수 없는 경우가 있다. 이럴 땐 데이터 형식을 직접 만들어서 적용하면 된다.

CREATE TYPE "DT_TestNumber" AS

   ("Num1" integer,

    "Num2" integer);

ALTER TYPE "DT_TestNumber"

  OWNER TO postgres;

위와 같이 데이터 형식을 만든 후

CREATE OR REPLACE FUNCTION "FN_SelectTestNumberTable4"()

  RETURNS SETOF "DT_TestNumber" AS

$BODY$

BEGIN

   RETURN QUERY SELECT "Number1", "Number2" FROM "TestNumber";

END

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100

  ROWS 1000;

ALTER FUNCTION "FN_SelectTestNumberTable4"()

  OWNER TO postgres;

Function에 적용하면 설정한 데이터 형식으로 결과가 반환된다. 유의할 점은 데이터 형식과 Function의 결과의 테이터 형식의 순서가 맞아 떨어져야 정상적으로 동작한다.

Function 사용은 SELECT를 하면 된다.

SELECT "FN_Test1"();

SELECT "FN_Test2"();

SELECT "FN_Test2"(2);

SELECT "FN_SelectTestNumberTable1"();

SELECT "FN_SelectTestNumberTable2"();

SELECT "FN_SelectTestNumberTable3"();

SELECT "FN_SelectTestNumberTable4"();

SELECT * FROM "FN_SelectTestNumberTable1"();

SELECT * FROM "FN_SelectTestNumberTable2"();

SELECT * FROM "FN_SelectTestNumberTable3"();

SELECT * FROM "FN_SelectTestNumberTable4"();

SELECT "Number1", "Number2" FROM "FN_SelectTestNumberTable2"();

SELECT "Number1", "Number2" FROM "FN_SelectTestNumberTable3"();

SELECT "Num1", "Num2" FROM "FN_SelectTestNumberTable4"();