On 05/25/2015 07: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?
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
That seems pretty over complicated version of:
insert into b select * from a;
Which is all you'll need in PG. It it does something else, then I failed to
understand the stored proc.
-Andy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
