Dear Andy, We are using bulk collect to enhance the performance as the data is huge.
But as you said it is ideally insert into b select * from a; So now I am using the looping through query result option as Adrian suggested. http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING Thank You Adrian. Thanks & Regards Medhavi Mahansaria Tata Consultancy Services Limited Unit-VI, No.78, 79& 83, L-Centre, EPIP Industrial Estate, Whitefield Bangalore - 560066,Karnataka India Ph:- +91 80 67253769 Cell:- +91 9620053040 Mailto: medhavi.mahansa...@tcs.com Website: http://www.tcs.com ____________________________________________ Experience certainty. IT Services Business Solutions Consulting ____________________________________________ From: Andy Colson <a...@squeakycode.net> To: Medhavi Mahansaria <medhavi.mahansa...@tcs.com>, pgsql-general@postgresql.org Date: 05/25/2015 06:15 PM Subject: Re: [GENERAL] Reg: BULK COLLECT 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 =====-----=====-----===== Notice: The information contained in this e-mail message and/or attachments to it may contain confidential or privileged information. If you are not the intended recipient, any dissemination, use, review, distribution, printing or copying of the information contained in this e-mail message and/or attachments to it are strictly prohibited. If you have received this communication in error, please notify us by reply e-mail or telephone and immediately and permanently delete the message and any attachments. Thank you