Hi All,

I wrote a query that created a view and when I looked at it in the database
the SELECT statement was very different. In part

SELECT items.id,
COALESCE(sales.sum, 0) AS committed,
COALESCE(purchases.sum, 0) AS on_order,
COALESCE(stock.quantity, 0) AS on_hand
FROM peachtree.tbl_item AS items...

was changed to

CREATE OR REPLACE VIEW interface.view_inventory AS

SELECT items.id,
CASE
WHEN (sales.sum IS NOT NULL ) THEN sales.sum
WHEN (0 IS NOT NULL ) THEN (0 )::real
ELSE NULL::real
END AS "committed",
CASE
WHEN (purchases.sum IS NOT NULL ) THEN purchases.sum
WHEN (0 IS NOT NULL ) THEN (0 )::real
ELSE NULL::real
END AS on_order,
CASE
WHEN (stock.quantity IS NOT NULL ) THEN stock.quantity
WHEN (0 IS NOT NULL ) THEN (0 )::real
ELSE NULL::real
END AS on_hand
FROM (((peachtree.tbl_item items

Is this expected behavior? Should I be concerned? Is the modified query
faster? Better in some way? Should I be writing my queries to conform with
the modified syntax?

Kind Regards,
Keith

______________________________________________
99main Internet Services http://www.99main.com


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--- Keith Worthington <keithw@narrowpathinc.com>
escribió:
> Hi All,
>
> I wrote a query that created a view and when I
> looked at it in the database
> the SELECT statement was very different.
> ...
> Is this expected behavior?

Yes. Sometimes postgres will rewrite your queries.

> Should I be concerned?

Actually, no. If a query has to be rewritten Postgres
always will rewrite the query before execution. In a
view it doesn't want to do that every time so it
create the view with the modified query.

> Is the modified query faster?

Sometimes, but not necesarilly.

> Better in some way?

I think is just a way to manage different sintaxis
that do the same. But i can be wrong.

For example queries with field1 IN (value1, value2)
will be rewritten like
(field1 = value1 or field1 = value2)

> Should I be writing my queries to conform with
> the modified syntax?
>
If u want. I do not if u can gain some performance
when the query will be no rewritten. I do not.

regards,
Jaime Casanova

__________________________________________________ _______
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--- Keith Worthington <keithw@narrowpathinc.com>
escribió:
> Hi All,
>
> I wrote a query that created a view and when I
> looked at it in the database
> the SELECT statement was very different. In part
>
> SELECT items.id,
> COALESCE(sales.sum, 0) AS committed,
> COALESCE(purchases.sum, 0) AS on_order,
> COALESCE(stock.quantity, 0) AS on_hand
> FROM peachtree.tbl_item AS items...
>
> was changed to
>
> CREATE OR REPLACE VIEW interface.view_inventory AS
>
> SELECT items.id,
> CASE
> WHEN (sales.sum IS NOT NULL ) THEN
> sales.sum
> WHEN (0 IS NOT NULL ) THEN (0 )::real
> ELSE NULL::real
> END AS "committed",
> CASE
> WHEN (purchases.sum IS NOT NULL ) THEN
> purchases.sum
> WHEN (0 IS NOT NULL ) THEN (0 )::real
> ELSE NULL::real
> END AS on_order,
> CASE
> WHEN (stock.quantity IS NOT NULL ) THEN
> stock.quantity
> WHEN (0 IS NOT NULL ) THEN (0 )::real
> ELSE NULL::real
> END AS on_hand
> FROM (((peachtree.tbl_item items
>
What version of postgres are u using?
I don't see that particular behavior in coalesce.

someone knows if this is the behavior in any older
version of pg?

regards,
Jaime Casanova

__________________________________________________ _______
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Jaime Casanova <systemguards@yahoo.com> writes:
> What version of postgres are u using?
> I don't see that particular behavior in coalesce.

> someone knows if this is the behavior in any older
> version of pg?

Yes. Since about 7.4 COALESCE is a first-class expression node type,
but before that the parser expanded it into a CASE construct.

The CASE implementation is lacking in that it may evaluate the arguments
more than once, which could be wrong if they are volatile values.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--- Tom Lane <tgl@sss.pgh.pa.us> escribió:
> Jaime Casanova <systemguards@yahoo.com> writes:
> > What version of postgres are u using?
> > I don't see that particular behavior in coalesce.
>
> > someone knows if this is the behavior in any older
> > version of pg?
>
> Yes. Since about 7.4 COALESCE is a first-class
> expression node type,
> but before that the parser expanded it into a CASE
> construct.
>
> The CASE implementation is lacking in that it may
> evaluate the arguments
> more than once, which could be wrong if they are
> volatile values.
>
> regards, tom lane
>

So, i think the recommended suggestion will be to
upgrade to a newer version. Maybe you want to wait
until 8 is out.

regards,
Jaime Casanova

__________________________________________________ _______
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org