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

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

From

Rémi Cura

Date:

22 October 2013, 20:21:42

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

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

From

Marc Mamin

Date:

22 October 2013, 22:54:38

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

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

From

Steve Grey

Date:

23 October 2013, 08:21:32

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@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@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

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

From

Rémi Cura

Date:

23 October 2013, 10:52:33

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@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@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@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

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

From

Rémi Cura

Date:

23 October 2013, 11:39:58

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@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@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@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@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users