Can Postgres stored functions have both a return value AND out parameters?

I know Oracle and PL/SQL

Compared to what I know about Oracle PL/SQL, I'm not very familiar with PostgreSQL's stored procedures and plpgsql. In Oracle, there are two types of callables:

• Procedures. They can have IN, OUT and IN OUT parameters, but no return values
• Functions. They can have IN, OUT and IN OUT parameters and they MUST return a value

But I'm new to plpgsql

I understand that in plpgsql, all stored procedures are considered functions. To my understanding, this means, they can (but don't have to) always return a value. Now I see on the documentation page, that I can also declare OUT parameters on functions, a thing that's not possible in Oracle. But I don't see an example or any clear statement about whether OUT parameters can be combined with return values. Neither can I see wh开发者_如何学编程ether IN OUT parameters are possible.

So these are my questions:

• Does plpgsql allow IN OUT parameters?
• Does plpgsql allow OUT parameters to be combined with return values? Is this a common practice? Do you have examples for that?

IN and OUT are basically aliases for older syntax.

old way:

create function test(param int) 
returns integer as 
$$ select 1 $$ 
language sql;

equivalent:

create function test(in param int, out int)
as $$ select 1 $$
langauge sql;

What params do provide is type information which basically creates an anonymous type for your return:

create function test(in param, out int, out int)
as $$ select 1, 2 $$
langauge sql;

now you can write:

 select * from test(1);
 column1 | column2 
---------+---------
       1 |       2

Without the out params you would have have had to create a type or table that had two ints to cast the data to the right type:

create or replace function test(in a int) 
returns record as 
as $$ select 1, 2 $$ 
language sql;
                                ^
select * from test(1);
ERROR:  a column definition list is required 
   for functions returning "record"

... actually I should have searched a bit more myself. The answer is not far away on the documentations page:

http://www.postgresql.org/docs/current/static/sql-createfunction.html

if u specified out parameter, it means structure of your result

eg.

create function test(in param, out int, out int)

will return 2 columns of int. in postgre so far i know 2 way to do it.

1 return setof refcursor and use app to read it.

create function test(in param) RETURNS setof refcursor AS
declare result refcursor;
declare parameters refcursor;
begin
 open result for select * from mytable;
 return next result;

 open parameter for select 11 as a, 22 as b;
 return next parameters;
end;

2 use raise notice. In npgsql notice is an event which u can add handler to recieve.

raise notice 'my parameter = %', 11;
return query select * from mytable;

sorry that i didn't make it clear.

1 using 'out' parameter is to specifiy return query structure. u cannot return data + variable. 'out' in postgre doens't mean passing parameter reference.

2 if u want to return data + variable, either method 1 or 2.