Re: SQL "pseudo-variables" for a view

On Thu, Jul 10, 2008 at 11:12 AM, Brian Hurt <bhurt(at)janestcapital(dot)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