Returning an inserted value into a variable with PostgreSQL RETURNING .. INTO clause

Question

Following insert, the statement works fine with Oracle.  

INSERT INTO table_name (col1, col2) VALUES (val1, val2) RETURNING col1 INTO :var ;

We can bind a variable to :var and execute the statement.

However, when the same query runs in a Postgres database it gives following error.

ERROR: syntax error at or near "INTO".

What is the reason for this behavior?

I tried to run the query in a PL/pgSQL block and it works perfectly.

DO $$
DECLARE var integer;
BEGIN
  INSERT INTO "table_name "(
            "col1, "col2")
          VALUES (val1, val2)
          RETURNING "col1" INTO var;
END $$

But above query cannot use inside C++ code directly without PL/pgSQL block when we connect to a Postgres database. I am using PostgreSQL 9.5.4. Since we support database agnostic software applications we need to clarify this behavior.

I expect both database support RETURNING .. INTO clause in the same way.

Answer 1

In PostgreSQL, the SQL statement INSERT ... RETURNING doesn't support an INTO clause, same as SELECT.

Both statements support INTO only in PL/pgSQL.

You handle INSERT ... RETURNING just like SELECT in PostgresSQL — it returns a result set.

I don't know why Oracle treats INSERT ... RETURNING so complicated and different from SELECT; the design decision was probably made as an afterthought in the late Seventies.