Thread: Parameterized views proposition
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
What about using PREPARE ?
Prepared statements are not really what I'm looking for. Prepared statements only last for the duration of the current database session. I need "permanent prepared statements", if you prefer. Even this is not entirely correct, because I don't want the query plan to be remembered. Tambet > -----Original Message----- > From: PFC [mailto:lists@boutiquenumerique.com] > Sent: Saturday, March 12, 2005 2:08 PM > To: Tambet Matiisen; pgsql-sql@postgresql.org > Subject: Re: [SQL] Parameterized views proposition > > > > What about using PREPARE ? > >
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.
> > > 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
On 2005-03-12, Bruno Wolff III <bruno@wolff.to> wrote: > You need to expose the columns you want to filter on so that they can > be used in WHERE clauses. That doesn't help when you're doing outer joins with additional join restrictions; the semantics of those can't be converted into additional WHERE clauses. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services
... > > 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 noticed that braces after view name are already reserved for renaming view columns. OK, then this syntax won't do. Isn't there some suggestion in SQL standards? Quick search with Google revealed only that FoxPro has parameterized views, but the syntax is quite different. I've heard people talking about parameterized views since I started database programming. So, PostgreSQL could be the first to really implement them :). How tough it would be to implement CREATE VIEW xxx PARAMETERS (yyy) AS zzz; as CREATE TYPE xxx; CREATE FUNCTION xxx(yyy) RETURNING SETOF xxx LANGUAGE sql AS 'zzz'; Would you suggest it as first step in hacking PostgreSQL sources? Tambet
"Tambet Matiisen" <t.matiisen@aprote.ee> writes:
> How tough it would be to implement
> CREATE VIEW xxx PARAMETERS (yyy) AS zzz;
> as
> CREATE TYPE xxx;
> CREATE FUNCTION xxx(yyy) RETURNING SETOF xxx LANGUAGE sql AS 'zzz';
What's the point? It'd be nonstandard anyway, so just use the function.
regards, tom lane
> > "Tambet Matiisen" <t.matiisen@aprote.ee> writes: > > How tough it would be to implement > > > CREATE VIEW xxx PARAMETERS (yyy) AS zzz; > > > as > > > CREATE TYPE xxx; > > CREATE FUNCTION xxx(yyy) RETURNING SETOF xxx LANGUAGE sql AS 'zzz'; > > What's the point? It'd be nonstandard anyway, so just use > the function. > > regards, tom lane > 1. Cleaner syntax. 2. No possible inconsistencies between return type and query result. 3. No strange quoting, like '' and $$. 4. Just to save some keystrokes. Another issue, that has to be handled separately: 5. Show up in EXPLAIN as normal query, not function scan. About standards - maybe it's time to set some standard? :) Now seriously, maybe it's really not right to call it a parameterized view. Maybe instead CREATE FUNCTION syntax could be improved somehow, so that you don't have to CREATE TYPE separately. Tambet