Re: [HACKERS] Creating temp tables inside read only transactions

On Jul11, 2011, at 21:49 , David Johnston wrote:
> Right now I can emulate a hierarchical schema structure via a naming scheme
> - for example "schemabase_sub1_sub2_etc". I am simply looking for a formal
> way to do the above AND also tell the system that I want all schemas under
> "schemabase" to be in the search path. Heck, I guess just allowing for
> simply pattern matching in "search_path" would be useful in this case
> regardless of the presence of an actual schema hierarchy. Using "LIKE"
> syntax say: "SET search_path TO schemabase_sub1_%" or something similar.

create function set_searchpath_expand(v_pattern text) returns void as $$
declare
v_searchpath text;
begin
select string_agg(quote_ident(nspname), ',') into v_searchpath
from pg_catalog.pg_namespace where nspname like v_pattern;
execute 'set search_path = ' || v_searchpath;
end
$$ language plpgsql;

best regards,
Florian Pflug