Here's a tricky one:
Versions: I'm using PostgreSQL 9.0.7 and PHP 5.3.8.
First, I have a table "bob" with two columns: "bobid" (integer) and "bobtext"
(text). The "bobid" field defaults to a sequence called "bob_seq" which is just
a simple counter (1,2,3 etc.)
I can do an INSERT query into "bob":
INSERT INTO bob (bobtext) VALUES ('Hello Bob') RETURNING bobid;
and get the value the sequence has given me for "bobid": this works fine using
both the psql tool and pg_query in PHP.
I can also do an UPDATE query in the same vein:
UPDATE bob SET bobtext='Hello Bob revisited' WHERE bobid=<x> RETURNING bobid;
where <x> is a valid "bobid" value for an existing record in the table. This
returns the value of "bobid" for the updated record which should be the same as
<x> and again it works in the psql tool and in pg_query.
Next, I have a VIEW based on "bob", called "fred". It is simply defined as
CREATE VIEW fred AS (
SELECT * FROM bob
I have two rules on "fred" which allow me to alter the view.
The first is used for INSERT into "fred", which translates them into INSERT into
CREATE OR REPLACE RULE fred_insert AS
ON INSERT TO fred
INSERT INTO bob (bobtext) VALUES (NEW.bobtext) RETURNING *
The second is used for UPDATE on "fred", which translates to creating a new
record in "bob" and deleting the old record:
CREATE OR REPLACE RULE fred_update AS
ON UPDATE TO fred
DO INSTEAD (
INSERT INTO bob (bobtext) VALUES (NEW.bobtext) RETURNING *;
DELETE FROM bob WHERE bobid=OLD.bobid;
Note that these rules both return all of the fields of the newly inserted "bob"
records back up the chain to be accessible to the original query.
Now, I can insert into "fred" knowing that the rule will handle it:
INSERT INTO fred (bobtext) VALUES ('Hello Fred') RETURNING bobid;
and that should return the new "bobid" value of the inserted record (from the
"bob_seq" sequence). It still works in the psql tool and in pg_query.
I should also be able to exercise the update rule on "fred":
UPDATE fred SET bobtext='Hello Fred revisited' WHERE bobid=<x> RETURNING bobid
where <x> is a valid "bobid" value for an existing record in the table.
This should insert a new row with a new "bobid" from the sequence and the new
"bobtext" value, delete the row with "bobid"=<x>, and return the "bobid" for the
Here's the punchline (at last):
It works in the psql tool.
It doesn't work in pg_query.
$result = pg_query("UPDATE fred SET bobtext='Hello Fred revisited' WHERE
bobid=42 RETURNING bobid"); // Assume there is a record with bobid==42
if ($result && pg_numrows($result))
echo pg_fetch_result($result, 0, 0).PHP_EOL;
Tells me there that although the $result resource is valid, there are no rows
and therefore no result to fetch (PHP Warning: pg_fetch_result(): Unable to
jump to row 0 on PostgreSQL result ...)
So: why is the return from the UPDATE rule different to the return from the
INSERT rule in PHP pgsql?
Thanks for taking the time...
Peter Ford, Developer phone: 01580 893333 fax: 01580 893399
Justcroft International Ltd. www.justcroft.com
Justcroft House, High Street, Staplehurst, Kent TN12 0AH United Kingdom
Registered in England and Wales: 2297906
Registered office: Stag Gates House, 63/64 The Avenue, Southampton SO17 1XS
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php