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

[Rémi Cura]

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.cura at 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.grey at 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.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
>>>
>>
>>
>> _______________________________________________
>> 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-devel/attachments/20131023/fdc60021/attachment.html>