Обсуждение: Trying to update a box data type column

Hi all,

I got the following table:

CREATE TABLE treecategory (
nleft INT4 NOT NULL,
nright INT4 NOT NULL,
box BOX NOT NULL
);

I can't figure out how I can update the box column using the values at
the nleft & nright columns.

For example if I try:
UPDATE treecategory SET box =
'('||"nleft"||','||"nright"||'),('||"nleft"||','||"nright"||')';
ERROR:  column "box" is of type box but expression is of type text
HINT:  You will need to rewrite or cast the expression.

And if I try to cast type text to box I get:
ERROR:  cannot cast type text to box

How can I solve this problem?

Thanks in advance,
    Ben-Nes Yonatan
    http://www.epoch.co.il

On Tue, May 23, 2006 at 07:13:08PM +0200, Yonatan Ben-Nes wrote:
> UPDATE treecategory SET box =
> '('||"nleft"||','||"nright"||'),('||"nleft"||','||"nright"||')';
> ERROR:  column "box" is of type box but expression is of type text
> HINT:  You will need to rewrite or cast the expression.
>
> And if I try to cast type text to box I get:
> ERROR:  cannot cast type text to box

Try this:

UPDATE treecategory SET box = box(point(nleft, nright), point(nleft, nright));

A hackish way to convert types where no cast exists is to use the
source and destination types' I/O functions:

UPDATE treecategory SET box = box_in(textout( <text expression> ));

You could create a function that implicitly does the above conversion:

CREATE FUNCTION box(text) RETURNS box AS $$
BEGIN
    RETURN $1;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

UPDATE treecategory SET box = box( <text expression> );

If you need to perform such conversions regularly then you could
use CREATE CAST to create a cast between the types.

--
Michael Fuhr

Yonatan Ben-Nes <yonatan@epoch.co.il> writes:
> I can't figure out how I can update the box column using the values at
> the nleft & nright columns.

Use the provided constructor functions:

regression=# select box(point(1,2),point(3,4));
     box
-------------
 (3,4),(1,2)
(1 row)

As a general rule, converting something to text and back is not the way
to convert data from one type to another; if it's not a really
outlandish conversion, there'll be a function or cast to help.

            regards, tom lane