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

[Steve Grey]

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.cura at gmail.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.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20131023/21984d4b/attachment.html>