PostgreSQL how to return data from anonymous code block?

Question

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

Answer 1

With Postgres you could do that in a single data modifying CTE without the need for an IF statement:

WITH data AS (
 SELECT tbl.id, tbl."someLongURI"
 FROM mytable tbl
 WHERE tbl."userId" = 123;
), changed AS (
 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 
 )
 AND NOT EXISTS (SELECT * FROM data)
 RETURNING tbl.id, tbl."someLongURI"
)
SELECT *
FROM changed
UNION ALL
SELECT *
FROM data
WHERE NOT EXISTS (SELECT * FROM changed);