Subject: Get error when calling function that returns a table

From: Russell Rose | Passfield Data Systems <[email-masked]>
Date: Mon, 25 Mar 2024 15:43:11 +0000

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

Can anyone help please

Russell Rose

---

From: Tom Lane <[email-masked]>
Date: Mon, 25 Mar 2024 12:26:43 -0400

> CONTEXT: PL/pgSQL function test() line 13 at FETCH

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