PostgreSQL存储过程返回选择结果集 [英] Postgresql stored procedure return select result set
问题描述
在Microsoft SQL Server中,我可以执行以下操作:
In Microsoft SQL server I could do something like this :
create procedure my_procedure @argument1 int, @argument2 int
as
select *
from my_table
where ID > @argument1 and ID < @argument2
这将返回具有 my_table中所有列的表 code>。
And that would return me table with all columns from my_table
.
与我在Postgresql中所做的最接近的事情是:
Closest thing to that what I managed to do in postgresql is :
create or replace function
get_test()
returns setof record
as
$$ select * from my_table $$
language sql
或者我可以定义我的表类型,但是手动重新创建技术上已经存在的表是非常不现实的。
or i could define my table type, but manually recreating what technically already exists is very impractical.
create or replace function
get_agent_summary()
returns table (
column1 type, column2 type, ...
)
as
$$
begin
return query select col1, col2, ... from my_existing_table;
...
维护起来很痛苦。
那么,如何在不重新定义要返回的表中的每一列的情况下轻松返回结果集?
So, how can I easily return resultset without redefining defining every single column from table that I want to return?
推荐答案
在Postgres中,一个表会自动定义相应的类型:
In Postgres a table automatically defines the corresponding type:
create or replace function select_my_table(argument1 int, argument2 int)
returns setof my_table language sql as $$
select *
from my_table
where id > argument1 and id < argument2;
$$;
select * from select_my_table(0, 2);
语法比MS SQL Server更冗长,因为您可以使用多种语言之一创建函数,并且函数可能会过载。
The syntax is more verbose than in MS SQL Server because you can create functions in one of several languages and functions may be overloaded.
这篇关于PostgreSQL存储过程返回选择结果集的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!