Postgres Pro
Downloads
Home   >   mailing lists

Search path & functions in temporary schemas - Mailing list pgsql-general

From jose luis pillado
Subject Search path & functions in temporary schemas
Date
Msg-id CAHmzsaFuuNH4CRadFtHmSuMV+MdV=4a0-PMN3FA9EdRj09gGGg@mail.gmail.com
Whole thread Raw
Responses Re: Search path & functions in temporary schemas  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
Tree view
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

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Newly Created Source DB Table Not Reflecting into DestinationForeign Tables
Next
From: Francisco Olarte
Date:
Subject: Fwd: Code for getting particular day of week number from month