Syntax error while using EXECUTE format in Postgresql Function to assign variable

Question

I have been trying to create a function that intends to assign a value to a declared variable, and act accordingly based on that value.

I use EXECUTE format(<SQL statement>) for assigning the value to cnt.

CREATE OR REPLACE FUNCTION my_function() RETURNS TRIGGER AS $$ 
DECLARE 
    cnt bigint;
BEGIN 
    IF NEW.field1 = 'DECLINED' THEN 
        cnt := EXECUTE format('SELECT count(*) FROM table1 WHERE field2 = $1 AND field1 != $2 AND id != $3;') USING NEW.field2, NEW.field1, NEW.id INTO cnt;
        IF cnt = 0 THEN
             EXECUTE format('UPDATE table1 SET field1 = %1$s WHERE id = $2') USING 'DECLINED', NEW.field2;
         END IF;
    END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER my_trigger BEFORE 
UPDATE OF field1 ON table1 FOR EACH ROW WHEN (NEW.field1 = 'DECLINED') EXECUTE FUNCTION my_function();

However, I am getting the following error:

ERROR:  syntax error at or near "("
LINE 7:         cnt := EXECUTE format('SELECT count(*) FROM...

Not sure if it is relevant, but id is a text column, field1 is an ENUM, and field2 is also a text column. Could that be a problem in the SELECT statement?

Any ideas what I could be missing?

Am I missing something? I cannot see anything dynamic about the statements. Why do you use `EXECUTE` and not just fire the statements as they are? — sticky bit, Dec 27 '20 at 14:59
@stickybit I only want to fire the second `EXECUTE` statement if `cnt` equals to 0. That's why I use the first `EXECUTE`. Is there another way to do this? — chris, Dec 27 '20 at 15:01
I am asking why you use `EXECUTE format(SELECT ...` and not just `SELECT ...`... — sticky bit, Dec 27 '20 at 15:03
in that case how would I pass the variables to the `WHERE` clause without `format()`? — chris, Dec 27 '20 at 15:08
Just write it? `... WHERE field2 = new.field2 ...` Variables can be used in statements for values (but not for identifiers, that's when you need dynamic SQL but not for values). — sticky bit, Dec 27 '20 at 15:11
oh ok, I'm not sure what I was thinking either. That worked, thanks! — chris, Dec 27 '20 at 15:48

Lukasz Szozda · Answer 1 · 2020-12-27T15:14:38.350

3

I only want to fire the second statement if cnt equals to 0

It could be rewritten as single statement:

UPDATE table1 
SET field1 = ...
WHERE id = ...
  AND NOT EXISTS (SELECT * 
                  FROM table1 
                  WHERE field2 = ...
                     AND field1 != ... 
                     AND id != ...);

Using it in trigger indicates it is a try to implement partial uniqueness. If so then partial/filtered index is also an option:

CREATE UNIQUE INDEX uq ON table1(id, field1) WHERE field2 = ....;

edited Dec 27 '20 at 15:14

answered Dec 27 '20 at 15:03

Lukasz Szozda

162,964
23
234
275

nice, upvoted. Without `UNIQUE INDEX` will that work in the function? – chris Dec 27 '20 at 15:50
Yes it will, unique index is addendum for future investigation(if applicable) – Lukasz Szozda Dec 27 '20 at 15:51

score 0 · Accepted Answer · answered Jan 08 '21 at 16:18

Although @Lukasz solution may also work. I ended up using the implementation suggested by @stickybit in the comments of the question

Answer:

CREATE OR REPLACE FUNCTION my_function() RETURNS TRIGGER AS $$ 
DECLARE 
    cnt bigint;
BEGIN 
    IF NEW.field1 = 'DECLINED' THEN 
        cnt := ('SELECT count(*) FROM table1 WHERE field2 = NEW.field2 AND field1 != NEW.field1 AND id != NEW.id;')
        IF cnt = 0 THEN
             'UPDATE table1 SET field1 = 'DECLINED' WHERE id = NEW.field2';
         END IF;
    END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Syntax error while using EXECUTE format in Postgresql Function to assign variable

2 Answers2