do functions cache views?
От | Thomas F. O'Connell |
---|---|
Тема | do functions cache views? |
Дата | |
Msg-id | 3AED9224.2000208@monsterlabs.com обсуждение исходный текст |
Ответы |
Re: do functions cache views?
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: