Postgres Pro
Downloads
Home   >   mailing lists

Thread: Question on stored functions

Question on stored functions

From
Gunjeet Singh
Date:
Hi,

I could only find support for defining functions in pl/pgsql.
With a function I can have only one return value. What if I need
to have multiple OUT parameters (i.e multiple return values) ?
Is there any support for something that is similar to an Oracle
stored procedure ?

I understand that I can return a REFCURSOR from a postgres function,
but my question is for a case, say, where I need to return 2 string
variables/values based on some logic in addition to a REFCURSOR.
How do I do that in pl/pgsql ?

Thanks,

Gunjeet

Re: Question on stored functions

From
"scott.marlowe"
Date:
On Wed, 3 Mar 2004, Gunjeet Singh wrote:

>
> Hi,
>
> I could only find support for defining functions in pl/pgsql.
> With a function I can have only one return value. What if I need
> to have multiple OUT parameters (i.e multiple return values) ?
> Is there any support for something that is similar to an Oracle
> stored procedure ?
>
> I understand that I can return a REFCURSOR from a postgres function,
> but my question is for a case, say, where I need to return 2 string
> variables/values based on some logic in addition to a REFCURSOR.
> How do I do that in pl/pgsql ?

You want set returning functions probably:

http://www.postgresql.org/docs/7.4/static/xfunc-sql.html#AEN28798

you can basically return one or more rows of a complex type (i.e. just
like what you get back from a select query).

Re: Question on stored functions

From
Bill Moran
Date:
Gunjeet Singh wrote:
> Hi,
>
> I could only find support for defining functions in pl/pgsql.
> With a function I can have only one return value. What if I need
> to have multiple OUT parameters (i.e multiple return values) ?
> Is there any support for something that is similar to an Oracle
> stored procedure ?
>
> I understand that I can return a REFCURSOR from a postgres function,
> but my question is for a case, say, where I need to return 2 string
> variables/values based on some logic in addition to a REFCURSOR.
> How do I do that in pl/pgsql ?

Example:

create type demotype as (
  value1 text,
  value2 text );

create function returns2strings()
returns demotype
as '
  declare
   rval demotype;
  begin
   rval.value1 := ''some string'';
   rval.value2 := ''some other string'';
   return rval;
  end;
' language 'plpgsql';

--
Bill Moran
Potential Technologies
http://www.potentialtech.com