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
aaa.sql
Description: Binary data
-- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
