Re: [GENERAL] Reg: BULK COLLECT

[Medhavi Mahansaria]

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