Skip site navigation (1) Skip section navigation (2)

Re: Error calling function which returns a ROWTYPE from within another function

From: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
To: Craig Miles <craig(dot)miles0712(at)gmail(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Error calling function which returns a ROWTYPE from within another function
Date: 2011-08-15 07:27:32
Message-ID: 4E48CA64.5000900@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-bugs
On 15.08.2011 08:54, Craig Miles wrote:
> I am experiencing unexpected behaviour on Postgres 9.0.4 using pl/pgsql
> relating to selecting from a function that returns a ROWTYPE into a ROWTYPE
> variable from within another function.
> In the example below I :
>
> 1) Create a table, TESTTABLE and insert a row.
> 2) Create a function FN_TEST_GET_ROW that returns a row of ROWTYPE TESTTABLE
> based on selection of a single row from TESTTABLE
> 3) Create a test harness in the form of a function TESTX that calls
> FN_TEST_GET_ROW with ID=1
> 4) Call the test harness
>
> The error shown below is returned unexpectedly
> ERROR:  invalid input syntax for integer: "(1,Fred)"
>
> I would just expect the values (1, Fred) to be returned which is what
> happens if I execute
>
> SELECT fn_test_get_row(1);
>
> directly.

You need to define testx as:

CREATE OR REPLACE FUNCTION testx() RETURNS testtable AS $$
DECLARE
   i_row testtable;
BEGIN
   -- Note the "* FROM "
   SELECT * FROM fn_test_get_row(1) INTO i_row;

   -- Success
   RETURN i_row;
END;
$$ LANGUAGE plpgsql;

It's surprising at first, but makes sense when you compare the SELECTs 
in testx and fn_test_get_row. In fn_test_get_row:

   SELECT * INTO   i_row FROM testtable WHERE id = a;

The SELECT returns two columns of types integer and varchar, and assigns 
them to the two fields in i_row. In testx you have:

SELECT fn_test_get_row(1) INTO i_row;

That SELECT returns only one column, of type testtable. It can't be 
assigned into i_row, which expects an integer and a varchar.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

In response to

pgsql-bugs by date

Next:From: Kyle FoxDate: 2011-08-16 03:31:42
Subject: BUG #6164: Flashing dialog
Previous:From: Craig MilesDate: 2011-08-15 05:54:25
Subject: Error calling function which returns a ROWTYPE from within another function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group