<div dir="ltr">Hey,<div>thanks for the answers,</div><div><br></div><div>sorry for the cross post, i didn't know if it was postgis or postgres issue, hence the double post (removed postgis now).</div><div><br></div><div>
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?</div>
<div><br>This seems like at best a strange behavior !</div><div><br></div><div>The whole point of using custom type is to provide interface, right? <br><br>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 !</div>
<div><br></div><div>I'm obviously missing something, as</div><div><div>SELECT * FROM testTopogeom(); --returns columns</div><div>SELECT testTopogeom(); --returns object</div></div><div><br></div><div>Could you suggest me some more documentation (other than <a href="http://www.postgresql.org/docs/9.3/static/plpgsql-control-structures.html">http://www.postgresql.org/docs/9.3/static/plpgsql-control-structures.html</a>)?<br>
<br>Is this difference between Return Next and return query documented?</div><div><br>Thanks for your help,</div><div>Cheers,</div><div>Rémi-C</div></div><div class="gmail_extra"><br><br><div class="gmail_quote">2013/10/23 Steve Grey <span dir="ltr"><<a href="mailto:steven.c.r.grey@gmail.com" target="_blank">steven.c.r.grey@gmail.com</a>></span><br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><span style="font-family:arial,sans-serif;font-size:13.333333969116211px">try:</span><div><span style="font-family:arial,sans-serif;font-size:13.333333969116211px"><br>
</span></div><div><span style="font-family:arial,sans-serif;font-size:13.333333969116211px">RETURN QUERY SELECT 1,1,1,1;</span></div>
<div><font face="arial, sans-serif"><br></font></div><div><font face="arial, sans-serif">The error message means the cast failed between </font><span style="font-family:arial,sans-serif;font-size:13.333333969116211px">ttt.fake_</span><span style="font-family:arial,sans-serif;font-size:13.333333969116211px">topogeometry and the </span><span style="font-family:arial,sans-serif;font-size:13.333333969116211px">topology_id (i.e. first) field of the return type of the function, which isn't what you wanted to do.</span><font face="arial, sans-serif"><br>
</font><div><br></div><div>Pls. don't cross-post between lists.</div></div></div><div class="gmail_extra"><br><br><div class="gmail_quote"><div><div class="h5">On 23 October 2013 01:21, Rémi Cura <span dir="ltr"><<a href="mailto:remi.cura@gmail.com" target="_blank">remi.cura@gmail.com</a>></span> wrote:<br>
</div></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div class="h5"><div dir="ltr"><br><br>Hey dear lists,<div><br></div><div>Here is a self contained example showing strange behavior from a real life example concerning the use of postgis_topology topogeometry type.</div>
<div><br><br>The problem is :</div>
<div>when trying to return setof topogeometry,</div><div>the "return query" gives an error of type where there is none, and the return next is working fine.</div><div><br></div><div>The precise error message is ERROR 42804</div>
<blockquote style="margin:0px 0px 0px 40px;border:none;padding:0px"><div>"ERROR: structure of query does not match function result type</div><div><div>DETAIL: Returned type ttt.fake_topogeometry does not match expected type integer in column 1.</div>
</div><div><div>CONTEXT: PL/pgSQL function ttt.testtopogeom(ttt.fake_topogeometry) line 9 at RETURN QUERY</div></div><div><div>"</div></div></blockquote><div><br></div><div>Is it ok, postres bug, postgis bug?</div>
<div>
What are the possible corrections?</div><div><br></div><div><br><div><div>Here is the self contained code stored in the "ttt" schema.</div></div></div><blockquote style="margin:0 0 0 40px;border:none;padding:0px">
<div><div><div><br></div></div></div><div><div><div>DROP SCHEMA IF EXISTS ttt CASCADE;</div></div></div><div><div>CREATE SCHEMA ttt;</div></div><div><div><br></div></div><div><div>DROP TYPE IF EXISTS ttt.fake_topogeometry CASCADE;</div>
</div><div><div>CREATE TYPE ttt.fake_topogeometry AS</div></div><div><div> (topology_id integer,</div></div><div><div> layer_id integer,</div></div><div><div> id integer,</div></div><div><div> a_type integer);</div>
</div><div><div><br></div></div><div><div><span style="white-space:pre-wrap"> </span>DROP FUNCTION IF EXISTS ttt.testTopogeom(test_topom ttt.fake_topogeometry);</div></div><div><div><span style="white-space:pre-wrap"> </span>CREATE FUNCTION ttt.testTopogeom(test_topom ttt.fake_topogeometry)</div>
</div><div><div><span style="white-space:pre-wrap"> </span>RETURNS SETOF ttt.fake_topogeometry AS</div></div><div><div><span style="white-space:pre-wrap"> </span>$BODY$</div></div><div><div><span style="white-space:pre-wrap"> </span>-- this function is an empty function to test return of multiple topogeom</div>
</div><div><div><span style="white-space:pre-wrap"> </span>DECLARE</div></div><div><div><span style="white-space:pre-wrap"> </span>the_topo ttt.fake_topogeometry;</div></div><div><div><span style="white-space:pre-wrap"> </span>BEGIN</div>
</div><div><div><span style="white-space:pre-wrap"> </span>RETURN NEXT (1,1,1,1)::ttt.fake_topogeometry;</div></div><div><div><span style="white-space:pre-wrap"> </span>--RETURN NEXT (3,3,3,3)::ttt.fake_topogeometry;</div>
</div><div><div><br></div></div><div><div><span style="white-space:pre-wrap"> </span>RETURN QUERY SELECT (1,1,1,1)::ttt.fake_topogeometry as foo;</div></div><div><div><span style="white-space:pre-wrap"> </span>--<span style="white-space:pre-wrap"> </span>UNION </div>
</div><div><div><span style="white-space:pre-wrap"> </span>--SELECT (3,3,3,3)::ttt.fake_topogeometry as foo</div></div><div><div><span style="white-space:pre-wrap"> </span>RETURN ;</div></div><div><div><span style="white-space:pre-wrap"> </span>END ;</div>
</div><div><div><span style="white-space:pre-wrap"> </span>$BODY$</div></div><div><div><span style="white-space:pre-wrap"> </span>LANGUAGE plpgsql IMMUTABLE;</div></div><div><div><br></div></div><div><div><span style="white-space:pre-wrap"> </span>SELECT *</div>
</div><div><div><span style="white-space:pre-wrap"> </span>FROM ttt.testTopogeom((2,2,2,2)::ttt.fake_topogeometry);</div></div></blockquote><div><br></div></div>
<br></div></div>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>