0

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

  • A do block cannot return anything. And you should really avoid those dreaded quoted identifiers. – a_horse_with_no_name Jan 23 at 9:56
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);
  • Thank you, it works as expected. But could I have the results from anonymous block code in that case? Just to know more about these "DO $$ { ... } END $$;" in PostgreSQL – Naryck Jan 23 at 11:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.