[postgis-users] Create View From Spatial Table with libpq

[郭家成]

Thanks for your quick reply, I just did some tests.

select oid,typname from pg_type where typname='geometry' or typname='bytea';
oid       typname
-------------------------
17        bytea
16402   geometry

But Postgre gives me another error message, I list what I tested

Test 1:
   create view "v" as select * from "t" where ST_Intersects("geocol", $1);
   Oid paramtypes[1]={16402};
   The Errmsg: There is no parameter $1

 Test 2:
   create view "v" as select * from "t" where ST_Intersects("geocol", $1);
   Oid paramtypes[1]={17};
   The Errmsg: There is no parameter $1

 Test 3:
   create view "v" as select * from "t" where ST_Intersects("geocol",
$1::geometry);
   Oid paramtypes[1]={16402};
   The Errmsg: There is no parameter $1

 Test 4:
   create view "v" as select * from "t" where ST_Intersects("geocol",
ST_GeomFromWKB($1::bytea));
   Oid paramtypes[1]={17};
   The Errmsg: There is no parameter $1

The 4 tests above all return PGRES_FATAL_ERROR and same errmsg,
Here is another 4 tests:

 Test 5:
   select * from "t" where ST_Intersects("geocol", $1);
   Oid paramtypes[1]={16402};

 Test 6:
   select * from "t" where ST_Intersects("geocol", $1);
   Oid paramtypes[1]={17};

 Test 7:
   select * from "t" where ST_Intersects("geocol", $1::geometry);
   Oid paramtypes[1]={16402};

 Test 8:
   select * from "t" where ST_Intersects("geocol",
ST_GeomFromWKB($1::bytea));
   Oid paramtypes[1]={17};

Test 5 to 8 are return PGRES_TUPLES_OK, and even Test 9.

Test 9:
   select * from "t" where ST_Intersects("geocol",
ST_GeomFromWKB($1::geometry));
   Oid paramtypes[1]={17};
   ::geometry does not match to paramtypes[1], but Test 9 still return OK

So, everything works fine without "CREATE VIEW", weird.




2010/5/24 Mike Toews <mwtoews at gmail.com>

> 2010/5/23 郭家成 <iron1103 at gmail.com>
>
>> Hi Mike,
>> Thanks for your reply, but it still doesn't work.
>> The key point possibly lies in *Oid paramtypes[1]={0}*,
>> Where can I find the ParamType of Geometry or Bytea?
>>
>
> They are defined in the system tables:
>
> select oid, * from pg_type where typname='geometry' or typname='bytea';
>
> I'm not sure if the oid for geometry is constant with different
> installations (mine is 16892). Bytea is always 17, since it is hard-coded
> in src/include/catalog/pg_type.h
>
> -Mike
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100524/f5603c11/attachment.html>