From: | "Thomas F(dot) O'Connell" <tfo(at)monsterlabs(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: do functions cache views? |
Date: | 2001-05-01 22:59:34 |
Message-ID: | 3AEF3FD6.2030206@monsterlabs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>> now, this function works for existing records in the table
>> significant_records. but as i add records, i end up having to drop and
>> recreate the function in order for it to record the existence of the new
>> records.
>
> This is hard to believe.
indeed, but it still seems to be happening.
> I am not sure that "if not found" means anything after a "select max()"
> query. The select will always return exactly one row --- even if it's
> just a NULL --- so I'd expect the "if not found" never to succeed.
> Perhaps you want to be testing whether v_significant_date is NULL or
> not, instead.
ok. i made this an explicit check for NULL just in case.
> Another issue, since you omitted the details of the view and of what
> version you are running, is whether the view involves GROUP BY and/or
> aggregates. An aggregate over a grouped view won't work properly in
> versions before 7.1.
this is on a system running 7.1b4. and there are no GROUP BYs in the
view. the only aggregate is in a subquery.
> If it's not those issues then we'll need more details --- preferably
> a self-contained example.
ok. here are some more details:
the only other thing that might cause a problem with the view that i can
see is that there is a part of the where clause that references
CURRENT_DATE.
as in:
create view significant_records_view as
select *
from significant_records sr
where sr.significant_date <= CURRENT_DATE
-- rest of where clause
;
now, this function is used in a date-sensitive context. could the
function in my original example, which references this view, be caching
the view as of the last CURRENT_DATE on which the function was brought
into being?
-tfo
From | Date | Subject | |
---|---|---|---|
Next Message | pnews | 2001-05-01 23:00:20 | underlying structure: varchar vs. text |
Previous Message | Peter Pilsl | 2001-05-01 22:35:14 | readline-support |