Обсуждение: Error calling function which returns a ROWTYPE from within another function
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
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