pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
SQL Server -> Postgres migration: Stored Procedure replacement?
4+ messages / 3 participants
[nested] [flat]

* SQL Server -> Postgres migration: Stored Procedure replacement?
@ 2001-04-29 17:08  Eliel Mamousette <eliel@panix.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Eliel Mamousette @ 2001-04-29 17:08 UTC (permalink / raw)
  To: pgsql-general@postgresql.org

What's the standard methodology in the Postgres world for
creating functions (SQL Server calls them 'stored procedures')
that return more than one column of data per row?

I have tried using the SETOF operand in the CREATE FUNCTION
statement, and that allows me to return more than one row,
but haven't had any luck with specifying more than one return
type and hence more than one column.

Thanks in advance for any assistance,
	eliel






^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: SQL Server -> Postgres migration: Stored Procedure replacement?
@ 2001-04-30 15:28  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Eliel Mamousette <eliel@panix.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Tom Lane @ 2001-04-30 15:28 UTC (permalink / raw)
  To: Eliel Mamousette <eliel@panix.com>; +Cc: pgsql-general@postgresql.org

"Eliel Mamousette" <eliel@panix.com> writes:
> I have tried using the SETOF operand in the CREATE FUNCTION
> statement, and that allows me to return more than one row,
> but haven't had any luck with specifying more than one return
> type and hence more than one column.

You don't specify more than one return type --- you specify one return
type that is a composite type.  Composite types are currently tied to
tables; creating a table also creates a type that represents one of its
rows.  Thus

	create table foo (a int, b int);

	create function foobar (...) returns foo as ...

Note that there are some annoying syntactic limitations on what you can
actually *do* with a function returning tuples.  We have plans to
improve that situation in 7.2 or beyond, but for now, this facility
isn't nearly as useful as one might think.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* RE: SQL Server -> Postgres migration: Stored Procedure replacement?
@ 2001-05-02 22:57  Eliel Mamousette <eliel@panix.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 4+ messages in thread

From: Eliel Mamousette @ 2001-05-02 22:57 UTC (permalink / raw)
  To: pgsql-general@postgresql.org; +Cc: Tom Lane <tgl@sss.pgh.pa.us>

"Tom Lane" <sss.pgh.pa.us> writes:
> "Eliel Mamousette" <eliel@panix.com> writes:
[question re: returning rows deleted to conserve bits]
> 
> You don't specify more than one return type --- you specify one return
> type that is a composite type.  Composite types are currently tied to
> tables; creating a table also creates a type that represents one of its
> rows.  Thus
> 
> 	create table foo (a int, b int, c int);
> 	create function foobar (...) returns foo as ...

Does rule also apply to views?

For example, what is the best practice when one doesn't want to return
a whole row?  Given the restriction as stated, if I only wanted to
return column a and c from the table above, would I create a view
fooview and then say that function foobarview returns fooview?

If I write a paragraph about this process, to whom should I mail it for
inclusion in the documentation?  I imagine it will be a FAQ for we who
are striving to escape from the legacy of Sybase and Microsoft's SQL
Server....

> Note that there are some annoying syntactic limitations on what you can
> actually *do* with a function returning tuples.  We have plans to
> improve that situation in 7.2 or beyond, but for now, this facility
> isn't nearly as useful as one might think.

Thanks Tom, but can you be a bit more specific about what one can't do
with returned tuples?  It might save some folks (and me) some time.

thanks again, this process has been extremely helpful,
	eliel

> 			regards, tom lane
> 




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: SQL Server -> Postgres migration: Stored Procedure replacement?
@ 2001-05-08 18:31  Roger Wernersson <rw@mindark.com>
  parent: Eliel Mamousette <eliel@panix.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Roger Wernersson @ 2001-05-08 18:31 UTC (permalink / raw)
  To: pgsql-general@postgresql.org

You just use a view. Period.

CREATE VIEW foobarview AS SELECT a, c FROM foo;

What else do you need?

CREATE VIEW foobarview AS SELECT a*2 AS double, b*c AS multiple,
max(a,c) AS maximum FROM foo;

I believe views does it all.

/Roger

Eliel Mamousette wrote:
> 
> "Tom Lane" <sss.pgh.pa.us> writes:
> > "Eliel Mamousette" <eliel@panix.com> writes:
> [question re: returning rows deleted to conserve bits]
> >
> > You don't specify more than one return type --- you specify one return
> > type that is a composite type.  Composite types are currently tied to
> > tables; creating a table also creates a type that represents one of its
> > rows.  Thus
> >
> >       create table foo (a int, b int, c int);
> >       create function foobar (...) returns foo as ...
> 
> Does rule also apply to views?
> 
> For example, what is the best practice when one doesn't want to return
> a whole row?  Given the restriction as stated, if I only wanted to
> return column a and c from the table above, would I create a view
> fooview and then say that function foobarview returns fooview?
> 
> If I write a paragraph about this process, to whom should I mail it for
> inclusion in the documentation?  I imagine it will be a FAQ for we who
> are striving to escape from the legacy of Sybase and Microsoft's SQL
> Server....
> 
> > Note that there are some annoying syntactic limitations on what you can
> > actually *do* with a function returning tuples.  We have plans to
> > improve that situation in 7.2 or beyond, but for now, this facility
> > isn't nearly as useful as one might think.
> 
> Thanks Tom, but can you be a bit more specific about what one can't do
> with returned tuples?  It might save some folks (and me) some time.
> 
> thanks again, this process has been extremely helpful,
>         eliel
> 
> >                       regards, tom lane
> >
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2001-05-08 18:31 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-04-29 17:08 SQL Server -> Postgres migration: Stored Procedure replacement? Eliel Mamousette <eliel@panix.com>
2001-04-30 15:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-05-02 22:57   ` Eliel Mamousette <eliel@panix.com>
2001-05-08 18:31     ` Roger Wernersson <rw@mindark.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