Search Postgresql Archives

Re: Reg: BULK COLLECT

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxx
Website:
http://www.tcs.com
____________________________________________
Experience certainty.        IT Services
                       Business Solutions
                       Consulting
____________________________________________




From:        Andy Colson <andy@xxxxxxxxxxxxxxx>
To:        Medhavi Mahansaria <medhavi.mahansaria@xxxxxxx>, pgsql-general@xxxxxxxxxxxxxx
Date:        05/25/2015 06:15 PM
Subject:        Re: 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@xxxxxxx
>

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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux