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

(Disclaimer: PostgreSQL newbie.)

(免责声明:PostgreSQL新手)。

OK, as far as I can tell, my function properly resembles the samples I've seen. Can someone clue me in as to how I get this to work?

好的，据我所知，我的功能与我所看到的样本很相似。有人能告诉我我是怎么做到这一点的吗?

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;

2 个解决方案

#1

if you would like to create function returning setof record, you'll need to define column types in your select statement

如果您想要创建函数返回的记录集，那么您需要在select语句中定义列类型。

More info

更多信息

Your query should look something like this:

您的查询应该如下所示:

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)

(you will probably need to change the data types)

(您可能需要更改数据类型)

I personaly prefer the types approach. it assures that if the function is edited, all the queries will return correct results. It might be a pain because every time you modify function's arguments you'll need to recreate/drop types aswell tho.

我个人更喜欢类型方法。它确保如果函数被编辑，所有查询将返回正确的结果。这可能是一种痛苦，因为每次修改函数的参数时，都需要重新创建/删除类型。

Eg:

例如:

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;

#2

Return selected columns

CREATE OR REPLACE FUNCTION get_user_by_username(_username text, _online bool)
  RETURNS TABLE (
    user_id int
   ,user_name text
   ,last_activity timestamp
   , ... ) AS
$func$
BEGIN

IF _online THEN
   RETURN QUERY
   UPDATE users u 
   SET    last_activity = current_timestamp
   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$  LANGUAGE plpgsql;

Call:

电话:

SELECT * FROM get_user_by_username('myuser', TRUE)

Major points

• You had DECLARE result record; but didn't use the variable. I deleted the cruft.

  您已经声明了结果记录;但是没有用变量。我删除了繁琐的东西。

• You can return the record directly from the UPDATE, which is much faster than calling an additional SELECT statement. Use RETURN QUERY and UPDATE with a RETURNING clause.
  If the user is not _online, default to a plain SELECT.

  您可以直接从更新中返回记录，这比调用额外的SELECT语句要快得多。使用返回查询，并使用返回子句进行更新。如果用户不在线，默认为普通选择。

• If you don't table-qualify column names (tablename.columnname) in queries inside the function, be wary of naming conflicts between column names and named parameters, which are visible (most) everywhere inside a function.
  You can also avoid such conflicts by using positional references ($n) for parameters. Or use a prefix that you never use for column names: like an underscore (_username).

  如果在函数内的查询中没有表限定列名(tablename.columnname)，那么要注意命名列名称和命名参数之间的冲突，这些参数在函数中随处可见(大多数)。您还可以使用位置引用($n)来避免此类冲突。或者使用一个从未用于列名的前缀:比如下划线(_username)。

• If users.username is defined unique in your table, then LIMIT 1 in the second query is just cruft.
  If it is not, then the UPDATE can update multiple rows, which is most likely wrong.
  I assumed a unique username and deleted the cruft.

  如果用户。在您的表中定义了用户名，然后在第二个查询中限制1只是cruft。如果不是，那么更新可以更新多行，这很可能是错误的。我假设了一个唯一的用户名并删除了cruft。

• Define the return type of the function (like @ertx demonstrated) or you will have to provide a column definition list in every function call, which is awkward.

  定义函数的返回类型(如@ertx演示)，或者在每个函数调用中都必须提供列定义列表，这很尴尬。

• Creating a type for that purpose (like @ertx proposed) is a valid approach, but probably overkill for a single function. That was the way to go in old versions of PostgreSQL before we had RETURNS TABLE for that purpose - like demonstrated above.

  为这个目的创建一个类型(如@ertx所建议的)是一个有效的方法，但是对于单个函数来说可能是过量的。在返回表之前，这是在旧版本的PostgreSQL中使用的方法，就像上面演示的那样。

• You do not need a loop for this simple function.

  对于这个简单的函数，您不需要一个循环。

• Every function needs a language declaration. LANGUAGE plpgsql in this case.

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

• Probably no point in defining a length restriction (varchar(250)) for the parameter. I simplified to type text.

  可能没有必要为参数定义长度限制(varchar(250))。我简化了文本类型。

Return whole table

If you want to return all columns of table users, there is a simpler way. PostgreSQL automatically defines a composite type of the same name for every table. In this case you could use RETURNS SETOF users and vastly simplify the query:

如果您想要返回所有表用户的列，有一种更简单的方法。PostgreSQL自动为每个表定义相同名称的复合类型。在这种情况下，您可以使用返回SETOF用户，并大大简化查询:

CREATE OR REPLACE FUNCTION get_user_by_username(_username text, _online bool)
  RETURNS SETOF users 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$  LANGUAGE plpgsql;

---

If you need something more "dynamic", consider:

如果你需要更“动态”的东西，可以考虑:

• Refactor a PL/pgSQL function to return the output of various SELECT queries
• 重构一个PL/pgSQL函数，以返回各种选择查询的输出。