From: Martin Chantler <MC44@acw.co.uk>
To: pgsql-sql@postgresql.org
Subject: Stored Procedure Question
Date: Wed, 30 Apr 2003 17:52:00 +0100
Message-ID: <3EAD2E0C@acw.co.uk> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: MC44@acw.co.uk
Subject: Re: Stored Procedure Question
In-Reply-To: <3EAD2E0C@acw.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox