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
On 5/27/2015 12:52 AM, Medhavi Mahansaria wrote:> 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
Did you time it? I'll bet "insert into b select * from a" is the
fastest method.
-Andy
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
