[postgis-users] operator is not unique error

[Obe, Regina]

Actually  probably better to do, but may not be required
 
 
 the_geom && CAST(''' || CAST(temp_geom As text)  || '''  as geometry)

________________________________

From: Obe, Regina 
Sent: Monday, October 27, 2008 7:08 AM
To: 'PostGIS Users Discussion'
Subject: RE: [postgis-users] operator is not unique error


Chetan,
I think its this part that may be giving you problems -- 
 
''' || temp_geom || ''' 
 
I'm assuming temp_geom is a geometry and 8.3 has taken out a lot of
default casting behavior.
 
In general I try to avoid having the database do these automatic
castings except when its common because its too unpredicatable from
database platform to platform or even version to version of same db
product.
 
Try changing that to
 
''' || CAST(temp_geom As text) || ''' 
 
Unfortunately I don't have a 8.3 lying around at the moment to test.
 
Also it appears from the small snippet you provided that you are using
old syntax instead of $ quoting, named variables - so its harder to
follow what you are doing.
 
Hope that helps,
Regina
 

________________________________

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Chetan Tiwari
Sent: Monday, October 27, 2008 2:11 AM
To: PostGIS Users Discussion
Subject: [postgis-users] operator is not unique error


Hi All
 
I've been having a bit of trouble after recently upgrading to postgresql
8.3. I have a small function that used to work fine before the upgrade.
The code is in pgsql, and the specific line that is giving me problems
is as follows:
 
execute 'select sum(weight) from ' || quote_ident($1) || '.' ||
quote_ident($2) || ' where the_geom && ''' || temp_geom || ''' order by
sum desc limit 1' into pts_weight;
 
I am trying calculate a weighted sum of all points (defined by
'the_geom') that fall within the bounding box of a polygon defined by
'temp_geom'. The error I get is as follows:
 

ERROR:  operator is not unique: text || geometry
LINE 1: ...ast (st_extent(the_geom) as geometry) && cast(''' ||  $3
||...
                                                             ^
HINT:  Could not choose a best candidate operator. You might need to add
explicit type casts.
QUERY:  SELECT  'select sum(weight) from ' || quote_ident( $1 ) || '.'
|| quote_ident( $2 ) || ' where cast (st_extent(the_geom) as geometry)
&& cast(''' ||  $3  || ''' as geometry) order by sum desc limit 1'
CONTEXT:  PL/pgSQL function "computegrid_quad" line 84 at EXECUTE
statement


********** Error **********

ERROR: operator is not unique: text || geometry
SQL state: 42725
Hint: Could not choose a best candidate operator. You might need to add
explicit type casts.
Context: PL/pgSQL function "computegrid_quad" line 84 at EXECUTE
statement

I did find some information about this online, but can't quite get a
hold on the problem. Any suggestions will be most appreciated.

Thanks much,

Chetan



-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20081027/743b34ed/attachment.html>