CODEWITHSUNDEEP
sqlpostgresqltransactionsplpgsqlcommon-table-expression
Alexandru R
Alexandru R

Reputation: 8833

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

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

  1. If user doesn't exists it gets inserted and id should be saved
  2. If sessions doesn't exists it is inserted with previous id from user
  3. 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!

Upvotes: 3

Views: 6575

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658472

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

Upvotes: 7

Related Questions