CODEWITHSUNDEEP
postgresqlpostgresql-12
user12694034
user12694034

Reputation:

Select 1 into variable postgresql?

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;

Upvotes: 0

Views: 1798

Answers (1)

user330315
user330315

Reputation:

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

Upvotes: 1

Related Questions