Can I capture created key id's, work with them, then return themlater?

Поиск
Список
Период
Сортировка
Искать
От
rox
Тема
Can I capture created key id's, work with them, then return themlater?
Дата
Msg-id
0983d7f6ecf35c11f3a7f83a32cd9576@mail.webfaction.com
Список
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 ;

    ...

    EXECUTE 'UPDATE ' || p_table_name... WHERE id in ();
    ...

    RETURN ; [??? maybe: RETURN QUERY SELECT )

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

В списке pgsql-novice по дате отправления
От: bradg
Дата:
Сообщение: Re: PQisBusy() always busy
От: Merlin Moncure
Дата:
Сообщение: Re: PQisBusy() always busy
FAQ