We run several instances of postgre in different countries, and we try keeping them as same as possible, in terms of structure of the tables and function definitions (except the content of schema "config", which differs between dbs). So if we need to implement some different algorithm per country, then we define a plpgsql function like this into each of our dbs:
BEGIN
select value from config.strings into country where name = 'country';
if country = 'CZ' then
-- Some computations here
elseif country = 'PL' then
-- Different calculations here
elseif country = 'RO' then
-- Yet another algorithm here
end if;
return (result);
END;
In this function, we get the value from a table config.strings (which contains a different value in each country's database), and based on this value we go through a specific if-branch. Simple. But reading this configuration value may involve reading from a disk.
So to avoid accessing the disk to fetch the country value, I would like to replace it by calling a function defined like this (in each db returning a different string indicating the country where db resides, of course):
create or replace function config.country () returns char(3) as $$ select 'CZ'::char(3) $$ language sql immutable;
And then call it like:
if config.country () = 'CZ' then
-- Some computations here
Now my questions is: Are the stored functions (both plpgsql and plain sql functions) kept always in a memory? Or they are stored similarly like tables, on the disk, reading them into memory when called and possibly release them from memory, if memory is needed for something else?
Thanks for reply.
R.G.
Disclaimer: http://www.aps-holding.com/disclaimer.html
