Skip site navigation (1) Skip section navigation (2)

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

From: rox <rox(at)tara-lu(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Can I capture created key id's, work with them, then return them later?
Date: 2011-09-14 13:44:49
Message-ID: 0983d7f6ecf35c11f3a7f83a32cd9576@mail.webfaction.com (view raw or flat)
Thread:
Lists: pgsql-novice
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


pgsql-novice by date

Next:From: Merlin MoncureDate: 2011-09-14 14:55:08
Subject: Re: PQisBusy() always busy
Previous:From: bradgDate: 2011-09-14 00:50:14
Subject: Re: PQisBusy() always busy

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group