Re: Error with "return query" ( "return next" working ) with custom type - Mailing list pgsql-general
| From | Marc Mamin |
|---|---|
| Subject | Re: Error with "return query" ( "return next" working ) with custom type |
| Date | |
| Msg-id | B6F6FD62F2624C4C9916AC0175D56D880CE3A9D5@jenmbs01.ad.intershop.net Whole thread Raw |
| In response to | Error with "return query" ( "return next" working ) with custom type (Rémi Cura <remi.cura@gmail.com>) |
| List | pgsql-general |
> 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);
>
>
pgsql-general by date: