Atlas, PostgreSQL, and RETURNING

One of the powerful features of PostgreSQL is its RETURNING clause. For example, if you have a table like this ...

CREATE TABLE articles (
    article_id SERIAL PRIMARY KEY,
    title      VARCHAR(255) NOT NULL,
    -- ...
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

... and you insert a row using Atlas.Query with a RETURNING clause ...

/** @var $insert \Atlas\Query\Insert */
$insert
    ->into('articles')
    ->columns(['title' => 'My Article'])
    ->returning('created_at');

$pdoStatement = $insert->perform();

... then you can fetch the RETURNING column values from the PDOStatement result:

$returning = $pdoStatement->fetch(PDO::FETCH_ASSOC);
var_export($returning);
// ['created_at' => (the postgresql timestamp)]

If you have an Atlas.Table data gateway for that table, you can make use of RETURNING in your TableEvents classes to populate database-provided values into your Row objects automatically.

To do so, add a RETURNING clause in the modifyInsertRow() method, then retrieve the values into the Row in the afterInsertRow() method:

// ...

class ArticleTableEvents extends TableEvents
{
    public function modifyInsertRow(
        Table $table,
        Row $row,
        Insert $insert
    ) : void
    {
        $insert->returning('created_at');
    }

    public function afterInsertRow(
        Table $table,
        Row $row,
        Insert $insert,
        PDOStatement $pdoStatement
    ) : void
    {
        $returning = $pdoStatement->fetch(PDO::FETCH_ASSOC);
        $row->created_at = $returning['created_at'];
    }
}

Now when a Row gets inserted through the Table data gateway, the created_at value will be populated automatically.

/** @var $tableLocator \Atlas\Table\TableLocator */
$articleTable = $tableLocator->get(ArticleTable::CLASS);
$article = $articlesTable->newRow();
$article->title = 'bar';
$articleTable->insertRow($article);
echo $article->created_at; // the postgresql timestamp

You can do the same for updates as well, using modifyUpdateRow() and afterUpdateRow().

Bonus: becuase it uses both Atlas.Table and Atlas.Query, this functionality is available "for free" in Atlas.Orm!

Are you stuck with a legacy PHP application? You should buy my book because it gives you a step-by-step guide to improving you codebase, all while keeping it running the whole time.