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?
Brian
