Thread: Updgrade from 7.3 to 7.4

I have just upgraded my development machine from 7.3.4 to 7.4.2 and have a
couple of small questions which I can't find any answers to in the documents or
history.

1) What does the TRIGGER group/user privilege mean when applied to a VIEW?
(after the upgrade all groups that had access rights to views now have the
TRIGGER option too)

2) One of my queries does an ORDER BY on a column that is of type BOX.
This query no longer runs and returns the following error message:
ERROR:  could not identify an ordering operator for type box
HINT:  Use an explicit ordering operator or modify the query.

The only things I can think of doing is splitting the box out into the four
separate values, but how do I do that with a box?
my_box[0] returns a point and my_box[0][0] is not recognised (I tried various
other forms but to no avail).

Any help/hints would be appreciated.

Regards
Donald Fraser

"Donald Fraser" <demolish@cwgsy.net> writes:
> 2) One of my queries does an ORDER BY on a column that is of type BOX.
> This query no longer runs and returns the following error message:
> ERROR:  could not identify an ordering operator for type box

What are you expecting that to do?  Boxes have no natural linear
ordering.

> my_box[0] returns a point and my_box[0][0] is not recognised (I tried various
> other forms but to no avail).

I think (my_box[0])[0] would work to get the lower x-coordinate, if
that's what you're after.

            regards, tom lane

----- Original Message -----
> > "Donald Fraser" <demolish@cwgsy.net> writes:
> > 2) One of my queries does an ORDER BY on a column that is of type BOX.
> > This query no longer runs and returns the following error message:
> > ERROR:  could not identify an ordering operator for type box
>
> What are you expecting that to do?  Boxes have no natural linear
> ordering.
>
> > my_box[0] returns a point and my_box[0][0] is not recognised (I tried
various
> > other forms but to no avail).
>
> I think (my_box[0])[0] would work to get the lower x-coordinate, if
> that's what you're after.
>
> regards, tom lane

Thanks the syntax you gave, (my_box[0])[0], is what I'm looking for.

I checked on my production machine which is still running 7.3.4 and the ORDER
BY for box doesn't seem to follow any pattern! Originally the data returned
would probably have been in the order that it was entered which would have been
correct and thus why I missed seeing that there was a problem.

I had assumed ORDER BY on a box was equivalent to ORDER BY y1, x1, y2, x2 -
that is if you imagine your computer screen as a co-ordinate system then a box
located at the top left hand corner is ordered before a box located at the
bottom right hand corner.
Thinking about it a bit more its quite a silly assumption on my behalf!

Anyway problem solved thanks.

Donald Fraser