Обсуждение: SQL "pseudo-variables" for a view

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

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