What does standard SQL or any of the non-PostgreSQL SQLs do instead of RETURNING?

Common code for me:

if ($new_id = dbcall('INSERT INTO table (column) VALUES ($1) RETURNING id', 'meow'))

$new_id then contains the new id for the freshly inserted record.

According to https://www.postgresql.org/docs/current/sql-insert.html :

INSERT conforms to the SQL standard, except that the RETURNING clause is a PostgreSQL extension

That makes me feel bad about using RETURNING, and it also makes me wonder what standard SQL and non-PG SQL databases do to let the user “grab” the id in a safe manner.

I remember doing things like:

SELECT lastval();

directly after the INSERT, but that could easily turn into a “race condition” and thus give the wrong id! That’s why I started doing RETURNING, after having it recommended to me by PG guys, but since it’s nonstandard, I don’t feel good about using it.

On the other hand, I have given up on ever writing “standard SQL” because such a thing doesn’t seem to exist outside of the meeting room at some organization that charges money for this massive SQL standard which they somehow make money from even though nobody follows it.

The Standard SQL Way™ of generating IDs is to

1. create a sequence for the table;
2. read the next value with SELECT NEXT VALUE FOR seq_name¹; and then
3. insert a complete row, including the ID value.

Before SQL:2003, there were no autogenerated IDs. And in SQL:2003, there appears to be no method to read the last GENERATED AS IDENTITY value.

_{¹ Postgres does not support this, and requires SELECT nextval('seq_name').}

The SQL standard offers data change delta tables for this:

<data change delta table> ::=
  <result option> TABLE <left paren> <data change statement> <right paren>
<data change statement> ::=
    <delete statement: searched>
  | <insert statement>
  | <merge statement>
  | <update statement: searched>
<result option> ::=
    FINAL
  | NEW 
  | OLD

and the standard-compliant statement would be this:

SELECT id FROM FINAL TABLE (INSERT INTO table (column) VALUES ('blah'))

As far as I know, only Db2 currently supports this syntax (partially — you cannot use it with MERGE).