Re: Stored procedures output variables

Shachar Shemesh wrote:
> Hi List,
>
> How do I define a stored procedure that has an output variable? I am
> looking for a way that will not significantly change the way the
> argument is called.
>
> Can I, for example, pass a reference to variable in some way?

If I understand your question, the best way is probably to define a type.

I assume you are intending to return multiple variables, since a single
variable is handled easily ... for example, to return an int:

CREATE FUNCITON returns_int()
RETURNS INT
AS '
DECLARE
BEGIN
RETURN 5;
END;
' LANGUAGE 'plpgsql';

If you want to return multiple variables, there are two different techniques.
1) If you want to return many of the same type of variable, return a set:

CREATE FUNCTION returns_many_int( ... parameters ...)
RETURNS SETOF INT
AS '
...

This will return as many INTs as you need. To the function/command that called
the function, it will look as if a table has been returned i.e. for a SELECT,
you'd do:
SELECT * FROM returns_many_int(... parameters ...);

2) If you want to return different types of values, you'll probably want to
define a custom rowtype:

CREATE TYPE custom_row AS (
col1 INT,
col2 TEXT,
col3 BOOL
);

CREATE FUNCTION returns_custom_type(... parameters ...)
RETURNS custom_row
AS '
...

The result will look like a single row from a table.

You can combine the two, for example if you need to return multiple custom_row's

See the docs on this for more details and much better explanation that I can make:
http://www.postgresql.org/docs/7.4/static/plpgsql.html

Hope this helps.

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