How to use query result in next query inside Postgres transaction?

Question

I have a query to insert an event in database only if external references (user, session) already exists.

If user doesn't exists it gets inserted and id should be saved
If sessions doesn't exists it is inserted with previous id from user
inserts event using userid and sessions id previously created.

.I get this error:

"PHP Warning: pg_query_params(): Query failed: ERROR: syntax error at or near "IF"

for this query:

BEGIN;
SELECT id FROM users WHERE mongoid = $1 (userid); 
IF not found THEN
    -- inserts user and should remember userid
    INSERT INTO users (mongoid, shopid, idinshop, attributes)
        VALUES ($1, $2, $3, $4) RETURNING id (userid);
END IF;
--looks for session and should remember sessionid
SELECT id FROM sessions WHERE mongoid = $5 (sessionid);
IF not found THEN
     -- inserts session
     INSERT INTO sessions (mongoid, shopid, userid, session, traffic, counts)
               VALUES ($5, $2, (userid), $6, $7, $8) RETURNING id (sessionid);
END IF;
-- finally inserts the event
INSERT INTO events (shopid, sessionid, userid, type, attributes, mongoid) 
     VALUES ($2, (sessionid), (userid), $9, $10, $11);
COMMIT;

LATER EDIT: I solve the issue using the answer below!

BTW, the key word `VALUES` is missing from the 2nd `INSERT`. — Erwin Brandstetter, Sep 13 '13 at 17:32

score 7 · Accepted Answer · edited May 23 '17 at 10:31

You can rewrite your procedural logic with pure SQL in a single SQL statement using data-modifying CTEs.

WITH usr1 AS (SELECT id FROM users WHERE mongoid = $1)
   , usr2 AS (
      INSERT INTO users (mongoid, shopid, idinshop, attributes)
      SELECT $1, $2, $3, $4
      WHERE  NOT EXISTS (SELECT 1 FROM usr1)
      RETURNING id
      )
   , ses1 AS (SELECT id FROM sessions WHERE mongoid = $5)
   , ses2 AS (
      INSERT INTO sessions (mongoid, shopid, userid, session, traffic, counts)
      SELECT $5, $2, (SELECT id FROM usr1 NATURAL FULL OUTER JOIN usr2
           , $6, $7, $8
      WHERE  NOT EXISTS (SELECT 1 FROM ses1)
      RETURNING id
      )
INSERT INTO events (shopid, sessionid, userid, type, attributes, mongoid) 
VALUES ($2
     , (SELECT id FROM usr1 NATURAL FULL OUTER JOIN usr2)
     , (SELECT id FROM ses1 NATURAL FULL OUTER JOIN ses2)
     , $9, $10, $11);

Requires Postgres 9.1 or later.
Untested. Provide a test case in your question if you want tested answers.

Should be considerably faster than individual commands (let alone repeated round trips to the db server!).

Be aware of potential concurrency issues with heavy concurrent load. The single statement presented is already much less likely to cause trouble. But the possibility is there. Possible solutions include manual locking (expensive) advisory locks or serializable transactions (may be expensive, too).

Related answer with more information:
Postgresql batch insert or ignore

thanks, seems to be very close. I do get this error: PHP Warning: pg_query_params(): Query failed: ERROR: INSERT has more target columns than expressions LINE 4: INSERT INTO users (mongoid, shopid, idinshop, attri... ^ HINT: The insertion source is a row expression containing the same number of columns expected by the INSERT. Did you accidentally use extra parentheses? — Alexandru R, Sep 13 '13 at 17:41
I removed parenthesis from INSERT INTO .... SELECT and it works. Thanks! You saved me 4 hours ! — Alexandru R, Sep 13 '13 at 17:54
@AlexandruRada: Right, amended. I forgot to remove parens after switching from `VALUES` to `SELECT`. Also note the chapter about concurrency I added. — Erwin Brandstetter, Sep 13 '13 at 18:13

How to use query result in next query inside Postgres transaction?

1 Answers1