How to select into multiple variables inside a trigger function?

Question

This is what I'd like to achieve:

CREATE FUNCTION f() RETURNS trigger AS $$
  BEGIN
    SELECT COUNT(*) AS total_num, SUM(width) AS total_width
    FROM some_table WHERE foo = NEW.foo;
    IF total_num > 0 AND total_width > 100
    THEN
      RAISE EXCEPTION 'this is bad';
    END IF;
    RETURN NEW;
  END;
$$ LANGUAGE plpgsql;

But it's not yet syntactically correct.

I've read I first need to DECLARE the variables (in this case total_num and total_width) so I can use those and use SELECT INTO but I've seen examples with a single variable / SELECT statement only. What if I have more of them?

user330315 · Accepted Answer · 2019-01-09 22:12:32Z

6

You can list multiple variables in the into part. And the declare section needs to come before the first begin:

CREATE FUNCTION f() RETURNS trigger 
AS $$
declare
  total_num bigint;
  total_width bigint;
BEGIN
   SELECT COUNT(*), SUM(width)
       into total_num, total_width
   FROM some_table 
   WHERE foo = NEW.foo;

   IF total_num > 0 AND total_width > 100 THEN
      RAISE EXCEPTION 'this is bad';
   END IF;
   RETURN NEW;
END;
$$ LANGUAGE plpgsql;

answered Jan 9, 2019 at 22:12

user330315

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

Comments

sticky bit · Accepted Answer · 2019-01-09 22:17:28Z

1

Edit: I'm not sure whether the emphasis here is on the use of variables or the actual IF. This is meant as an answer on the latter:

You can do this without variables using HAVING and EXISTS.

IF EXISTS (SELECT ''
                  FROM some_table
                  WHERE foo = new.foo
                  HAVING count(*) > 0
                         AND sum(width) > 100) THEN
  RAISE EXCEPTION 'this is bad';
END IF;

edited Jan 9, 2019 at 22:17

answered Jan 9, 2019 at 22:10

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

1 Comment

tom Over a year ago

The emphasis in my case is actually on the IF. So, thanks, I really like this approach, it's even simpler. I still accept the other one though as that's the answer for the question.

Collectives™ on Stack Overflow

How to select into multiple variables inside a trigger function?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related