Re: Parameterized views proposition

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.