X-Original-To: pgsql-sql@postgresql.org
Received: from spampd.localdomain (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id C1414475CE5
	for <pgsql-sql@postgresql.org>; Wed, 30 Apr 2003 13:05:24 -0400 (EDT)
Received: from acw.co.uk (unknown [62.253.192.161])
	by postgresql.org (Postfix) with ESMTP id 8434C475C15
	for <pgsql-sql@postgresql.org>; Wed, 30 Apr 2003 13:05:20 -0400 (EDT)
Date: Wed, 30 Apr 2003 17:52:00 +0100
From: Martin Chantler <MC44@acw.co.uk>
To: pgsql-sql@postgresql.org
Subject: Stored Procedure Question
Message-ID: <3EAD2E0C@acw.co.uk>
Mime-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: 7bit
X-Spam-Status: No, hits=-1.8 required=5.0
	tests=BAYES_20,FROM_ENDS_IN_NUMS,MAILTO_TO_SPAM_ADDR
	version=2.50
X-Spam-Level: 
X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp)
X-Archive-Number: 200304/449
X-Sequence-Number: 13150

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