From: Tambet Matiisen <t.matiisen@aprote.ee>
To: Bruno Wolff III <bruno@wolff.to>
Cc: pgsql-sql@postgresql.org
Subject: Re: Parameterized views proposition
Date: Sat, 12 Mar 2005 20:00:48 +0200
Message-ID: <A66A11DBF5525341AEF6B8DE39CDE77008804F@black.aprote.com> (raw)
>
> > Especially if you would like to put a filter on right side of left
> > join. Consider this query:
> >
> > select p.product_id, coalesce(sum(s.amount), 0)
> > from product p
> > left join sales s on p.product_id = s.product_id and s.date between
> > '2005-01-01' and '2005-01-31' group by p.product_id
> >
> > We would like to have all products listed with sum of their
> sales or 0
> > if there wasn't any. I haven't figured out so far, how to
> write this
> > query as view, so that I can set different filters at client side.
>
> You need to expose the columns you want to filter on so that
> they can be used in WHERE clauses.
>
Exposing columns won't solve my problem. See the previous query -
sales.date ise grouped out and can't be exposed (note that I want all
products listed, regardless if there were sales in this period or not).
Tambet
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: t.matiisen@aprote.ee, bruno@wolff.to
Subject: Re: Parameterized views proposition
In-Reply-To: <A66A11DBF5525341AEF6B8DE39CDE77008804F@black.aprote.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox