Question 240058 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Can't pass args to `DO $$ … END $$` function from Golang lib/pq

I have following SQL (PostgreSQL) query:

DO $$ BEGIN 
    IF EXISTS (SELECT * FROM segment WHERE slug = $1 AND is_active = true) THEN
        RAISE EXCEPTION $2; 
    ELSE 
        INSERT INTO segment (slug) VALUES ($1)
        ON CONFLICT(slug) DO 
            UPDATE SET is_active = true; 
    END IF; 
END $$;

If you wondering why I wrapped IF-ELSE statement inside $$ function: see this question.

Now I trying to use it with arguments from Golang using database/sql and github.com/lib/pq:

_, err := p.Exec(`
    DO $$ BEGIN 
        IF EXISTS (SELECT * FROM segment WHERE slug = $1 AND is_active = true) THEN
            RAISE EXCEPTION $2; 
        ELSE 
            INSERT INTO segment (slug) VALUES ($1)
            ON CONFLICT(slug) DO 
                UPDATE SET is_active = true; 
        ND IF; 
    END $$;`,
slug,
exceptionMsg,
)

But when I run this then I got error with following message:

pq: got 2 parameters but the statement requires 0

Looks like sql-driver doesn’t recognize argument placeholders inside of $$ function.

Firstly I tried use query without $$ function, like so:

IF EXISTS (SELECT * FROM segment WHERE slug = $1 AND is_active = true) THEN
    RAISE EXCEPTION $2; 
ELSE 
    INSERT INTO segment (slug) VALUES ($1)
    ON CONFLICT(slug) DO 
        UPDATE SET is_active = true; 
END IF;

But I got same error as in metioned question:

pq: syntax error at or near "IF"

UPD.
I understand that it will be more simple to split this logic into two separate queries and handle it from golang code, but I want to optimize it and use only one query.

Tags:

3

Answers


  1. 0

    Try modifying your code by bringing the positional placeholders outside the dollar quoted block, so you can pass the parameters to your query without having conflicts, something like this;

    query := `
    DO $$ BEGIN 
        IF EXISTS (SELECT * FROM segment WHERE slug = $1 AND is_active = true) THEN
            RAISE EXCEPTION $2; 
        ELSE 
            INSERT INTO segment (slug) VALUES ($1)
            ON CONFLICT(slug) DO 
                UPDATE SET is_active = true; 
        END IF; 
    END $$;
`

_, err := db.Exec(query,
    slug,
    exceptionMsg,
)
if err != nil {
    // Handle the error
}
    Login or Signup to reply.
  2. 0

    As already pointed out by others, the DO code block doesn’t accept parameters.

    The code block is treated as though it were the body of a function with no parameters, returning void. It is parsed and executed a single time.

    So you should either use a pre-declared function or, alternatively, you can use WITH (or CTE) to achieve pretty much the same thing.

    WITH x AS (
    SELECT *
    FROM segment
    WHERE slug = $1
    AND is_active
)
INSERT INTO segment (slug)
SELECT $1
WHERE NOT EXISTS(SELECT 1 FROM x)
ON CONFLICT (slug)
DO UPDATE SET is_active = true

    And you can use RowsAffected to determine if the query was successful (i.e., an insert, or an on-conflict-update occurred).

    res, err := db.Exec(query, slug)
if err != nil {
    return err
}
num, err := res.RowsAffected()
if err != nil {
    return err
}
if num < 1 {
    return errors.New(exceptionMsg)
}
    Login or Signup to reply.
  3. 0

    I would solve the problem with a trigger:

    CREATE FUNCTION dont_update_active() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   IF OLD.is_active AND NEW.is_active THEN
      RAISE EXCEPTION 'the segment is already active';
   END IF;

   RETURN NEW;
END;$$;

CREATE TRIGGER dont_update_active BEFORE UPDATE OF segment
   FOR EACH ROW EXECUTE FUNCTION dont_update_active();

    Then all you have to run is the INSERT ... ON CONFLICT.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search