Select 1 into variable postgresql?

Question

I have this select statement inside a trigger procedure:

  SELECT 1 FROM some_table WHERE "user_id" = new."user_id"
  AND created >= now()::date;

How can i store result in a variable and reuse it in IF statement like this:

IF NOT EXISTS (var_name) THEN ...;

procedure (for now i have select right in IF statement, but i want it separately)

CREATE OR REPLACE FUNCTION add_row() RETURNS TRIGGER AS $$
  BEGIN
    //need to check if row was created around today
    IF NOT EXISTS (SELECT 1 FROM some_table WHERE "user_id" = new."user_id"
  AND created >= now()::date) THEN
      INSERT INTO another_table VALUES(1, 2, 3);
    END IF;
  END;
$$ LANGUAGE plpgsql;

Please edit your question and show us your complete PL/pgSQL function — user330315
– user330315, Commented May 19, 2020 at 11:58
Does this answer your question? Store query result in a variable using in PL/pgSQL — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented May 19, 2020 at 12:08

user330315 · Accepted Answer · 2020-05-19 12:07:50Z

To store the result of a query into a variable, you need to declare a variable. Then you can use select .. into .. to store the result. But I would use a boolean and an exists condition for this purpose.

CREATE OR REPLACE FUNCTION add_row() 
  RETURNS TRIGGER 
AS $$
declare
  l_row_exists boolean;
BEGIN
  select exists (SELECT *
                 FROM some_table 
                 WHERE user_id = new.user_id
                  AND created >= current_date)
    into l_row_exists;

  IF NOT l_row_exists THEN
    INSERT INTO another_table (col1, col2, col3)
    VALUES(1, 2, 3);
  END IF;
END;
$$ LANGUAGE plpgsql;

However, you don't really need an IF statement to begin with. You can simplify this to a single INSERT statement:

INSERT INTO another_table (col1, col2, col3)
SELECT 1,2,3 
WHERE NOT EXISTS (SELECT *
                  FROM some_table 
                  WHERE user_id = new.user_id
                    AND created >= current_date);

Collectives™ on Stack Overflow

Select 1 into variable postgresql?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related