在PostgreSQL中的函数中，将一个子查询的结果赋值给一个变量。

Question

我正在尝试编写一个函数来检查关系中新条目中的日期是否大于其他关系中的条目。如果是这样，我希望将我的新关系中的日期值更新为另一关系中的日期值：

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;

我在set new.date = (select date from playlist where new.name = other.name )处遇到了语法错误。

然而，下面的代码可以正常运行：

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;

为什么会这样呢？

- krise

---

关键字 set 在 plpgsql 的赋值语句中是多余的。顺便说一下，该函数应该 return new;。 - klin

我们需要查看触发器，以完全正确地获取触发函数。并且始终使用您的Postgres版本。date还是datum？所有涉及的列都定义为NOT NULL和other.name为UNIQUE吗？ - Erwin Brandstetter

那么，你有你的答案吗？ - Erwin Brandstetter

Answer 1

假设所有涉及的列都是NOT NULL且other.name UNIQUE以避免复杂性。

使用一个SELECT查询，分配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;

对于类似以下的触发器：

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

db<>fiddle 这里

SELECT INTO _{(不要与SQL SELECT INTO 混淆，使用它是不被鼓励的)} 可以像= 或 :=一样进行赋值操作。这里没有SET。

如果没有符合条件的行，则普通的 SELECT INTO 会分配 NULL。手册：

...目标将被设置为查询返回的第一行，或者如果查询未返回任何行，则设置为 null。

附加 UNION ALL SELECT NEW.datum LIMIT 1 将默认为原始值。参见：

• 如果未找到记录，则返回一个值

这样，我们只执行 一个 查询并进行 一个 分配。

- Erwin Brandstetter

Answer 2

要将查询结果存储到变量中，请使用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;

这可以通过仅运行一次SELECT来进一步优化。

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;

- user330315