Postgresql Procedure Sample

PG/SQL Procedure Sample

CREATE TABLE USERS (
   USER_ID BPCHAR(20) NOT NULL,
   USER_NAME BPCHAR(30) NULL,
   USER_AGE INT4 NULL
);


CREATE OR REPLACE PROCEDURE SAMPLE_CURSOR()
LANGUAGE PLPGSQL
AS $PROCEDURE$
DECLARE TARGET_CURSOR RECORD;
BEGIN
    FOR TARGET_CURSOR IN
        SELECT USER_ID, USER_AGE FROM USERS
    LOOP
        UPDATE USERS
        SET    USER_AGE = USER_AGE+1
        WHERE
            USER_ID = TARGET_CURSOR.USER_ID;
    END LOOP;
END;
$PROCEDURE$;

CALL CURSOR_SAMPLE();

-------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE SAMPLE_CRUD(
   ID CHARACTER, 
   NAME CHARACTER, 
   AGE INTEGER
) LANGUAGE PLPGSQL
AS $PROCEDURE$
DECLARE 
 CNT INTEGER;
BEGIN
   SELECT COUNT(*) INTO CNT FROM USERS WHERE USER_NAME = NAME;
   IF CNT != 0 THEN
      NAME := NAME || CNT+1;
   end IF;
    INSERT INTO USERS VALUES( ID, NAME , AGE );
END;
$PROCEDURE$

CALL SAMPLE_CRUD('CRLEE','바다쓰기',10);

-------------------------------------------------------------------

CREATE OR REPLACE FUNCTION SAMPLE_RETURN_QUERY(
   P_AGE INTEGER
)
RETURNS TABLE ( 
   F_NAME CHAR(20), 
   F_AGE INT 
)
LANGUAGE PLPGSQL
AS $FUNCTION$
BEGIN
    RETURN QUERY 
    SELECT USER_NAME, USER_AGE
    FROM   USERS
    WHERE  USER_AGE > P_AGE;
END; 
$FUNCTION$


SELECT * FROM SAMPLE_RETURN_QUERY(5);
-------------------------------------------------------------------

CREATE OR REPLACE FUNCTION SAMPLE_OUT(
    V1 NUMERIC, 
    V2 NUMERIC,
    OUT MIN_VALUE NUMERIC,
    OUT MAX_VALUE NUMERIC)
LANGUAGE PLPGSQL
AS $FUNCTION$ 
BEGIN
    MIN_VALUE := GREATEST(V1, V2);
    MAX_VALUE := LEAST(V1, V2);
END; 
$FUNCTION$

SELECT SAMPLE_OUT(  30 , 40 );
-------------------------------------------------------------------

CREATE OR REPLACE FUNCTION SAMPLE_VARIDIC(
   variadic PARAMS NUMERIC[] 
) 
RETURNS NUMERIC
LANGUAGE PLPGSQL
AS $FUNCTION$ 
DECLARE
    RES NUMERIC := 0;
BEGIN
    FOR I IN 1 .. ARRAY_LENGTH(PARAMS, 1) LOOP
        RES := RES + PARAMS[I];
    END LOOP;
    RETURN RES;
END; 
$FUNCTION$ 

SELECT SAMPLE_VARIDIC(1,2,3,4,5,6,7,8,9);

-------------------------------------------------------------------

CREATE OR REPLACE FUNCTION SAMPLE_CURSOR(
   OUT PO_REF_CUR REFCURSOR
)
RETURNS REFCURSOR
LANGUAGE PLPGSQL
AS $FUNCTION$
BEGIN
    OPEN PO_REF_CUR FOR
    SELECT USER_NAME , USER_AGE FROM USERS;
EXCEPTION
WHEN OTHERS THEN
    OPEN PO_REF_CUR FOR
    SELECT 'NO_DATA';
END;
$FUNCTION$;

SELECT SAMPLE_CURSOR();
FETCH ALL IN "<unnamed portal 22>";