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

[Rémi Cura]

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/25d33733/attachment.html>