错误“错误:42601:需要列定义列表返回“record”的函数表示声明返回记录类型的函数在其返回值中没有指定列定义列表
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);
返回所有现有表用户的列,有一个更简单的方法。 Postgres 自动为每个表定义一个同名的复合类型。只需使用 RETURNS SETOF 用户即可大大简化查询:
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$;
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);
以上是如何正确定义返回记录或复合类型的 PostgreSQL 函数?的详细内容。更多信息请关注PHP中文网其他相关文章!