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
– sticky bit, Commented Dec 27, 2020 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
– chris, Commented Dec 27, 2020 at 15:01
I am asking why you use EXECUTE format(SELECT ... and not just SELECT ...... — sticky bit
– sticky bit, Commented Dec 27, 2020 at 15:03
in that case how would I pass the variables to the WHERE clause without format()? — chris
– chris, Commented Dec 27, 2020 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
– sticky bit, Commented Dec 27, 2020 at 15:11

Lukasz Szozda · Accepted Answer · 2020-12-27 15:14:38Z

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, 2020 at 15:14

answered Dec 27, 2020 at 15:03

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

chris Over a year ago

nice, upvoted. Without UNIQUE INDEX will that work in the function?

Lukasz Szozda Over a year ago

Yes it will, unique index is addendum for future investigation(if applicable)

chris · Accepted Answer · 2021-01-08 16:18:26Z

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;

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related