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

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;

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 结果记录； 但未使用该变量。我删除了那些残渣。

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

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

如果您在函数内部的查询中没有对列名 (tablename.columnname) 进行表限定，请注意列名和命名参数之间的命名冲突，这些冲突是可见的（大多数）在函数内随处可见。
您还可以通过对参数使用位置引用 ($n) 来避免此类冲突。或者使用您从不使用的列名称前缀：例如下划线 (_username)。

如果 users.username 在表中被定义为unique，那么第二个查询中的 LIMIT 1 就很糟糕了。如果不是，则UPDATE可以更新多行，这很可能是错误。我假设一个唯一的用户名并消除噪音。

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

为此目的创建类型（如 @ertx 提出的）是一种有效的方法，但对于单个函数来说可能有点过分了。在我们使用 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 在下面的评论中添加的问题：

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

Answer 2

如果您想创建返回 setof 记录的函数，则需要在 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;

Answer 3

更改

SELECT * FROM foo(params);

为

SELECT foo(params);

在你的情况下它将是

SELECT get_user_by_username('myuser', true);

Answer 4

我创建了 my_func() ，它使用 [RETURN NEXT][1] 或 [RETURN QUERY][1] 语句返回 SETOF RECORD 类型，如下所示：

CREATE FUNCTION my_func() RETURNS SETOF RECORD AS $
DECLARE 
  row RECORD;
BEGIN
  FOR row IN VALUES ('John','Smith'), ('David','Miller') LOOP
    RETURN NEXT row;
  END LOOP;
  RETURN;
END;    
$ LANGUAGE plpgsql;

或者：

CREATE FUNCTION my_func() RETURNS SETOF RECORD AS $
BEGIN
  RETURN QUERY VALUES ('John','Smith'), ('David','Miller');
END;    
$ LANGUAGE plpgsql;

然后，调用 my_func() 得到了相同的错误，如下所示：

postgres=# SELECT * FROM my_func();
ERROR:  a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM my_func();
                      ^

因此，我在 my_func() 之后使用 AS 设置了一个列定义列表，然后我可以调用 my_func() 时不会出现错误，如下所示：

postgres=# SELECT * FROM my_func() AS (f_n TEXT, l_n TEXT);
  f_n  |  l_n
-------+--------
 John  | Smith
 David | Miller
(2 rows)

此外，在不使用 FROM 子句的情况下调用 my_func() 会出现语法错误，如下所示：

postgres=# SELECT my_func() AS (f_n TEXT, l_n TEXT);
ERROR:  syntax error at or near "("
LINE 1: SELECT my_func() AS (f_n TEXT, l_n TEXT);

并且，在没有 FROM 子句和列定义列表的情况下调用 my_func() 会出现[错误][2]，如下所示：

postgres=# SELECT my_func();
ERROR:  materialize mode required, but it is not allowed in this context