PL / pgSQL函数:如何使用execute语句返回表 [英] PL/pgSQL functions: How to return table with an execute statement
本文介绍了PL / pgSQL函数:如何使用execute语句返回表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有这个 PL / pgSQL 函数,它必须返回一些用户信息。
I've got this PL/pgSQL function which must return some users information.
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 ║
╚═══╩════════════╩════════════╩═════════════╝
我认为(但并不确定)问题来自于 EXECUTE
语句,但我无法看到如何处理。
I think (but not sure) the problem comes from the EXECUTE
statement, but I can't see how to do otherwise.
有什么想法? / p>
Any ideas?
推荐答案
你如何执行该功能?它用作select语句。
How are you executing that function? It works as a select statement.
创建表格: 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');
功能: my_function
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;
现在您可以使用*
select * from my_function(1);
查询结果
Result of query
id | firstname | lastname
----+-----------+----------
1 | aaa | bbb
2 | ccc | ddd
或列名称
select id,firstname,lastname from my_function(1);
结果
Result
id | firstname | lastname
----+-----------+----------
1 | aaa | bbb
2 | ccc | ddd
这篇关于PL / pgSQL函数:如何使用execute语句返回表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文