By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,760 Members | 1,135 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,760 IT Pros & Developers. It's quick & easy.

Stored procedures output variables

P: n/a
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?

Shachar

--
Shachar Shemesh
Lingnu Open Source Consulting
http://www.lingnu.com/
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #1
Share this Question
Share on Google+
1 Reply

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

Nov 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.