[SQL] 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Parameterized views proposition
What about using PREPARE ? ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Parameterized views proposition
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:[EMAIL PROTECTED] > 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 ? > > ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Parameterized views proposition
On Sat, Mar 12, 2005 at 13:40:30 +0200, Tambet Matiisen <[EMAIL PROTECTED]> 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. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Parameterized views proposition
> > > 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Parameterized views proposition
On 2005-03-12, Bruno Wolff III <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
