PHP, Postgres help using RETURNING

php postgresql sql-returning
regan_leah picture regan_leah · Oct 25, 2012 · Viewed 17.5k times · Source

I think I understand how PostgreSQL and RETURNING works - I've found many, many resources. If I'm catching on, it would look something like

"INSERT INTO table (column2, column3) VALUES ('value1', 'value2') RETURNING id;"

However, I can't find anything that helps me access this via PHP. When I thought I figured it out, I tried

$new_id = pg_query($dbc, "INSERT INTO table (column2, column3) ".
"VALUES ('value1', 'value2') RETURNING id;");
return $new_id;

But it returns NULL. I also tried executing and declaring the variable separately with one query. After looking for hours for the solution, I've settled on a max(id) SELECT statement/function, but it's still bothering me. Any help is greatly appreciated.

I'm using Postgres 8.4 and PHP 5.3.

Answer

greg picture greg · Oct 25, 2012

$new_id does not contain the id but it is a resource descriptor. You need to fetch the data from it as the query would be a SELECT, with pg_fetch_array($new_id) by example.

The RETURNING clause of PostgreSQL projects any fields of the inserted or modified rows ie INSERT|UPDATE … RETURNING id, field1, field2.

Related questions

Cannot simply use PostgreSQL table name ("relation does not exist")

I'm trying to run the following PHP script to do a simple database query: $db_host = "localhost"; $db_name = "showfinder"; $username = "user"; $password = "password"; $dbconn = pg_connect("host=$db_host dbname=$db_name user=$username password=$password") or die('Could …

Read More
Laravel: Error [PDOException]: Could not Find Driver in PostgreSQL

I'm trying to connect with PostgreSQL database through Laravel in order to do a php artisan migrate but doesn't seem to be directed since it's reading the database name of MySQL. Here are the commands from database.php: 'connections' => …

Read More
How do I enable php to work with postgresql?

<?php try { $dbh = new PDO('pgsql:host=localhost;port=5432;dbname=###;user=###;password=##'); echo "PDO connection object created"; } catch(PDOException $e) { echo $e->getMessage(); } ?> I get the error message "Could Not Load Driver"

Read More