Re: SQL "pseudo-variables" for a view
От | Sean Davis |
---|---|
Тема | Re: SQL "pseudo-variables" for a view |
Дата | |
Msg-id | 264855a00807100832y6bc92523u7ad5a10212609295@mail.gmail.com обсуждение исходный текст |
Ответ на | SQL "pseudo-variables" for a view (Brian Hurt <bhurt@janestcapital.com>) |
Список | pgsql-novice |
On Thu, Jul 10, 2008 at 11:12 AM, Brian Hurt <bhurt@janestcapital.com> wrote: > > A more generic-SQL question if I could. > > Say I have a table foo with a column insert_date of type DATE. What I'd > like to do is define a view that works like this: > > CREATE OR REPLACE VIEW vw_foo AS > SELECT > my_date DATE, > foo.* > FROM > foo > WHERE > foo.insert_date >= (my_date - '7 days'::interval) > ; > > The idea here is that you'd select from the view with a query like: > SELECT * FROM vw_foo WHERE my_date = some_date; > > my_date acts as a "pseudo-variable", where the query supplies the date. > > Now, I know the above doesn't work- and unfortunately, a stored procedure > won't work either (which would have been my second choice)- the application > demands a view. > So the question is what's the best way to do this? One possibility I > thought of is to have a second table, call it my_dates, which I populate > with all "possible" dates, which I can link in. This table would be small > (100 years on either side of today means only ~73,000 rows). The problem is > that if I solve this for dates, I'll get told "you did it for dates- why > can't you do it for integers or floats?" > So is there a better way to do this? I don't think that postgresql has parameterized views (correct me if I am wrong). However, you could write something like: CREATE VIEW myview AS ( SELECT f1, f2, f3 FROM mytable ); CREATE FUNCTION myfilter(text, text) RETURNS boolean AS ' select $1 = $2' LANGUAGE SQL; To use it, you would write: SELECT * FROM myview WHERE myfilter(f3, 'paramvalue'); Of course, your myfilter function could be as complicated as you like. The point is that you can still pass in a parameter and get back a view of the corresponding data; you just can't pass the parameter directly to the view as you propose. Sean
В списке pgsql-novice по дате отправления: