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(at)acw(dot)co(dot)uk Website/URL: www.acw.co.uk
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>