Friday, October 22, 2010

Iterate Each row of a Table in postgersql storedprocedure

CREATE OR REPLACE FUNCTION "PROCGETDAILYCOLLECTIONREPORT"() AS
$BODY$
DECLARE
PAMOUNT DOUBLE PRECISION;
PROW "DAILYCOLLECTION"%rowtype;
.......................................................................
.............................................................................
FOR PROW IN SELECT * FROM "DAILYCOLLECTION"
WHERE "COLLECTIONDATE" = pdate
LOOP
SELECT "DISTANCEINKM" INTO PTRIPDISTANCE FROM "TRIP" WHERE "INTERNALID" = PROW."INTERNALTRIPNO";
UPDATE "TMP_TABLE" SET "KM" = "KM" + PTRIPDISTANCE
WHERE "INTERNALVRNNO" = PROW."INTERNALVRNNO";
-- can do some processing here

END LOOP;

$BODY$
LANGUAGE 'plpgsql' VOLATILE

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;