Re: Error with "return query" ( "return next" working ) with custom type

> 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?

hello,
this is indeed a bit surprising and RETURN NEXT seems not to respond consistently with RETURN QUERY.
my first expectation was to get a single column of composite type (int, int,int,int), but in fact the function will return 4 int columns,
but withe the return type "SETOF fake_topogeometry", the function will return 4 columns according to the type definition

So following works:

CREATE or replace FUNCTION testTopogeom()
RETURNS SETOF fake_topogeometry AS
$BODY$
BEGIN
RETURN NEXT (1,1,1,1);
-- FAILS: RETURN QUERY SELECT (5,5,5,5)::fake_topogeometry;
RETURN QUERY SELECT 3,3,3,3;
RETURN;
END ;
$BODY$
LANGUAGE plpgsql IMMUTABLE;

SELECT * FROM testTopogeom();

topology_id layer_id id a_type
----------- -------- -- ------
1 1 1 1
3 3 3 3

When using RETURN NEXT, Postgres will try to cast the result to the function output type.
NEXT (1,1,1,1)::fake_topogeometry is actually wrong, but it will be casted correctly.

for comparison:

SELECT (5,5,5,5)::fake_topogeometry;

row
fake_topogeometry
------------------
(5,5,5,5)

Doing the same test with a table type instead of a custom type works the other way.
This is consistent as the table definition has only one column.
here you get a single column of composite type.
(And I could not find a syntax to get RETURN NEXT working)

create table foo (a fake_topogeometry);

CREATE or replace FUNCTION testTopogeom_foo()
RETURNS SETOF foo AS
$BODY$
BEGIN
-- FAILS: RETURN NEXT (1,1,1,1)::fake_topogeometry;
RETURN QUERY SELECT (1,2,3,4)::fake_topogeometry;
RETURN;
END ;
$BODY$
LANGUAGE plpgsql IMMUTABLE;

select testTopogeom_foo();

testTopogeom_foo
foo
---------------
("(1,2,3,4)")

> 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);
>
>