PostgreSQL：错误：42601：返回“记录”的函数需要列定义列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8605174/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL: ERROR: 42601: a column definition list is required for functions returning "record"
提问by Jeremy Holovacs
(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;
采纳答案by ertx
if you would like to create function returning setof record, you'll need to define column types in your select statement
如果要创建返回 setof 记录的函数，则需要在 select 语句中定义列类型
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;
回答by Erwin Brandstetter
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 additionalSELECTstatement. UseRETURN QUERYandUPDATEwith aRETURNINGclause.
If the user is not_online, default to a plainSELECT.If you don't table-qualify column names (
tablename.columnname) in queries inside the function, be wary of naming conflictsbetween 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 neveruse for column names: like an underscore (_username).If
users.usernameis defined uniquein your table, thenLIMIT 1in the second query is just cruft.
If it is not, then theUPDATEcan update multiple rows, which is most likely wrong.
I assumed a uniqueusernameand deleted the 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.
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 TABLEfor that purpose - like demonstrated above.You do not need a loopfor this simple function.
Every function needs a language declaration.
LANGUAGE plpgsqlin this case.Probably no point in defining a length restriction (
varchar(250)) for the parameter. I simplified to typetext.
你有
DECLARE result record;但没有使用变量。我删除了cruft。您可以直接从 返回记录
UPDATE，这比调用附加SELECT语句要快得多。将RETURN QUERYandUPDATE与RETURNING子句一起使用。
如果用户不是_online，则默认为普通用户SELECT。如果您没有
tablename.columnname在函数内部的查询中对列名 ( ) 进行表限定，请注意列名和命名参数之间的命名冲突，它们在函数内的任何地方（大多数）都是可见的。
您还可以通过$n对参数使用位置引用 ( ) 来避免此类冲突。或者使用您从不用于列名的前缀：例如下划线 (_username)。如果在您的表中
users.username定义为唯一，那么LIMIT 1在第二个查询中就只是 cruft。
如果不是，则UPDATE可以更新多行，这很可能是错误的。
我假设了一个独特的username并删除了 cruft。定义函数的返回类型（就像@ertx 演示的那样），否则你将不得不在每个函数调用中提供一个列定义列表，这很尴尬。
为此目的创建一个类型（如@ertx 提出的）是一种有效的方法，但对于单个函数来说可能有点矫枉过正。在我们
RETURNS TABLE为此目的之前，这是在旧版本的 PostgreSQL 中采用的方法- 就像上面演示的那样。你并不需要一个循环为这个简单的功能。
每个函数都需要一个语言声明。
LANGUAGE plpgsql在这种情况下。varchar(250)为参数定义长度限制 ( )可能没有意义。我简化为键入text.
Return whole table
返回整桌
If you want to return all columnsof 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 usersand vastly simplify the query:
如果要返回table 的所有列users，有一种更简单的方法。PostgreSQL 自动为每个表定义一个同名的复合类型。在这种情况下，您可以使用RETURNS SETOF users并大大简化查询：
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:
如果您需要更“动态”的东西，请考虑：
