From: Tom Lane <tgl@sss.pgh.pa.us>
To: Russell Rose | Passfield Data Systems <russellrose@passfield.co.uk>
cc: "pgsql-sql@lists.postgresql.org" <pgsql-sql@lists.postgresql.org>
Subject: Re: Get error when calling function that returns a table
In-reply-to: 
 <VI1P191MB0638AA48C8FA4AC3CC71B5B9F2362@VI1P191MB0638.EURP191.PROD.OUTLOOK.COM>
References: 
 <VI1P191MB0638AA48C8FA4AC3CC71B5B9F2362@VI1P191MB0638.EURP191.PROD.OUTLOOK.COM>
Comments: In-reply-to Russell Rose | Passfield Data Systems
 <russellrose@passfield.co.uk>
	message dated "Mon, 25 Mar 2024 15:43:11 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2499066.1711384003.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 25 Mar 2024 12:26:43 -0400
Message-ID: <2499067.1711384003@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2499067.1711384003%40sss.pgh.pa.us>
Precedence: bulk

Russell Rose | Passfield Data Systems <russellrose@passfield.co.uk> writes=
:
> I have got a procedure (test.sql) and this calls a function (test_rr.sql=
) which returns a table. I use the SQL:
> Call test();

> I get the error:
> ERROR: invalid input syntax for type integer: "(14891,0,"VIMALBA ","P901=
 ",111.000000)"
> CONTEXT: PL/pgSQL function test() line 13 at FETCH
> SQL state: 22P02

Didn't test, but I think this:

    DECLARE my_cur  scroll CURSOR FOR
        SELECT test_rr() ;

needs to be more like

    DECLARE my_cur  scroll CURSOR FOR
        SELECT * FROM test_rr() ;

As you have it, the cursor produces a single composite column,
which isn't consistent with

    FETCH FROM my_cur INTO ret1, ret2, ret3, ret4, ret5 ;

I vaguely recall that plpgsql will sometimes let you be sloppy
about composites versus separate columns, but evidently not here.

			regards, tom lane