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
