On 05/25/2015 05:24 AM, Medhavi Mahansaria wrote:
Hello,
I am porting my application from Oracle to PostgreSQL.
We are using BULK COLLECT functionality of oracle.
How can i change the 'BULK COLLECT' fetch of the data from the cursor to
make if compatible for pg/plsql?
See here:
http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING
A small example is as below (This is just an example and the query is
much more complex which returns huge amount of data)
*/CREATE OR REPLACE FUNCTION abc() RETURNS VOID AS $body$/*
*/DECLARE/*
*/l_data b%ROWTYPE;/*
*/POPULATE_STATS CURSOR IS/*
*/(/*
*/SELECT * from a/*
*/)/*
*/; // query returning a huge amount of data/*
*/BEGIN/*
*/ OPEN POPULATE_STATS;/*
*/ LOOP/*
*/ FETCH POPULATE_STATS BULK COLLECT INTO l_data LIMIT 1000;/*
*/ IF POPULATE_STATS%ROWCOUNT > 0/*
*/ THEN/*
*/ FORALL i IN 1..l_data.COUNT/*
*/ INSERT INTO b VALUES l_data(i);/*
*/ END IF;/*
*/ IF NOT FOUND THEN EXIT; END IF; /*
*/ END LOOP;/*
*/ CLOSE POPULATE_STATS;/*
*/EXCEPTION/*
*/ WHEN OTHERS THEN/*
*/ CODE := SQLSTATE;/*
*/ MSG := SQLERRM;/*
*/ INSERT INTO tracker VALUES (CODE,MSG,LOCALTIMESTAMP);/*
*/ RAISE NOTICE 'SQLERRM';/*
*/ RAISE NOTICE '%', SQLSTATE;/*
*/ RAISE NOTICE '%', MSG;/*
*/END;
/*
*/$body$/*
*/LANGUAGE PLPGSQL;/*
How can i change the 'BULK COLLECT' fetch of the data from the cursor to
make if compatible for pg/plsql?
Thanks & Regards
Medhavi Mahansaria
Mailto: medhavi.mahansa...@tcs.com
--
Adrian Klaver
adrian.kla...@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
