PostgreSQL：错误：42601：返回“record”的函数需要列定义列表

Question

据我所知，我的函数与我看到的示例非常相似。有人可以指点我如何让它起作用吗？

据我所知，我的函数与我看到的示例非常相似。有人可以指点我如何让它起作用吗？

create or replace function get_user_by_username(
    username varchar(250),
    online boolean
    ) returns setof record as $$
declare result record;
begin

    if online then 
        update users
        set last_activity = current_timestamp
        where user_name = username;
    end if;

    return query
    select
        user_id,
        user_name,
        last_activity,
        created,
        email,
        approved,
        last_lockout,
        last_login,
        last_password_changed,
        password_question,
        comment
    from
        users
    where
        user_name = username
    limit 1;

    return;
end;
$$ language plpgsql;

- Jeremy Holovacs

Answer 1

返回选定的列

CREATE OR REPLACE FUNCTION get_user_by_username(_username text
                                              , _online bool DEFAULT false)
  RETURNS TABLE (
    user_id int
  , user_name varchar
  , last_activity timestamptz
  )
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF _online THEN
      RETURN QUERY
      UPDATE users u 
      SET    last_activity = current_timestamp  -- ts with time zone
      WHERE  u.user_name = _username
      RETURNING u.user_id
              , u.user_name
              , u.last_activity;
   ELSE
      RETURN QUERY
      SELECT u.user_id
           , u.user_name
           , u.last_activity
      FROM   users u
      WHERE  u.user_name = _username;
   END IF;
END
$func$;

调用:

SELECT * FROM get_user_by_username('myuser', true);

您有一个DECLARE result record;，但没有使用这个变量。我删掉了多余的内容。

您可以直接从UPDATE中返回记录，这比调用额外的SELECT语句要快得多。使用RETURN QUERY和带有RETURNING子句的UPDATE。

如果用户不是_online，则默认使用普通的SELECT。如果省略第二个参数，则也是（安全的）默认值 - 在函数定义中提供了DEFAULT false后才可能发生。

如果在函数内部的查询中不为列名加上表限定符（tablename.columnname），则要注意列名与命名参数之间的名称冲突，它们（大多数情况下）在函数内部的任何地方都可见。
你还可以通过使用参数的位置引用（$n）来避免这种冲突。或者使用一个前缀，你从未用于列名：比如下划线（_username）。

如果您的表中定义了users.username唯一，那么第二个查询中的LIMIT 1只是多余的。如果它不是，则UPDATE可以更新多行，这很可能是错误的。我假设username是唯一的并删去了无用内容。

定义函数的返回类型（如 @ertx所示），否则您必须在每个函数调用中提供一个列定义列表，这很麻烦。

为此创建一个类型（如@ertx建议的）是一个有效的方法，但对于单个函数来说可能有点过度设计。在 Postgres 在拥有RETURNS TABLE之前，这是正确的方式 - 正如上面所示。

这个简单的函数不需要循环。

每个函数都需要语言声明。在这种情况下是LANGUAGE plpgsql。

我使用timestamptz（带时区的时间戳）而不是timestamp（不带时区的时间戳），这是明智的默认值。请参见：

• 忽略Rails和PostgreSQL中的所有时区

返回（一组）完整的行

要返回现有表users的所有列，有一个更简单的方法。Postgres自动为每个表定义了相同名称的复合类型。只需使用RETURNS SETOF users来大大简化查询：

CREATE OR REPLACE FUNCTION get_user_by_username(_username text
                                              , _online bool DEFAULT false)
  RETURNS SETOF users
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF _online THEN
      RETURN QUERY
      UPDATE users u 
      SET    last_activity = current_timestamp
      WHERE  u.user_name = _username
      RETURNING u.*;
   ELSE
      RETURN QUERY
      SELECT *
      FROM   users u
      WHERE  u.user_name = _username;
   END IF;
END
$func$;

返回整行加自定义添加

为了回答下面TheRealChx101在评论中提出的问题：

如果您除了整个表之外还有一个计算值怎么办？

虽然不太简单，但是可行。我们可以将整行类型作为一个字段发送，并添加更多内容：

CREATE OR REPLACE FUNCTION get_user_by_username3(_username text
                                               , _online bool DEFAULT false)
  RETURNS TABLE (
    users_row users
  , custom_addition text
  )
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF _online THEN
      RETURN QUERY
      UPDATE users u 
      SET    last_activity = current_timestamp  -- ts with time zone
      WHERE  u.user_name = _username
      RETURNING u  -- whole row
              , u.user_name || u.user_id;
   ELSE
      RETURN QUERY
      SELECT u, u.user_name || u.user_id
      FROM   users u
      WHERE  u.user_name = _username;
   END IF;
END
$func$;

“魔法”在函数调用中，我们可以（可选）对行类型进行分解：

SELECT (users_row).*, custom_addition FROM get_user_by_username('foo', true);

db<>fiddle 在这里（显示所有）

---

如果您需要更加“动态”的内容，请考虑：

• 重构一个PL/pgSQL函数，以返回各种SELECT查询的输出

- Erwin Brandstetter

---

非常干净。Ertx让我到了一个可以弄清剩下的部分的地方，但这是一篇很好的写作。我希望它能为其他新手PostgreSQL从业者解决一些困惑。 - Jeremy Holovacs

如果除了整个表之外，您还有一个计算出来的值呢？ - TheRealChx101

@TheRealChx101：我新增了第三种解决方案来解决这个问题。 - Erwin Brandstetter

谢谢，这很有帮助。你就像 Stack Overflow 上的数据库教父一样。有时候我会想知道你脑子里在想什么哈哈哈。 - TheRealChx101

Answer 2

如果你想创建返回setof record的函数，那么在select语句中需要定义列类型。

更多信息

你的查询应该长这个样子：

select * from get_user_by_username('Username', True) as 
  f(user_id integer, user_name varchar, last_activity, varchar, created date, email        archar, approved boolean, last_lockout timestamp, last_login timestamp, 
  last_password_changed timestamp, password_question varchar, comment varchar)

（您可能需要更改数据类型）

我个人更喜欢使用类型方法。它确保如果编辑函数，所有查询都将返回正确的结果。但这可能会很麻烦，因为每次修改函数参数时，您也需要重新创建/删除类型。

例如：

CREATE TYPE return_type as 
(user_id integer,
 user_name varchar,
 last_activity varchar,
 created timestamp,
 email varchar,
 approved boolean,
 last_lockout timestamp ,
 last_login timestamp,
 last_password_changed timestamp,
 password_question varchar,
 comment varchar);

create or replace function get_user_by_username( username varchar(250), online 

boolean) returns setof return_type as $$
declare _rec return_type;
begin
    if online then 
        update users
        set last_activity = current_timestamp
        where user_name = username;
    end if;
    for _rec in select
        user_id,
        user_name,
        last_activity,
        created,
        email,
        approved,
        last_lockout,
        last_login,
        last_password_changed,
        password_question,
        comment
      from
        users
      where
        user_name = username
      limit 1 
    loop

      return next _rec;

    end loop

end;
$$ language plpgsql;

- ertx

---

示例如何定义列类型？对我来说，它似乎只是插入行。而return query不是应该将结果发送回去吗？您能否给我一个示例，说明我如何更改我的函数以满足要求？ - Jeremy Holovacs

谢谢...不知道为什么这个信息在我查看的任何文档中都没有显示。 - Jeremy Holovacs

你还可以使用“out”参数，它们有点像声明类型，但你不必管理一个单独的对象。 - araqnid

如此提到，您还可以定义自定义类型，并与json_populate_record一起使用，而无需使用plpgsql即可实现相同的功能- https://dba.stackexchange.com/questions/186033/using-a-type-in-place-of-a-column-definition-list - chrismarx

Answer 3

更改

SELECT * FROM foo(params);

至

SELECT foo(params);

在你的情况下，它将是：

SELECT get_user_by_username('myuser', true);

- kosciej16

Answer 4

由于您只返回单个记录（通过 LIMIT 1），因此您还可以删除 SETOF 并像这样声明函数：

create or replace function x() returns record as $$
declare result record;
begin
  select
    1, 2, 3
  into result;
  return result;
end;
$$ language plpgsql;

然后，从SELECT子句中引用函数，而不是FROM子句（如果未使用类型记录，则仍不可能）。

select x();

结果将如下所示：

|x      |
|-------|
|(1,2,3)|

- Lukas Eder

---

这会抛出错误：返回“record”的函数需要列定义列表。 - agad