Re: [postgis-users] Error with "return query" ( "return next" working ) with custom type

try:

RETURN QUERY SELECT 1,1,1,1;

The error message means the cast failed between ttt.fake_topogeometry and
the topology_id (i.e. first) field of the return type of the function,
which isn't what you wanted to do.

Pls. don't cross-post between lists.

On 23 October 2013 01:21, Rémi Cura <remi(dot)cura(at)gmail(dot)com> wrote:

>
>
> 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?
>
>
> 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);
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users(at)lists(dot)osgeo(dot)org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>