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

Transaction-based insert w/PHP?

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 (view raw or flat)
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

Responses

sfpug by date

Next:From: Josh BerkusDate: 2003-02-13 00:52:45
Subject: Re: Transaction-based insert w/PHP?
Previous:From: David FetterDate: 2003-02-11 01:20:49
Subject: Re: Are we having a meeting next week?

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