Date: Sat, 12 Mar 2005 10:34:57 -0600
From: Bruno Wolff III <bruno@wolff.to>
To: Tambet Matiisen <t.matiisen@aprote.ee>
Cc: pgsql-sql@postgresql.org
Subject: Re: Parameterized views proposition
Message-ID: <20050312163457.GC22317@wolff.to>
Mail-Followup-To: Bruno Wolff III <bruno@wolff.to>,
	Tambet Matiisen <t.matiisen@aprote.ee>, pgsql-sql@postgresql.org
References: <A66A11DBF5525341AEF6B8DE39CDE77008804C@black.aprote.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <A66A11DBF5525341AEF6B8DE39CDE77008804C@black.aprote.com>
User-Agent: Mutt/1.5.6i

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.