From: Bruno Wolff III <bruno@wolff.to>
To: Tambet Matiisen <t.matiisen@aprote.ee>
Cc: pgsql-sql@postgresql.org
Subject: Re: Parameterized views proposition
Date: Sat, 12 Mar 2005 10:34:57 -0600
Message-ID: <20050312163457.GC22317@wolff.to> (raw)
In-Reply-To: <A66A11DBF5525341AEF6B8DE39CDE77008804C@black.aprote.com>
References: <A66A11DBF5525341AEF6B8DE39CDE77008804C@black.aprote.com>
On Sat, Mar 12, 2005 at 13:40:30 +0200,
Tambet Matiisen <t.matiisen@aprote.ee> wrote:
> Hi there!
>
> We use views in our applications a lot, in fact we prefer to have least
> sql at client side. All queries are written as select * from view,
> whenever possible.
>
> But there are queries, which are impossible to express as views.
I don't think this is literally what you mean, since any select query
can be made into a view.
What may be hard is creating a simple view where you can supply parameters
to the view. This is especially going to be true if you want to use *
to select the columns and don't want extra columns that you might need
to paramterize the view.
> 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.
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: bruno@wolff.to, t.matiisen@aprote.ee
Subject: Re: Parameterized views proposition
In-Reply-To: <20050312163457.GC22317@wolff.to>
* 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