Search Postgresql Archives

Re: Reg: BULK COLLECT

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

 



On 05/25/2015 05: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?

See here:

http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING


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




--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[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