are there circumstances under which a pl/pgsql function will cache the
contents of a view?
i can do this sequence of actions just fine:
create table foo (
id int2 primary key
);
create view foo_view as
select *
from foo;
create function get_new_foo() returns int2 as '
declare
v_max_foo int2;
begin
select into v_max_foo max( id )
from foo;
return v_max_foo;
end;
then, if i run get_new_foo() while modifying the values in foo, the
function seems to get the correct values.
unfortunately, i have a function/view pair that looks roughly like this:
create view significant_records_view as
select *
from significant_records
where
/*
* certain status conditions hold.
* i don't think i need to include this logic since the joins here are
* only to other standard tables. no other functions or views.
*/
;
create function get_next_significant_date( int4 ) returns date as '
declare
v_id alias for $1;
v_significant_date date;
begin
select into v_significant_date
max( other_date )
from more_significant_records msr, significant_records_view srv
where msr.significant_records_id = srv.id
and srv.significant_id = v_id;
if not found then
select into v_significant_date min( significant_date )
from significant_records_view srv
where srv.significant_id = v_id;
end if;
return v_significant_date;
end;
' language 'plpgsql';
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.
when i notice this happening, i attempt to run the two select statements
of the function. the first one, as i expect, returns null. the second
one, as i expect, returns a date. but i'm suspecting that the function
may be caching the null for the second function and ends up returning that.
if this is the case, why does the foo example at top work correctly? if
this is not the case, why does my function not work?
i would be happy to provide further details if this is not sufficient
for a reasonable response.
thanks.
-tfo
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
