Skip site navigation (1) Skip section navigation (2)

Error calling function which returns a ROWTYPE from within another function

From: Craig Miles <craig(dot)miles0712(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Error calling function which returns a ROWTYPE from within another function
Date: 2011-08-15 05:54:25
Message-ID: CAEjcPkSrT10e880OSSV-aRGboZzwRzxPnr-pVYu-E9rj5Rzo6A@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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

Attachment: aaa.sql
Description: application/octet-stream (558 bytes)

Responses

pgsql-bugs by date

Next:From: Heikki LinnakangasDate: 2011-08-15 07:27:32
Subject: Re: Error calling function which returns a ROWTYPE from within another function
Previous:From: Ali KhamisDate: 2011-08-15 04:49:28
Subject: BUG #6163: Installation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group