Postgres Pro
Downloads
Home   >   mailing lists

Thread: Stored Procedure Question

Stored Procedure Question

From
Martin Chantler
Date:
Hi all,

I am confused about Postgres stored procedures (aka functions), can they 
only return one value???
All examples I have seen don't return a recordset

In MS SQL I have many simple procedures that execute SQL given parameters 
and return
a recordset, possibly many rows long

Example:
CREATE PROCEDURE SECGetMenuItems (@userID as varchar(50)) AS
Select distinct SECTask.TaskName, MenuDisplay, NodeName, ParentNode, Type, 
ProcedureName, TaskType, Icon
From (SECTaskContext inner join SECTask on SECTaskContext.TaskName = 
SECTask.TaskName)
left  join 
((select Type, TaskName from SECPermission inner join SystemUserDetails AS 
SUD on SECPermission.UserName = SUD.UserID where SUD.UserID =  @userID)union 
(select Type, TaskName from SECPermission inner join SECUserGroup on 
SECPermission.UserName = SECUserGroup.GroupName where 
SECUserGroup.MemberName =  @userID))
as Answer on Answer.TaskName = SECTask.TaskName
Where (Hide = 0
or Type is not null)
and Nodename is not null
order by ParentNode

My question is, can I get PostGres procedures to do the same thing?
I am also hoping that they return data to the client in a similar way (I am 
using ODBC)

e.g.:
rs.open "SECGetMenuItems 'MC44'"
then you can treat rs as a normal recordset

Thanks for any help,

Martin Chantler


-----------------------------------------------------------------------
Martin Chantler
Application Developer
ACW Technology Ltd
Comines Way
Hedge End
Southampton
Hants  SO30 4XX    U.K.

Tel:  023 8048 6000                      Fax:  023 8048 6001

International Tel :  +44 23 8048 6000    Fax : +44 23 8048 6001

E-mail: mc44@acw.co.uk     Website/URL:  www.acw.co.uk

Re: Stored Procedure Question

From
Stephan Szabo
Date:
On Wed, 30 Apr 2003, Martin Chantler wrote:

> Hi all,
>
> I am confused about Postgres stored procedures (aka functions), can they
> only return one value???

They could return sets, but before 7.3 the usefulness was limited

> All examples I have seen don't return a recordset

You might want to see
http://techdocs.postgresql.org/guides/SetReturningFunctions

Re: Stored Procedure Question

From
Roberto Mello
Date:
On Wed, Apr 30, 2003 at 11:03:02AM -0700, Stephan Szabo wrote:
> On Wed, 30 Apr 2003, Martin Chantler wrote:
> 
> > Hi all,
> >
> > I am confused about Postgres stored procedures (aka functions), can they
> > only return one value???
> 
> They could return sets, but before 7.3 the usefulness was limited
> 
> > All examples I have seen don't return a recordset
> 
> You might want to see
> http://techdocs.postgresql.org/guides/SetReturningFunctions

Hmm, this is cool. I can think of a couple documents I could contribute to
this area over the summer.

Is there a mailing list one can sign up to to be informed of new content
in techdocs? That would be useful.

-Roberto

-- 
+----|        Roberto Mello   -    http://www.brasileiro.net/  |------+
+       Computer Science Graduate Student, Utah State University      +
+       USU Free Software & GNU/Linux Club - http://fslc.usu.edu/     +
CU  =  See You

Re: Stored Procedure Question

From
Rajesh Kumar Mallah
Date:

All that is possible.

search google for Set returning functions + postgresql

regds
Mallah.

On Wed, 30 Apr 2003, Martin Chantler wrote:

> Hi all,
> 
> I am confused about Postgres stored procedures (aka functions), can they 
> only return one value???
> All examples I have seen don't return a recordset
> 
> In MS SQL I have many simple procedures that execute SQL given parameters 
> and return
> a recordset, possibly many rows long
> 
> Example:
> CREATE PROCEDURE SECGetMenuItems (@userID as varchar(50)) AS
> Select distinct SECTask.TaskName, MenuDisplay, NodeName, ParentNode, Type, 
> ProcedureName, TaskType, Icon
> From (SECTaskContext inner join SECTask on SECTaskContext.TaskName = 
> SECTask.TaskName)
> left  join 
> ((select Type, TaskName from SECPermission inner join SystemUserDetails AS 
> SUD on SECPermission.UserName = SUD.UserID where SUD.UserID =  @userID)
>  union 
> (select Type, TaskName from SECPermission inner join SECUserGroup on 
> SECPermission.UserName = SECUserGroup.GroupName where 
> SECUserGroup.MemberName =  @userID))
> as Answer on Answer.TaskName = SECTask.TaskName
> Where (Hide = 0
> or Type is not null)
> and Nodename is not null
> order by ParentNode
> 
> My question is, can I get PostGres procedures to do the same thing?
> I am also hoping that they return data to the client in a similar way (I am 
> using ODBC)
> 
> e.g.:
> rs.open "SECGetMenuItems 'MC44'"
> then you can treat rs as a normal recordset
> 
> Thanks for any help,
> 
> Martin Chantler
> 
> 
> -----------------------------------------------------------------------
> Martin Chantler
> Application Developer
> ACW Technology Ltd
> Comines Way
> Hedge End
> Southampton
> Hants  SO30 4XX    U.K.
> 
> Tel:  023 8048 6000                      Fax:  023 8048 6001
> 
> International Tel :  +44 23 8048 6000    Fax : +44 23 8048 6001
> 
> E-mail: mc44@acw.co.uk     Website/URL:  www.acw.co.uk
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>