Обсуждение: Error calling function which returns a ROWTYPE from within another function

Поиск
Список
Период
Сортировка

Error calling function which returns a ROWTYPE from within another function

От
Craig Miles
Дата:
Dear Postgres Support

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.

See attached SQL file for all scripts.

Create table
============
CREATE TABLE testtable
(
id INTEGER,
name VARCHAR(10)
);

Add Data
========
INSERT INTO testtable (id, name) VALUES (1, 'Fred');

END;
$$ LANGUAGE plpgsql;

Create function
===============
CREATE OR REPLACE FUNCTION fn_test_get_row(a INTEGER)
RETURNS testtable AS $$
DECLARE
i_row testtable;
BEGIN

SELECT *
INTO   i_row
FROM testtable
WHERE id = a;

-- Success
RETURN i_row;

END;
$$ LANGUAGE plpgsql;

Create test function
====================
CREATE OR REPLACE FUNCTION testx()
RETURNS testtable AS $$
DECLARE
i_row testtable;
BEGIN

SELECT fn_test_get_row(1)
INTO   i_row;

-- Success
RETURN i_row;

Execute the test function
=========================
select testx();

Error returned
==============
ERROR:  invalid input syntax for integer: "(1,Fred)"
CONTEXT:  PL/pgSQL function "testx" line 8 at SQL statement

********** Error **********

ERROR: invalid input syntax for integer: "(1,Fred)"
SQL state: 22P02
Context: PL/pgSQL function "testx" line 8 at SQL statement

Вложения

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

От
Heikki Linnakangas
Дата:
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