Re: Stored Procedure Question

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
>