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

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