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
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
