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
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?
pgsql-novice by date
|Next:||From: Sean Davis||Date: 2008-07-10 15:32:07|
|Subject: Re: SQL "pseudo-variables" for a view|
|Previous:||From: Leo||Date: 2008-07-10 13:54:27|
|Subject: For Perl users - hope it helps somebody|