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