postgresql PL/pgSQL 函数：如何使用执行语句返回具有多列的普通表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18084936/
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
PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement
提问by Getz
I've got this PL/pgSQLfunction which must return some users information.
我有这个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;
The problem I'm facing is that the result of the function is a single columns table like this:
我面临的问题是该函数的结果是一个像这样的单列表：
╔═══╦═════════════════════╗
║ ║my_function ║
╠═══╬═════════════════════╣
║ 1 ║ (106,Ned,STARK) ║
║ 2 ║ (130,Rob,STARK) ║
╚═══╩═════════════════════╝
While I expected:
虽然我期望：
╔═══╦════════════╦════════════╦═════════════╗
║ ║ id ║ firstname ║ lastname ║
╠═══╬════════════╬════════════╬═════════════╣
║ 1 ║ 106 ║ Ned ║ STARK ║
║ 2 ║ 103 ║ Rob ║ STARK ║
╚═══╩════════════╩════════════╩═════════════╝
I think (but not sure) the problem comes from the EXECUTEstatement, but I can't see how to do otherwise.
我认为（但不确定）问题来自EXECUTE声明，但我不知道该怎么做。
Any ideas?
有任何想法吗？
回答by bma
How are you executing that function? It works as a select statement.
你是如何执行该功能的？它用作选择语句。
Create a table: public.users
创建表：public.users
create table public.users (id int, firstname varchar, lastname varchar);
Insert some records:
插入一些记录：
insert into public.users values (1, 'aaa','bbb'),(2,'ccc','ddd');
function: my_function
功能：my_function
CREATE OR REPLACE FUNCTION my_function(user_id integer) RETURNS TABLE(id integer, firstname character varying, lastname character varying) AS $$
DECLARE
ids INTEGER[];
BEGIN
ids := ARRAY[1,2];
RETURN QUERY
SELECT users.id, users.firstname, users.lastname
FROM public.users
WHERE users.id = ANY(ids);
END;
$$ LANGUAGE plpgsql;
Now you can use with *
现在你可以使用 *
select * from my_function(1);
Result of query
查询结果
id | firstname | lastname
----+-----------+----------
1 | aaa | bbb
2 | ccc | ddd
Or with column names as well
或者也有列名
select id,firstname,lastname from my_function(1);
Result
结果
id | firstname | lastname
----+-----------+----------
1 | aaa | bbb
2 | ccc | ddd
回答by selin kama?
Call function like that :
像这样调用函数：
select * from my_function(123);
Not just with select. I did and It works
不仅仅是选择。我做了，它有效
回答by Skysnake
http://www.postgresqltutorial.com/plpgsql-function-returns-a-table/
http://www.postgresqltutorial.com/plpgsql-function-returns-a-table/
there is a difference in the output received from the function depending on the syntax of the selection:
根据选择的语法，从函数收到的输出会有所不同：
select * from myfunction();
and
和
select myfunction();
