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