Postgresql stored procedure return select result set

Question

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

And that would return me table with all columns from my_table.

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;
...

and it is pain to maintain.

So, how can I easily return resultset without redefining defining every single column from table that I want to return?

@VaoTsun How to do the thing from Microsoft SQL in PostgreSQL — Reygoch
– Reygoch, Commented Dec 8, 2016 at 9:13

klin · Accepted Answer · 2016-12-08 09:15:25Z

16

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);

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.

answered Dec 8, 2016 at 9:15

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql stored procedure return select result set

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related