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
Friday, October 22, 2010
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;
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;
Friday, July 23, 2010
Subscribe to:
Posts (Atom)