Re: An example for WITH QUERY

On Wed, Jun 22, 2011 at 1:35 PM, Durumdara <durumdara(at)gmail(dot)com> wrote:
> Hi!
>
> I have 3 tables. I want to run a query that collect some data from
> them, and join into one result table.
>
> I show a little example, how to do this in another DB with script:
>
> create temp table tmp_a as select id, name, sum(cost) cost from items
> where... group by id, name with data;
>
> create temp table tmp_b as select item_id, sum(price) price from bills
> where... group by item_id with data;
>
> create temp table tmp_c as select item_id, sum(price) price from
> incoming_bills where... group by item_id with data;
>
> select
>  tmp_a.id, tmp_a.name, tmp_a.cost,
>  tmp_b.price outgoing_price,
>  tmp_c.price incoming_price
> from tmp_a
> left join tmp_b on (tmp_a.id = tmp_b.item_id)
> left join tmp_c on (tmp_a.id = tmp_c.item_id)
> order by name
>
> This is very simple example. How I can write this in "WITH QUERY" form?
>
> Thanks for every help!
>
> dd
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

My understanding is that "WITH" queries are to facilitate the
capturing of a "dynamic" relation and enabling it's usage multiple
times within the query without having to have this "dynamic" result
set or relation regenerated for each use within the larger query.
This would be useful in situations where one had to create a temporary
table then make use of this table multiple times in a complex query.

In your case I see no need to use WITH queries (but I could be wrong).
Perhaps the expert documentation may help see
"http://www.postgresql.org/docs/9.0/interactive/queries-with.html".

Allan.