Wednesday, October 20, 2010

Replacing Cursor with Temporary table in PostgerSql

CREATE FUNCTION PROCSAMPLE()
RETURNS TABLE(C1 integer, C2 integer) AS $$

BEGIN
DROP TABLE IF EXISTS TMP_TABLE;

CREATE TEMPORARY TABLE TMP_TABLE
(

COL1 integer,
COL2 integer
);

INSERT INTO TMP_TABLE (COL1, COL2)
SELECT"STATUS", "MODE"
FROM "PURCHASEREQ"
;


RETURN QUERY SELECT *
FROM TMP_TABLE;


DROP TABLE TMP_TABLE;
END;
$$ LANGUAGE plpgsql;

No comments:

Post a Comment