Re: Reg: BULK COLLECT - Mailing list pgsql-general
| From | Medhavi Mahansaria |
|---|---|
| Subject | Re: Reg: BULK COLLECT |
| Date | |
| Msg-id | OF97D396FE.EF89C3AC-ON65257E52.001FBC5C-65257E52.00204260@tcs.com Whole thread Raw |
| In response to | Re: Reg: BULK COLLECT (Andy Colson <andy@squeakycode.net>) |
| Responses |
Re: Reg: BULK COLLECT
(Andy Colson <andy@squeakycode.net>)
Re: Reg: BULK COLLECT (Simon Riggs <simon@2ndQuadrant.com>) |
| List | pgsql-general |
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.mahansaria@tcs.com
Website: http://www.tcs.com
____________________________________________
Experience certainty. IT Services
Business Solutions
Consulting
____________________________________________
From: Andy Colson <andy@squeakycode.net>
To: Medhavi Mahansaria <medhavi.mahansaria@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.mahansaria@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
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.mahansaria@tcs.com
Website: http://www.tcs.com
____________________________________________
Experience certainty. IT Services
Business Solutions
Consulting
____________________________________________
From: Andy Colson <andy@squeakycode.net>
To: Medhavi Mahansaria <medhavi.mahansaria@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.mahansaria@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
pgsql-general by date: