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.mahansaria@tcs.com
>



--
Adrian Klaver
adrian.klaver@aklaver.com