Use of search path in plpgsql functions, PG 8.3.12

Started by Troy Rasiahover 15 years ago2 messagesgeneral
Jump to latest
#1Troy Rasiah
troyr@vicnet.net.au

Hello,

At present i have the following

Schema 1
-------------

Table: events

Public Schema
-------------

Table: events

Function

CREATE OR REPLACE FUNCTION "public"."recurring_events_for" (
"range_start" timestamp,
"range_end" timestamp,
"time_zone" varchar,
"events_limit" integer
)
RETURNS SETOF "events" AS

....

If i set the search path to schema1,public the function still returns rows from the events table in the public schema. I would like to use the same function for both schema's. I'm sure i'm missing something obvious, can anyone help me understand this better ?

Thanks

--
Troy Rasiah

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Troy Rasiah (#1)
Re: Use of search path in plpgsql functions, PG 8.3.12

Troy Rasiah <troyr@vicnet.net.au> writes:

If i set the search path to schema1,public the function still returns
rows from the events table in the public schema.

What's probably happening is that plpgsql caches the execution plan for
the SELECT during the first execution of the function in any given
session. The only really reliable way around that is to use EXECUTE
instead of an in-line statement.

regards, tom lane