For archive : FOR x IN q_query behaves like RETURN QUERY regarding previously described behavior.
Cheers, Rémi-C 2013/10/23 Rémi Cura <remi.c...@gmail.com> > Hey, > thanks for the answers, > > sorry for the cross post, i didn't know if it was postgis or postgres > issue, hence the double post (removed postgis now). > > I'm afraid I don't understand perfectly the answer. Are you (both) saying > that it is a normal behavior that a function that should return a custom > type doesn't in fact return this custom type, but a number of columns > composing this custom type? > > This seems like at best a strange behavior ! > > The whole point of using custom type is to provide interface, right? > > To be precise, when specifying "return setof fake_topogeometry" I would > expect that the function returns a fake_topogeometry object (like the > querry `SELECT (5,5,5,5)::fake_topogeometry`), not some columns ! > > I'm obviously missing something, as > SELECT * FROM testTopogeom(); --returns columns > SELECT testTopogeom(); --returns object > > Could you suggest me some more documentation (other than > http://www.postgresql.org/docs/9.3/static/plpgsql-control-structures.html > )? > > Is this difference between Return Next and return query documented? > > Thanks for your help, > Cheers, > Rémi-C > > > 2013/10/23 Steve Grey <steven.c.r.g...@gmail.com> > >> 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.c...@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-us...@lists.osgeo.org >>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >>> >> >> >> _______________________________________________ >> postgis-users mailing list >> postgis-us...@lists.osgeo.org >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> > >
