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