Thread: Search path & functions in temporary schemas
Hi all,
I was trying to mock a function. So I followed the instructions in this thread.
I created a function with the same name as the existing one in different schema, and I updated the search path adding that schema at the beginning.
This solution worked with a real schema, but it did not with a temporary one.
Code working with a real schema:
SHOW SEARCH_PATH; -- public
CREATE OR REPLACE FUNCTION public.get_random_string()
RETURNS TEXT LANGUAGE SQL AS $$
SELECT 'real'::text;
$$;
SELECT get_random_string(); -- real
CREATE SCHEMA mock;
CREATE OR REPLACE FUNCTION mock.get_random_string()
RETURNS TEXT LANGUAGE SQL AS $$
SELECT 'mock'::text;
$$;
SELECT get_random_string(); -- real
SET SEARCH_PATH = mock, public;
SELECT get_random_string(); -- mock
Code not working with a temporary schema:
SHOW SEARCH_PATH; -- public
CREATE OR REPLACE FUNCTION public.get_random_string()
RETURNS TEXT LANGUAGE SQL AS $$
SELECT 'real'::text;
$$;
SELECT get_random_string(); -- real
SELECT nspname FROM pg_namespace WHERE oid = pg_my_temp_schema(); -- pg_temp_12
CREATE OR REPLACE FUNCTION pg_temp_12.get_random_string()
RETURNS TEXT LANGUAGE SQL AS $$
SELECT 'mock'::text;
$$;
SELECT get_random_string(); -- real
SET SEARCH_PATH = pg_temp_12, public;
SELECT get_random_string(); -- real
Is there any way to make this work?
Thanks,
Jose
jose luis pillado <josel.pillado@gmail.com> writes:
> This solution worked with a real schema, but it did not with a temporary
> one. ...
> Is there any way to make this work?
The temp schema is intentionally excluded from the search path for
functions and operators, because otherwise it's just too easy to
trojan-horse things. If you really want to create and call a
temp function, you have to schema-qualify its name when you call it.
To make that a bit less messy, you can use "pg_temp" as an alias
for your session's temp schema, rather than having to find out which
numbered temp schema you're really using.
regards, tom lane