Parameterized views proposition - Mailing list pgsql-sql

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.
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.

Fortunately there are functions returning set, which have helped me out
every time. They do exactly what I need and I'm quite happy with them,
but here are some improvements that could be done:

* You have to CREATE TYPE for every function. This is tedious and error
prone - if you decide to add another column, you have to do it in two
places.

* You have to double quote all strings. This is less an issue with 8.0
and dollar quoting, but I still consider dollar quoting a bit hackish
feature, while undoubtedly useful.

* EXPLAIN doesn't show true plan of your query, it only shows function
scan. This makes debugging your queries cumbersome - copy function body,
replace parameters and add explain, copy to psql, watch result, pollute
all your psql history.

I wonder if it could be possible to improve CREATE VIEW syntax by adding
parameters? Something like this:

CREATE VIEW product_sales(date,date) AS
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 $1
and $2
group by p.product_id

I understand, that implementation could be a problem with view currently
being a table with SELECT rule. But setting all inconsistencies aside,
why can't previous query just be macro, which first creates type and
then function returning that type? Like regular CREATE VIEW is just
macro, which first creates table and then SELECT rule on that.

This solves first two problems. About the last problem, if I'm correct,
the regular SQL language functions are inlined into query plan, why it
shouldn't be possible with set returning functions? If you take views as
macros in C, it seems natural, that macros can have parameters.
 Tambet