TODO for plpgsql: RETURN should accept arbitrary composite expressions
Folks,
On 8.0.4 and 8.1b4 given:
create type return_value as (
id INTEGER,
message TEXT
);
this function:
create function return_test (
vuser INT, vsession INT
) returns return_value as $fnc$
declare vtemp return_value;
begin
vtemp := row( -1, 'bad' );
return vtemp;
end; $fnc$ language plpgsql;
works, but this function:
create function return_test_2 (
vuser INT, vsession INT
) returns return_value as $fnc$
begin
vtemp := row( -1, 'bad' );
end; $fnc$ language plpgsql;
gives this error at run time:
ERROR: syntax error at or near "vtemp" at character 1
QUERY: vtemp := row( -1, 'bad' )
CONTEXT: PL/pgSQL function "return_test_2" line 2 at SQL statement
LINE 1: vtemp := row( -1, 'bad' )
... the problem seems to be that RETURN will accept variables and constants
but not arbitrary composites. We should fix that eventually. Can we put
it on the TODO list?
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
Folks,
Two corrections:
The second example was the wrong code, it should have been this function:
create function return_test_2 (
vuser INT, vsession INT
) returns return_value as $fnc$
begin
return row( -1, 'bad' );
end; $fnc$ language plpgsql;
Also, this issue is documented, but I believe that it still needs fixing,
as current behavior is cumbersome and unintuitive:
"When returning a scalar type, any expression can be used. The expression's
result will be automatically cast into the function's return type as
described for assignments. To return a composite (row) value, you must
write a record or row variable as the expression."
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco