Hello...
I'm working on migrating some convoluted code from PHP into a stored
procedure because it's all DB work anyway.
This code inserts a bunch of new records, then modifies different
columns based on other complex queries. Because we're dealing
with a large volume of records, it is not really feasible to keep all
the data in memory.
Because I'm phasing the implementation in bits, I need to be able to
pass the list of identifiers for the newly created rows
back to PHP (and/or on to other SQL statements). I'm also willing to
use a temp table for the id's.
So, we start with inserting the records. We may be inserting "all" of
a table, or adding new rows to a table
depending upon when or how this takes place. I need to be able to
capture the new record identifiers (somehow).
I've worked out that (under 8.4 at least) I can capture the id's
created from a bulk insert with:
CREATE FUNCTION A ... RETURNS TABLE (i int) AS $f$
RETURN QUERY EXECUTE 'INSERT INTO ' || p_table_name ... RETURNING
table_id;
$f$ LANGUAGE plpgsql VOLATILE;
That returns the list of id's from the stored procedure quite nicely...
Now I'm trying to augment that beginning with some "post-processing"
based on those id's... and would rather put that
in the same procedure such that...
EXECUTE 'INSERT INTO ... RETURNING table_id' INTO <list of ids>;
...
EXECUTE 'UPDATE ' || p_table_name... WHERE id in (<list of ids>);
...
RETURN <list of ids>; [??? maybe: RETURN QUERY SELECT <list of ids>)
In concept that's what I want to be able to do... however I haven't
seen an example in various searches of the forums and docs that shows
how to return a rowset into a pgsql variable from a multi-row INSERT
statement or any form of EXECUTE/INTO with multiple rows.
Am I going about this totally backwards, or... ?
Roxanne
