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);
You need to define testx as:
CREATE OR REPLACE FUNCTION testx() RETURNS testtable AS $$
-- Note the "* FROM "
SELECT * FROM fn_test_get_row(1) INTO i_row;
$$ 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.
In response to
pgsql-bugs by date
|Next:||From: Kyle Fox||Date: 2011-08-16 03:31:42|
|Subject: BUG #6164: Flashing dialog|
|Previous:||From: Craig Miles||Date: 2011-08-15 05:54:25|
|Subject: Error calling function which returns a ROWTYPE from within another function|