PHP + PostgreSQL: pg_query not returning the expected result for this (edge) case:

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 "bob": CREATE OR REPLACE RULE fred_insert AS

    ON INSERT TO fred
    DO INSTEAD
      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 new row. Here's the punchline (at last): It works in the psql tool. It doesn't work in pg_query. <?php $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_numrows($result).PHP_EOL;
       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... Cheers Pete -- 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