Get values from RETURNING * within a transaction

Question

When I have two INSERT SQL statements (see below) within a begin; and commit; transaction then the RETURNING * does not return anything but if I take out the begin; and commit; the RETURNING * does return the inserted record.
How can I get the RETURNING * to work within a transaction?

BEGIN;

INSERT INTO gis_field_configuration
   (level_unique_name, level_name_caption, use_for_charts, use_as_displayby, 
    displayby_label, data_type, level_help_text)
VALUES (
    '[john].[john]',
    'john',
    'false',
    'false',
    '',
    'text',
    'help text'
);

INSERT INTO gis_field_configuration_bycube
   (cube, level_unique_name)
VALUES (
    'Instruments',
    '[john].[john]'
) RETURNING *;

COMMIT;

Answer 1

I suspect you're running all these statements as a single text string from your language's client driver. If that's the case, the result returned to the client will be what the last statement in the block of statements you sent returns. In this case that's the COMMIT statement, which has no result, so you get no result back.

Run each statement one-by-one and it should work fine. I can't give a more detailed example because you haven't mentioned which language you are using.

Here's a Python/psycopg example. The first approach where all the SQL is sent in one blob causes an exception when I try to get the results, because commit doesn't produce a result. The second example, where I run each statement separately and get the results of the select before I commit, works fine.

import psycopg2

conn = psycopg2.connect("dbname=regress")
curs = conn.cursor();

# All in one blob
try:
    curs.execute("BEGIN; SELECT generate_series(1,10); COMMIT;")
    print(curs.fetchall())
except (psycopg2.ProgrammingError) as ex:
    print("Failed: ", ex)

# vs one-by-one
curs.execute("BEGIN;")
curs.execute("SELECT x.* FROM generate_series(1,10) x;")
print(curs.fetchall())
curs.execute("COMMIT;")

Output:

$ python3 test.py 
Failed:  no results to fetch
[(1,), (2,), (3,), (4,), (5,), (6,), (7,), (8,), (9,), (10,)]

Answer 2

One way would be to use a data-modifying CTE and pack the two INSERTs into one command. Requires PostgreSQL 9.1 or later:

WITH x AS (
   INSERT INTO gis_field_configuration (level_unique_name, level_name_caption
                ,use_for_charts, use_as_displayby, displayby_label, data_type
                ,level_help_text)
   VALUES (
       '[john].[john]',
       'john',
       'false',
       'false',
       '',
       'text',
       'help text'
   )
   )
INSERT INTO gis_field_configuration_bycube
   (cube, level_unique_name)
VALUES (
    'Instruments',
    '[john].[john]'
    )
RETURNING *;

However, you do get values back with RETURNING * in any case. Just read them in before sending COMMIT. Sent in as batch, only results from the last command are returned - which would be the result of COMMIT, if you send all commands as one batch.

Hold back COMMIT; until you have received the results from the second INSERT.

---

In a plpgsql function

A function runs inside a transaction automatically . You don't need explicit BEGIN / COMMIT. To reuse values you get back from an INSERT use RETURNING *expressions* INTO [STRICT] *target*.

Consider this simple demo:

CREATE TABLE foo (foo_id serial, bar text);

CREATE OR REPLACE FUNCTION f_foo()
  RETURNS void LANGUAGE plpgsql AS
$BODY$
DECLARE
   foo_var foo; -- type name = table name, fits return type of RETURNING *
   -- or use a generic type record
BEGIN

   INSERT INTO foo (bar)
   VALUES ('baz')
   RETURNING *
   INTO foo_var;

   RAISE NOTICE 'New id is: %', foo_var.foo_id;

   -- do stuff with foo_var

   END;
   $BODY$;

SELECT f_foo();