Skip site navigation (1) Skip section navigation (2)

SQL "pseudo-variables" for a view

From: Brian Hurt <bhurt(at)janestcapital(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: SQL "pseudo-variables" for a view
Date: 2008-07-10 15:12:21
Message-ID: 487626D5.90300@janestcapital.com (view raw or flat)
Thread:
Lists: pgsql-novice
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

Responses

pgsql-novice by date

Next:From: Sean DavisDate: 2008-07-10 15:32:07
Subject: Re: SQL "pseudo-variables" for a view
Previous:From: LeoDate: 2008-07-10 13:54:27
Subject: For Perl users - hope it helps somebody

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group