Re: Reg: BULK COLLECT

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(dot)mahansaria(at)tcs(dot)com
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com