我有这个PL / pgsql函数,它必须返回一些用户的信息.
CREATE OR REPLACE FUNCTION my_function(user_id integer)
RETURNS TABLE(id integer,firstname character varying,lastname character varying) AS
$$
DECLARE
ids character varying;
BEGIN
ids := '';
--Some code which build the ids string,not interesting for this issue
RETURN QUERY EXECUTE 'SELECT users.id,users.firstname,users.lastname
FROM public.users WHERE ids IN (' || ids || ')';
END;
$$LANGUAGE plpgsql;
我所面临的问题是函数的结果是单列表,如下所示：
╔═══╦═════════════════════╗ ║ ║my_function ║ ╠═══╬═════════════════════╣ ║ 1 ║ (106,Ned,STARK) ║ ║ 2 ║ (130,Rob,STARK) ║ ╚═══╩═════════════════════╝
虽然我预计：
╔═══╦════════════╦════════════╦═════════════╗ ║ ║ id ║ firstname ║ lastname ║ ╠═══╬════════════╬════════════╬═════════════╣ ║ 1 ║ 106 ║ Ned ║ STARK ║ ║ 2 ║ 103 ║ Rob ║ STARK ║ ╚═══╩════════════╩════════════╩═════════════╝
我想(但不确定)问题来自EXECUTE语句,但我看不到如何做到.
有任何想法吗？
你如何执行这个功能？它作为一个选择语句.
create table public.users (id int,firstname varchar,lastname varchar);
insert into public.users values (1,'aaa','bbb'),(2,'ccc','ddd');
CREATE OR REPLACE FUNCTION my_function(user_id integer) RETURNS TABLE(id integer,lastname character varying) AS $$
DECLARE
ids INTEGER[];
BEGIN
ids := ARRAY[1,2];
RETURN QUERY
SELECT users.id,users.lastname
FROM public.users
WHERE users.id = ANY(ids);
END;
$$LANGUAGE plpgsql;
select * from my_function(1);
id | firstname | lastname
----+-----------+----------
1 | aaa | bbb
2 | ccc | ddd
select id,firstname,lastname from my_function(1);
id | firstname | lastname
----+-----------+----------
1 | aaa | bbb
2 | ccc | ddd