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
