Thread: Plpgsql functions with output parameters
I have been beating my head against the documentation on plpgsql functions with output parameters for the last three hours, but I haven't been able to get them to work yet.
I am playing with the sum_n_product function, taken from the doucmentation:
-- Function: sum_n_product(integer, integer)
-- DROP FUNCTION sum_n_product(integer, integer);
CREATE OR REPLACE FUNCTION sum_n_product(IN x integer, IN y integer, OUT sum integer, OUT prod integer)
RETURNS record AS
$BODY$
BEGIN
sum := x + y;
prod := x * y;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
RETURNS record AS
$BODY$
BEGIN
sum := x + y;
prod := x * y;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
If I go to a query window and execute "select sum_n_product(2, 3)", I get back "(2,3)". I then wrote a function named "test_sum_n_product" to try to retrieve one of the numbers from inside the record (or whatever it is) that sum_n_product gave me.
Here is my first try:
CREATE or replace FUNCTION test_sum_n_product(x int4, y int4) returns int4 AS $$
declare
sum int4;
product int4;
result record;
BEGIN
select into sum, product sum_n_product($1, $2);
return sum;
END;
$$ LANGUAGE plpgsql;
declare
sum int4;
product int4;
result record;
BEGIN
select into sum, product sum_n_product($1, $2);
return sum;
END;
$$ LANGUAGE plpgsql;
When I call that function from a query window, I get:
ERROR: invalid input syntax for integer: "(5,6)"
CONTEXT: PL/pgSQL function "test_sum_n_product" line 6 at SQL statement
CONTEXT: PL/pgSQL function "test_sum_n_product" line 6 at SQL statement
It seems that it's trying to stuff the entire record into the first variable I gave it. So, I tested that by changing the function to:
CREATE or replace FUNCTION test_sum_n_product(x int4, y int4) returns int4 AS $$
declare
sum int4;
product int4;
result record;
BEGIN
select into result sum_n_product($1, $2);
return 2;
END;
$$ LANGUAGE plpgsql;
declare
sum int4;
product int4;
result record;
BEGIN
select into result sum_n_product($1, $2);
return 2;
END;
$$ LANGUAGE plpgsql;
This function ran successfully, and returned 2. While it confirms my hypothesis that I need to put what I get back from sum_n_product into a record, I'm no closer to getting individual numbers out of the record.
I thought maybe the record's fields would have the names of the output parameters, so I tried:
CREATE or replace FUNCTION test_sum_n_product(x int4, y int4) returns int4 AS $$
declare
sum int4;
product int4;
result record;
BEGIN
select into result sum_n_product($1, $2);
return result.sum;
END;
$$ LANGUAGE plpgsql;
declare
sum int4;
product int4;
result record;
BEGIN
select into result sum_n_product($1, $2);
return result.sum;
END;
$$ LANGUAGE plpgsql;
I got:
ERROR: record "result" has no field "sum"
It seems to me, from working in C++ and C#, that to use an output parameter, I should define a place for the value to be written, and then I should pass the name of that place in to the function. That would make my function look like:
CREATE or replace FUNCTION test_sum_n_product(x int4, y int4) returns int4 AS $$
declare
sum int4;
product int4;
result record;
BEGIN
select into result sum_n_product($1, $2, sum, product);
return sum;
END;
$$ LANGUAGE plpgsql;
declare
sum int4;
product int4;
result record;
BEGIN
select into result sum_n_product($1, $2, sum, product);
return sum;
END;
$$ LANGUAGE plpgsql;
But when I try that, I get:
ERROR: function sum_n_product(integer, integer, integer, integer) does not exist
LINE 1: select sum_n_product( $1 , $2 , $3 , $4 )
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY: select sum_n_product( $1 , $2 , $3 , $4 )
CONTEXT: PL/pgSQL function "test_sum_n_product" line 6 at SQL statement
LINE 1: select sum_n_product( $1 , $2 , $3 , $4 )
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY: select sum_n_product( $1 , $2 , $3 , $4 )
CONTEXT: PL/pgSQL function "test_sum_n_product" line 6 at SQL statement
So, I don't have any more guesses. How do I work with the record that comes back from a function with output parameters?
Thank you very much.
RobR
On Thursday 4. June 2009, Radcon Entec wrote: >I have been beating my head against the documentation on plpgsql > functions with output parameters for the last three hours, but I > haven't been able to get them to work yet. > > >I am playing with the sum_n_product function, taken from the > doucmentation: > >-- Function: sum_n_product(integer, integer) >-- DROP FUNCTION sum_n_product(integer, integer); >CREATE OR REPLACE FUNCTION sum_n_product(IN x integer, IN y integer, > OUT sum integer, OUT prod integer) RETURNS record AS >$BODY$ >BEGIN > sum := x + y; > prod := x * y; >END; >$BODY$ > LANGUAGE 'plpgsql' VOLATILE; > >If I go to a query window and execute "select sum_n_product(2, 3)", I > get back "(2,3)". I then wrote a function named "test_sum_n_product" > to try to retrieve one of the numbers from inside the record (or > whatever it is) that sum_n_product gave me. Hint 1: SELECT * FROM sum_n_product(2, 3); Hint 2: SELECT * FROM sum_n_product(2, 3) INTO foo; -- Leif Biberg Kristensen | Registered Linux User #338009 Me And My Database: http://solumslekt.org/blog/