Re: Error with "return query" ( "return next" working ) with custom type - Mailing list pgsql-general

From Marc Mamin
Subject Re: Error with "return query" ( "return next" working ) with custom type
Date
Msg-id B6F6FD62F2624C4C9916AC0175D56D880CE3A9D5@jenmbs01.ad.intershop.net
Whole thread Raw
In response to Error with "return query" ( "return next" working ) with custom type  (Rémi Cura <remi.cura@gmail.com>)
List pgsql-general

> Hey dear lists,
> Here is a self contained example showing strange behavior from a real life example concerning the use of postgis_topology topogeometry type.
>
>
> The problem is :
> when trying to return setof topogeometry,
> the "return query"  gives an error of type where there is none, and the return next is working fine.
> The precise error message is ERROR 42804
>
>     "ERROR:  structure of query does not match function result type
>     DETAIL:  Returned type ttt.fake_topogeometry does not match expected type integer in column 1.
>     CONTEXT:  PL/pgSQL function ttt.testtopogeom(ttt.fake_topogeometry) line 9 at RETURN QUERY
>     "
>
> Is it ok, postres bug, postgis bug?
> What are the possible corrections?

hello,
this is indeed a bit surprising and RETURN NEXT seems not to respond consistently with RETURN QUERY.
my first expectation was to get a single column of composite type (int, int,int,int), but in fact the function will return 4 int columns,
but withe the return type "SETOF fake_topogeometry", the function will return 4 columns according to the type definition

So following works:

    CREATE or replace FUNCTION testTopogeom()
    RETURNS SETOF fake_topogeometry AS
    $BODY$
         BEGIN
           RETURN          NEXT (1,1,1,1);
-- FAILS:  RETURN QUERY  SELECT (5,5,5,5)::fake_topogeometry;
           RETURN QUERY  SELECT 3,3,3,3;
         RETURN;
    END ;
    $BODY$
    LANGUAGE plpgsql IMMUTABLE;

    SELECT * FROM testTopogeom();
    
    topology_id  layer_id  id a_type
    -----------  --------  -- ------
    1            1         1  1
    3            3         3  3
    
When using RETURN NEXT, Postgres will try to cast the result to the function output type.
NEXT (1,1,1,1)::fake_topogeometry is actually wrong, but it will be casted correctly.

for comparison:

    SELECT (5,5,5,5)::fake_topogeometry;
    
    row
    fake_topogeometry
    ------------------
    (5,5,5,5)
    
Doing the same test with a table type instead of a custom type works the other way.
This is consistent as the table definition has only one column.
here you get a single column of composite type.
(And I could not find a syntax to get RETURN NEXT working)


    create table foo (a fake_topogeometry);

    CREATE or replace FUNCTION testTopogeom_foo()
    RETURNS SETOF foo AS
    $BODY$
      BEGIN
      --  FAILS:  RETURN NEXT (1,1,1,1)::fake_topogeometry;
        RETURN QUERY SELECT (1,2,3,4)::fake_topogeometry;
      RETURN;
    END ;
    $BODY$
    LANGUAGE plpgsql IMMUTABLE;

    select testTopogeom_foo();

    testTopogeom_foo
    foo
    ---------------
    ("(1,2,3,4)")


> Here is the self contained code stored in the "ttt" schema.
>
>     DROP SCHEMA IF EXISTS ttt CASCADE;
>     CREATE SCHEMA ttt;
>     DROP TYPE IF EXISTS ttt.fake_topogeometry CASCADE;
>     CREATE TYPE ttt.fake_topogeometry AS
>        (topology_id integer,
>         layer_id integer,
>         id integer,
>         a_type integer);
>     DROP FUNCTION IF EXISTS ttt.testTopogeom(test_topom ttt.fake_topogeometry);
>     CREATE FUNCTION ttt.testTopogeom(test_topom ttt.fake_topogeometry)
>     RETURNS SETOF ttt.fake_topogeometry AS
>     $BODY$
>     -- this function is an empty function to test return of multiple topogeom
>     DECLARE
>     the_topo ttt.fake_topogeometry;
>     BEGIN
>     RETURN NEXT (1,1,1,1)::ttt.fake_topogeometry;
>     --RETURN NEXT (3,3,3,3)::ttt.fake_topogeometry;
>     RETURN QUERY  SELECT (1,1,1,1)::ttt.fake_topogeometry as foo;
>     -- UNION
>     --SELECT  (3,3,3,3)::ttt.fake_topogeometry as foo
>     RETURN  ;
>     END ;
>     $BODY$
>     LANGUAGE plpgsql IMMUTABLE;
>     SELECT *
>     FROM ttt.testTopogeom((2,2,2,2)::ttt.fake_topogeometry);
>
>

pgsql-general by date:

Previous
From: Stephen Frost
Date:
Subject: Re: Monitoring number of backends
Next
From: Shaun Thomas
Date:
Subject: Re: Backup Question