Re: Reg: BULK COLLECT

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(dot)mahansaria(at)tcs(dot)com
Website: http://www.tcs.com
____________________________________________
Experience certainty. IT Services
Business Solutions
Consulting
____________________________________________

From: Andy Colson <andy(at)squeakycode(dot)net>
To: Medhavi Mahansaria <medhavi(dot)mahansaria(at)tcs(dot)com>,
pgsql-general(at)postgresql(dot)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(dot)mahansaria(at)tcs(dot)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