Re: Reg: BULK COLLECT

It seems you are fetching from a table then sequentially inserting each
record to another table.
In PostgreSQL, you could use cursors in PL/pgSQL ("
http://www.postgresql.org/docs/9.4/interactive/plpgsql-cursors.html").

Alternatively you may write a single query which selects from the table and
performs an insert into the other table, it is usually more efficient than
sequential single record inserts.

If the amount of data are significant you may use ORDER BY and LIMIT in
your SELECT query in order to limit the count of records you are fetching
for insertion.
You may place this SQL query in a loop within a bash script as shown below,
this bash script and the pseudo query updates a few records of a given
table at a time, you may convert this example into an INSERT query for
piecemeal inserts keeping the transaction size manageable.

#!/bin/sh
#date;time sh scripts/sample_piecemeal_db_updation.sh 10000 2>&1|tee
/tmp/sample_piecemeal_db_updation.sh.sh.log;date;
export PSQL_PATH="/apps/postgreSQL/pgsql-9.1.4/bin/psql";
export DB_NAME="my_db";
export PGPORT="5432";
export DB_USER="some_user";
export DB_HOST="some_ip"; #put the actual ip here.
step=$1;

echo `date +%Y-%m-%d.%H%Mhrs:%S.%N`'-DB update commences';
trim() { echo $1; }

_sql_query2="
SELECT count(a.*)AS cnt FROM main_table a;
";
echo $_sql_query2;
counter=0;
for cnt in `$PSQL_PATH -d$DB_NAME -U$DB_USER --port=$PGPORT
--host=$DB_HOST --quiet --tuples-only -c"$_sql_query2"`
do
echo '$cnt is:'$cnt;
counter=$(( $counter + 1 ));
for i in $(seq 0 $step $cnt)
do
_sql_query1="
SET work_mem='1GB';
UPDATE main_table b
SET some_field=a.some_field
FROM
(
SELECT
a.id
,b.some_field2
FROM
(
SELECT
a.*
FROM
main_table a
ORDER BY a.id ASC
OFFSET $i
LIMIT $step
)a
JOIN
some_other_table b
ON
b.some_other_field=a.some_other_field
)a
WHERE
b.id=a.id
;
";
#echo "_sql_query1 is:$_sql_query1";
results=`$PSQL_PATH -d$DB_NAME -U$DB_USER --port=$PGPORT
--host=$DB_HOST --quiet --tuples-only -c"$_sql_query1"`
echo "cnt is:$cnt, step is:$step, i is:$i,
results:$results";
done;
done;
echo `date +%Y-%m-%d.%H%Mhrs:%S.%N`'-DB updation concludes.';

Regards,
Allan.

On Mon, May 25, 2015 at 3:24 PM, Medhavi Mahansaria <
medhavi(dot)mahansaria(at)tcs(dot)com> 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
>
> =====-----=====-----=====
> Notice: The information contained in this e-mail
> message and/or attachments to it may contain
> confidential or privileged information. If you are
> not the intended recipient, any dissemination, use,
> review, distribution, printing or copying of the
> information contained in this e-mail message
> and/or attachments to it are strictly prohibited. If
> you have received this communication in error,
> please notify us by reply e-mail or telephone and
> immediately and permanently delete the message
> and any attachments. Thank you
>
>