From: | David Fetter <david(at)fetter(dot)org> |
---|---|
To: | SF Postgres <sfpug(at)postgresql(dot)org> |
Subject: | Transaction-based insert w/PHP? |
Date: | 2003-02-12 20:35:07 |
Message-ID: | 20030212203507.GX30934@fetter.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | sfpug |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2003-02-13 00:52:45 | Re: Transaction-based insert w/PHP? |
Previous Message | David Fetter | 2003-02-11 01:20:49 | Re: Are we having a meeting next week? |