text to point conversion not working. ( cannot cast type text to point )

[Dan Libby <dan@xxxxxxxxx>]

Hi all, 

Using pgsql 8.0.1

I'm just starting with using the geometry data types in postgres, and ran into 
what seems like a very basic problem.  Namely, I can't seem to convert/cast 
type text into type point when that text results from any expression.  Ie, it 
*only* works for a plain string literal.

Examples:

select '1,2'::point;
 point
-------
 (1,2)

  That works with a string literal.  This does not.

select ('1' || ',2')::point;
ERROR:  cannot cast type text to point

  Nor does this.

select cast('1' || ',2' as point);
ERROR:  cannot cast type text to point

  Nor this.

select '1,2'::varchar::point;
ERROR:  cannot cast type character varying to point

  Nor this.

select '1,2'::char::point;
ERROR:  cannot cast type character to point

  This works.  With a string literal.

select point_in('1,2');
 point_in
----------
 (1,2)

  But this does not.  :(

select point_in('1' || ',2');
ERROR:  function point_in(text) does not exist


So, is there a built-in way to do this, or.....?



Background:

I have a hierarchical table where I have coordinate data for only the leaf 
nodes.  I therefore want to find the center of all the leaf nodes under a 
given parent node, and set the parent node coordinate to that center point.

I can calcululate that center point using aggregate functions (min, max) to 
find the necessary x,y values.    So my query would look something like this:

update parent_table set col =
(select (max(pnt[0])-min(pnt[0]))/2+min(pnt[0]) || ',' || 
max(pnt[1])-max(pnt[1])/2+min(pnt[1])  from point_tmp where condition)
where condition2 ;

Where point_tmp.tmp is defined as a point column.

However, when I try to do it, I get a similar error:

   column "col" is of type point but expression is of type text

If the above task can be performed some other way, perhaps I don't require 
string concatenation....



-- 
Dan Libby