I have a table mytable (id, "someLongURI", status, "userId") and a working query:
UPDATE mytable
SET status = 'IN_WORK'
WHERE "someLongURI" IN (
SELECT "someLongURI"
FROM mytable
WHERE status = 'UNUSED'
AND pg_try_advisory_xact_lock(id)
ORDER BY id ASC
LIMIT 1
FOR UPDATE
)
RETURNING id, "someLongURI";
But now I need to check the "userId" parameter and based on this either select existing row or update (and receive updated row). Something like that (in MySQL this would work):
IF NOT EXISTS (
SELECT 1
FROM mytable tbl
WHERE tbl."userId" = 123
)
THEN
UPDATE mytable tbl
SET tbl.status = 'IN_WORK',
tbl."userId" = 123
WHERE tbl."someLongURI" IN (
SELECT "someLongURI"
FROM tbl.mytable
WHERE tbl.status = 'UNUSED'
AND pg_try_advisory_xact_lock(id)
ORDER BY id ASC
LIMIT 1
FOR UPDATE
)
RETURNING tbl.id, tbl."someLongURI";
ELSE
SELECT tbl.id, tbl."someLongURI"
FROM mytable tbl
WHERE tbl."userId" = 123;
END IF;
I've wrapped it with DO $$ BEGIN {…} END $$; but it didn't work for me. Last thing I came to:
DO
RETURNS TABLE (id INT4, "someLongURI" TEXT) AS
$$
DECLARE
"_userId" INT4;
BEGIN
"_userId" = 123;
IF NOT EXISTS (
SELECT 1
FROM mytable tbl
WHERE tbl."userId" = "_userId"
)
THEN
UPDATE mytable tbl
SET tbl.status = 'IN_WORK'
tbl."userId" = "_userId"
WHERE tbl."someLongURI" IN (
SELECT "someLongURI"
FROM tbl.mytable
WHERE tbl.status = 'UNUSED'
AND pg_try_advisory_xact_lock(id)
ORDER BY id ASC
LIMIT 1
FOR UPDATE
)
RETURNING tbl.id, tbl."someLongURI";
ELSE
SELECT tbl.id, tbl."someLongURI"
FROM mytable tbl
WHERE tbl."userId" = "_userId";
END IF;
END $$;
Can't understand where I need to add "RETURNS" or some other statements inside this anonymous code block to receive the results of query
Best Answer
With Postgres you could do that in a single data modifying CTE without the need for an IF statement: