참고

형식

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
} ...

예제

기본

CREATE OR REPLACE FUNCTION increment
(
    i integer
)
RETURNS integer
AS $$
BEGIN

    RETURN i + 1;

END;
$$
LANGUAGE plpgsql;

DECLARE, IF

DROP FUNCTION IF EXISTS LAST_INDEXOF;
CREATE FUNCTION LAST_INDEXOF
(
    str VARCHAR(50),
    substr VARCHAR(50)
)
RETURNS INT
AS $$
    DECLARE i INT DEFAULT 0;
BEGIN
    i := CHAR_LENGTH(str) - INSTR(REVERSE(str), REVERSE(substr)) - CHAR_LENGTH(substr) + 2;

    IF i > CHAR_LENGTH(str) THEN
	    i := 0;
	  END IF;

    RETURN i;
END;
$$
LANGUAGE plpgsql;

SELECT 결과를 곧바로 리턴

CREATE OR REPLACE FUNCTION GET_ADM_NAME
(
    i integer
)
RETURNS VARCHAR
AS $$
BEGIN
    RETURN(
        SELECT adm_address
          FROM adm_dstrt
         WHERE adm_code = i::VARCHAR
         LIMIT 1
    );
END; $$
LANGUAGE plpgsql;
-- 사용 예시
SELECT GET_ADM_NAME(1111010500);

테이블 형태로 리턴

DROP FUNCTION IF EXISTS TEST_FUNC_A;
CREATE FUNCTION TEST_FUNC_A()
RETURNS TABLE (
    _txt     TEXT,
    _int     INTEGER
) AS $$
BEGIN
    RETURN QUERY
        SELECT 'a', 1
        UNION
        SELECT 'b', 2
   ;
END; $$
LANGUAGE plpgsql ;
-- 호출 예시
SELECT TEST_FUNC_A();  -- 레코드 형태로 SELECT
SELECT * FROM TEST_FUNC_A(); -- 테이블 형태로 SELECT