Skip site navigation (1) Skip section navigation (2)

Re: SQL Server stored procedures?

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Todd P Marek <affe23(at)somahq(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: SQL Server stored procedures?
Date: 2004-11-30 04:21:42
Message-ID: 20041130042141.GA87257@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-general
On Mon, Nov 29, 2004 at 07:35:57PM -0600, Todd P Marek wrote:

> I am curious if there is a way to select and return multiple columns 
> from multiple tables without having to first create a view. 

You could use CREATE TYPE to create a composite type with the desired
fields and return SETOF that type.  When I do this, I sometimes
create the type to contain only keys; if I want additional columns
then I join the function results (rows of keys) to the appropriate
tables.

Depending on why you object to creating a view to get a composite
type, you might not want to create a custom type either.  The
function could return SETOF RECORD, but then queries that use it
will have to supply their own column definitions.  I prefer to do
this only when the return rows don't have a fixed format.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

In response to

pgsql-general by date

Next:From: Harvey, Allan ACDate: 2004-11-30 04:49:27
Subject: SCO installation help
Previous:From: Christopher BrowneDate: 2004-11-30 04:09:14
Subject: Re: create stored procedure from temporary table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group