public inbox for [email protected]  

help / color / mirror / Atom feed

Get error when calling function that returns a table
2+ messages / 2 participants
[nested] [flat]

* Get error when calling function that returns a table
@ 2024-03-25 15:43  Russell Rose | Passfield Data Systems <[email protected]>
  0 siblings, 1 reply; 2+ messages in thread

From: Russell Rose | Passfield Data Systems @ 2024-03-25 15:43 UTC (permalink / raw)
  To: [email protected] <[email protected]>

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



Attachments:

  [application/octet-stream] test_rr.sql (517B, 3-test_rr.sql)
  download

  [application/octet-stream] test.sql (509B, 4-test.sql)
  download

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Get error when calling function that returns a table
@ 2024-03-25 16:26  Tom Lane <[email protected]>
  parent: Russell Rose | Passfield Data Systems <[email protected]>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2024-03-25 16:26 UTC (permalink / raw)
  To: Russell Rose | Passfield Data Systems <[email protected]>; +Cc: [email protected] <[email protected]>

Russell Rose | Passfield Data Systems <[email protected]> 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






^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2024-03-25 16:26 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-03-25 15:43 Get error when calling function that returns a table Russell Rose | Passfield Data Systems <[email protected]>
2024-03-25 16:26 ` Tom Lane <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox