Setting a value to the result of a subquery in a function in postgresql

user_9310248 0
Viewed 2675

I'm trying to write a function for a trigger that checks if the date in a new entry in a relation is bigger than the entry in an other relation. If that is the case, I want to update the date value in my new relation to the date value in my other relation: 

create or replace function curDate()
returns trigger as $$
Begin
    if (new.date >= (select date from other where new.name = other.name )) then
        set new.date = (select date from playlist where new.name = other.name );
    end if;
end; $$ language plpgsql;

I get a syntax error on:set new.date = (select date from playlist where new.name = other.name )

However, this works fine:

create or replace function curDate()
    returns trigger as $$
    declare dateVar date;
Begin
    dateVar := (select date from other where new.name = other.name);
    if (new.datum >= dateVar) then
        new.datum = dateVar;
    end if;
end; $$ language plpgsql;

Why is that?

subquery plpgsql postgresql sql
2 Answers

Assuming all involved columns NOT NULL and other.name UNIQUE to avoid complications.

Use one SELECT query assigning NEW.datum:

CREATE OR REPLACE FUNCTION trg_cur_date()
  RETURNS TRIGGER AS
$func$
BEGIN
   SELECT o.datum
   FROM   other o
   WHERE  o.name  = NEW.name
   AND    o.datum < NEW.datum

   UNION ALL SELECT NEW.datum
   LIMIT  1

   INTO   NEW.datum;

   RETURN NEW;  -- required for BEFORE INSERT trigger
END
$func$  LANGUAGE plpgsql;

For a trigger like:

CREATE TRIGGER playlist_ins_bef
BEFORE INSERT ON playlist
FOR EACH ROW EXECUTE PROCEDURE trg_cur_date();

db<>fiddle here

SELECT INTO (not to confused with SQL SELECT INTO, the use of which is discouraged) can do the assignment just like = or :=. No SET here.

If no row qualifies, a plain SELECT INTO would assign NULL. The manual:

... target will be set to the first row returned by the query, or to nulls if the query returned no rows.

Appending UNION ALL SELECT NEW.datum LIMIT 1 defaults to the original value instead. See:

This way, we only ever execute one query with one assignment.

user_9398600

To store the result of a query into a variable, use select into

create or replace function curDate()
returns trigger as $$
Begin
  if (new.date >= (select date from other where new.name = other.name )) then
    select date 
      into new.date 
    from other 
    where new.name = other.name;
  end if;
end; $$ language plpgsql;

This can be further optimized by only running the SELECT once. 

create or replace function curDate()
returns trigger as $$
declare
   l_date date;
Begin
  select date 
    into l_date
  from other 
  where new.name = other.name;

  if (new.date >= l_date) then
    new.date := l_date;
  end if;
end; $$ language plpgsql;
user_3303150
Related