Re: Transaction-based insert w/PHP?

David,

This should work:

BEGIN;

SET CONSTRAINTS ALL deferred;
INSERT INTO foo (foo_desc)
VALUES ('too much unix!');

INSERT INTO bar (bar_desc, foo_id)
-- currval(sequence_name) fetches the current value of the sequence
VALUES ('never too much unix!', currval('foo_foo_id_seq'));

COMMIT;

> -- commit if this succeeds, roll back if it fails.
>
> END;

On Wed, Feb 12, 2003 at 12:35:07PM -0800, David Fetter wrote:
> Kind people,
>
> I'm using PHP on a project. Part of it involves doing inserts in
> several tables as a transaction, where the latter tables depend on the
> PK's returned by the first.
>
> Simplified example DDL:
>
> CREATE TABLE foo (
> foo_id SERIAL NOT NULL PRIMARY KEY
> , foo_desc VARCHAR(16) NOT NULL
> , UNIQUE(foo_desc)
> );
>
> CREATE TABLE bar (
> bar_id SERIAL NOT NULL PRIMARY KEY
> , bar_desc VARCHAR(32) NOT NULL
> , foo_id INTEGER NOT NULL REFERENCES foo(foo_id)
> );
>
> The SQL I want to do for an insert--is this right?
>
> BEGIN
>
> INSERT INTO foo (foo_desc)
> VALUES ('too much unix!')
>
> -- see if the insert succeeded, roll back if not.
> -- fetch the OID into $oid. Roll back if the fetch fails.
>
> SELECT foo_id
> FROM foo
> WHERE oid = $oid
>
> -- Fetch the $foo_id, or roll back if unable.
>
> INSERT INTO bar (bar_desc, foo_id)
> VALUES ('never too much unix!', $foo_id)
>
> -- commit if this succeeds, roll back if it fails.
>
> END;
>
> Does fetching that OID inside a transaction automatically guarantee
> that the right thing happens?
>
> Big TIA for any feedback :)
>
> Cheers,
> D
> --
> David Fetter david(at)fetter(dot)org http://fetter.org/
> phone: +1 510 893 6100 cell: +1 415 235 3778
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

--
Dror Matalon
Zapatec Inc
1700 MLK Way
Berkeley, CA 94709
http://www.zapatec.com