CODEWITHSUNDEEP
functionpostgresqlsyntax-errorrowtype
Craig Miles
Craig Miles

Reputation: 495

Postgres 9.0.4 : 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.

Create table:

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

Add Data:

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

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;
END;    
$$ LANGUAGE plpgsql;

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

Upvotes: 2

Views: 2274

Answers (2)

Jörn Horstmann
Jörn Horstmann

Reputation: 34054

What also seems to work is to change the select in testx to the following:

SELECT (fn_test_get_row(1)).*
INTO   i_row;

The error message makes sense if you consider that with out the parenthesis and star you are selecting one column of your record type. Postgres then tries to convert this column to the first column type of your result, leading to the error message you gave.

Upvotes: 0

jmz
jmz

Reputation: 5479

I've not seen the RETURNS tablename syntax before. I'd personally use RETURNS RECORD or RETURNS SETOF. Here are the fixed functions for you. What I did was change the testx function to treat fn_test_get_row() as a table, and change the result type of fn_test_get_row() to a set.

CREATE OR REPLACE FUNCTION fn_test_get_row(a INTEGER)
RETURNS SETOF testtable AS $$
DECLARE
    i_row testtable%ROWTYPE;
BEGIN
    SELECT INTO i_row * FROM testtable WHERE id = a;
    RETURN NEXT i_row;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION testx()
RETURNS SETOF testtable AS $$
DECLARE
    i_row testtable%ROWTYPE;
BEGIN
    SELECT INTO i_row * FROM fn_test_get_row(1) AS foo;
    RETURN NEXT i_row;
END;
$$ LANGUAGE plpgsql;

Which gives:

# select testx();
  testx   
---------- 
 (1,Fred)
(1 row)

Upvotes: 1

Related Questions