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!
