Solved

Postgresql function returning select query

Posted on 2002-07-10
5
1,708 Views
Last Modified: 2008-02-01
Hi,

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
go

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 ()
as
  select field1, field2 from table1, table2
  where table1.field = table2.field
go


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.
0
Comment
Question by:vindevogel
5 Comments
 
LVL 1

Expert Comment

by:boolee
ID: 7145218
vindevogel  Could u give me any links to tutorials on postgres stored procedures? I am looking for any such docs..and I'm sorry I cant answer ur question at this time..
0
 
LVL 4

Author Comment

by:vindevogel
ID: 7145602
0
 
LVL 3

Expert Comment

by:rycamor
ID: 7226863
vindevogel - Why not just store your queries as views? Honestly, it seems to me that it is a serious waste of resources to create a procedure simply to process a query. That is exactly what views are for. (And also, that is your answer for how to return a joined query: make a view, and query the view in your stored procedure, if you really must do that...)

Generally, though, you should only use procedures for special data manipulation that you can't handle in a regular SQL query. If you don't want to 'see' the query in your application code, you should just create a standard include file that defines your queries. The whole point of a SQL DBMS is that it has a standardized interface (SQL), so that portability between applications and between DBMS's is fairly simple. Once you start stuffing everything away into stored procedures, you will have a headache every time you migrate. (I have one potential client that now needs to migrate 2500 stored procedures from SQL Server 7 to Oracle. This is a perfect example of someone mistakenly thinking you should use a procedure for everything. It might seem simple at first, but creates many headaches later :-(. )
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7294270

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - PAQ'd and pts removed
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 

Accepted Solution

by:
ComTech earned 0 total points
ID: 7314784
This question will be placed in PAQ and points refunded.

Best regards,
ComTech
Community Support
Administrator @ EE
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question