While the documentation isn’t entirely clear, it does state that: If the INSERT command contains a RETURNING clause, the result will be similar to that of a SELECT statement containing the columns and values defined in the RETURNING list, computed over the row(s) inserted by the command. Now “similar to” isn’t an ironclad guarantee, and … Read more
You need to use the INTO clause in the RETURNING to set the value being returned into your variable: DECLARE myid OAMENI.id%TYPE; INSERT INTO oameni VALUES (default,’lol’) RETURNING id INTO myid; You also need to specify the data type of your variable; I’m glad to see postgresql supports %TYPE and %ROWTYPE.
Use data-modifying CTEs: WITH ins1 AS ( INSERT INTO sample(firstname, lastname) VALUES (‘fai55’, ‘shaggk’) — ON CONFLICT DO NOTHING — optional addition in Postgres 9.5+ RETURNING id AS sample_id ) , ins2 AS ( INSERT INTO sample1 (sample_id, adddetails) SELECT sample_id, ‘ss’ FROM ins1 RETURNING user_id ) INSERT INTO sample2 (user_id, value) SELECT user_id, ‘ss2’ … Read more
You can do so starting with Postgres 9.1: with rows as ( INSERT INTO Table1 (name) VALUES (‘a_title’) RETURNING id ) INSERT INTO Table2 (val) SELECT id FROM rows In the meanwhile, if you’re only interested in the id, you can do so with a trigger: create function t1_ins_into_t2() returns trigger as $$ begin insert … Read more
The currently accepted answer seems ok for a single conflict target, few conflicts, small tuples and no triggers. It avoids concurrency issue 1 (see below) with brute force. The simple solution has its appeal, the side effects may be less important. For all other cases, though, do not update identical rows without need. Even if … Read more