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

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(dot)cura(at)gmail(dot)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(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
>>
>
>