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 — , May 19 '20 at 11:58
Does this answer your question? [Store query result in a variable using in PL/pgSQL](https://stackoverflow.com/questions/12328198/store-query-result-in-a-variable-using-in-pl-pgsql) — Bob Jarvis - Слава Україні, May 19 '20 at 12:08

score 1 · Accepted Answer · answered May 19 '20 at 12:07

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);

Select 1 into variable postgresql?

1 Answers1