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