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

No comments:

Post a Comment