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

Re: Do Stored Procedures exist (Besides FUNCTIONs)

From: "Joshua b(dot) Jore" <josh(at)greentechnologist(dot)org>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: Art Nicewick <art(dot)nicewick(at)ams(dot)com>,Masse Jacques <jacques(dot)masse(at)bordeaux(dot)cemagref(dot)fr>,<pgsql-novice(at)postgresql(dot)org>
Subject: Re: Do Stored Procedures exist (Besides FUNCTIONs)
Date: 2002-03-04 22:44:58
Message-ID: Pine.BSO.4.40.0203041635500.25700-100000@kitten.greentechnologist.org (view raw or flat)
Thread:
Lists: pgsql-novice
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Mon, 4 Mar 2002, Josh Berkus wrote:
> > 2. Ability to return multiple values  ?
> >
> > Are both of these on the TODO list ?.
>
> Just #2.  And, as I said, there is some limited ability to return
>  mutliple values now.  I just don't know how to use it.  Hopefully
>  another developer will speak up.

I've used the 'SETOF' token to get my functions to return multiple single
attribute tuples. There is some support for returning multiple attributes
but it didn't work well for me so I don't use it.

For the record:

CREATE TABLE bran(
	flake integer,
	fiber integer
);
INSERT INTO bran (1,2);
INSERT INTO bran (2,3);
INSERT INTO bran (3,4);

1 x 1
CREATE FUNCTION blah() RETURNS integer AS '
	SELECT flake FROM bran;
' LANGUAGE 'sql';

returns a single row

1 x n
CREATE FUNCTION blan() RETURNS SETOF integer AS '
	SELECT flake FROM bran;
' LANGUAGE 'sql';

returns all the

n x 1
CREATE FUNCTION blah() RETURNS bran AS '
	SELECT * FROM bran;
' LANGUAGE 'sql';

Returns an oid, use the projection syntax to get the individual
attributes:

SELECT flake(blah()), fiber(blah());


n x n
CREATE FRUNCTION blah() RETURNS SETOF bran AS '
	SELECT * FROM bran;
' LANGUAGE 'sql';

SELECT flake(blah()), fiber(blah());

Joshua b. Jore
http://www.greentechnologist.org

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (OpenBSD)
Comment: For info see http://www.gnupg.org

iD8DBQE8g/jtfexLsowstzcRAmHTAKCaZjZ3OOMVxUo0yiHU0cd9on7PFgCg7VL4
P5l42NyWNSlpOPI+a2a4GBA=
=ed42
-----END PGP SIGNATURE-----


In response to

pgsql-novice by date

Next:From: Jon HassenDate: 2002-03-05 00:27:59
Subject: index item size 4496 exceeds maximum 2713
Previous:From: Jon HassenDate: 2002-03-04 22:18:33
Subject: Index item size 4496 exceeds maximum 2713

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