Plpgsql functions with output parameters

From: Radcon Entec <radconentec(at)yahoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Plpgsql functions with output parameters
Date: 2009-06-04 12:10:59
Message-ID: 453361.28796.qm@web43413.mail.sp1.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

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;

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

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;

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;

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;

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

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Grzegorz Jaśkiewicz 2009-06-04 12:18:03 Re: Upgrading Database: need to dump and restore?
Previous Message Carlos Oliva 2009-06-04 12:07:37 Re: Upgrading Database: need to dump and restore?