Postgresql function returning select query
Posted on 2002-07-10
I'm pretty novice to Postgresql, and I wonder how I could do this ...
In M$ SQL Server, you can create a procedure like this:
create procudure test () as
select * from mytable
In Postgresql, you can create functions returning rows too. You have to declare your return value as the name of a table.
In the above case, this would be:
create function test() returns setof mytable as
' select * from mytable
' language 'sql';
But, what if I wanted to join two tables, how do I return results then ?
What I want, is the equivalent to this M$ SP:
create procedure test ()
select field1, field2 from table1, table2
where table1.field = table2.field
I want this because I like to create procedures (or functions) that hold my queries, rather than having them executed from within some other part of my code.
In Visual Basic, I'm used to creating ADO recordsets based on simple stored procedures like the one above. I'd like to do similar things in Postgresql.