pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedStored Procedure Question
4+ messages / 4 participants
[nested] [flat]
* Stored Procedure Question
@ 2003-04-30 16:52 Martin Chantler <MC44@acw.co.uk>
2003-04-30 18:03 ` Re: Stored Procedure Question Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-04-30 18:03 ` Re: Stored Procedure Question Rajesh Kumar Mallah <rmallah@trade-india.com>
0 siblings, 2 replies; 4+ messages in thread
From: Martin Chantler @ 2003-04-30 16:52 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Stored Procedure Question
2003-04-30 16:52 Stored Procedure Question Martin Chantler <MC44@acw.co.uk>
@ 2003-04-30 18:03 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-05-01 02:16 ` Re: Stored Procedure Question Roberto Mello <rmello@cc.usu.edu>
1 sibling, 1 reply; 4+ messages in thread
From: Stephan Szabo @ 2003-04-30 18:03 UTC (permalink / raw)
To: Martin Chantler <MC44@acw.co.uk>; +Cc: pgsql-sql
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???
They could return sets, but before 7.3 the usefulness was limited
> All examples I have seen don't return a recordset
You might want to see
http://techdocs.postgresql.org/guides/SetReturningFunctions
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Stored Procedure Question
2003-04-30 16:52 Stored Procedure Question Martin Chantler <MC44@acw.co.uk>
2003-04-30 18:03 ` Re: Stored Procedure Question Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2003-05-01 02:16 ` Roberto Mello <rmello@cc.usu.edu>
0 siblings, 0 replies; 4+ messages in thread
From: Roberto Mello @ 2003-05-01 02:16 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: Martin Chantler <MC44@acw.co.uk>; pgsql-sql
On Wed, Apr 30, 2003 at 11:03:02AM -0700, Stephan Szabo wrote:
> 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???
>
> They could return sets, but before 7.3 the usefulness was limited
>
> > All examples I have seen don't return a recordset
>
> You might want to see
> http://techdocs.postgresql.org/guides/SetReturningFunctions
Hmm, this is cool. I can think of a couple documents I could contribute to
this area over the summer.
Is there a mailing list one can sign up to to be informed of new content
in techdocs? That would be useful.
-Roberto
--
+----| Roberto Mello - http://www.brasileiro.net/ |------+
+ Computer Science Graduate Student, Utah State University +
+ USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ +
CU = See You
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Stored Procedure Question
2003-04-30 16:52 Stored Procedure Question Martin Chantler <MC44@acw.co.uk>
@ 2003-04-30 18:03 ` Rajesh Kumar Mallah <rmallah@trade-india.com>
1 sibling, 0 replies; 4+ messages in thread
From: Rajesh Kumar Mallah @ 2003-04-30 18:03 UTC (permalink / raw)
To: Martin Chantler <MC44@acw.co.uk>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-05-01 02:16 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-30 16:52 Stored Procedure Question Martin Chantler <MC44@acw.co.uk>
2003-04-30 18:03 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-05-01 02:16 ` Roberto Mello <rmello@cc.usu.edu>
2003-04-30 18:03 ` Rajesh Kumar Mallah <rmallah@trade-india.com>
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