From: | Philip Couling <phil(at)pedal(dot)me(dot)uk> |
---|---|
To: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | How do write schema independent install files for functions. |
Date: | 2012-07-16 19:19:39 |
Message-ID: | 5004694B.7010602@pedal.me.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi
I have a number of functions which I wish to wrap up in a SQL script
(well technically DDL script). The functions reference one another and
for safety it is necessary for them to ether set the search_path or
directly reference the schema for one another.
I was wondering if there is good / recommended way to write the install
script to easily install to an arbitrary schema.
The problem I have is that some functions need to set the search_path
for code security. As an example below, the function "bar()" needs to
set search_path or it would accidently reference the wrong "foo()".
However in order to install the same functions to a different schema I
would have to re-write the script with numerous changes to the various
"set search_path = my_schema" lines.
SET search_path = my_schema;
CREATE OR REPLACE FUNCTION foo()
RETURNS INTEGER AS
$BODY$
BEGIN
RETURN 42;
END;
$BODY$
LANGUAGE plpgsql IMMUTABLE
COST 100;
CREATE OR REPLACE FUNCTION bar()
RETURNS INTEGER AS
$BODY$
BEGIN
RETURN foo();
END;
$BODY$
LANGUAGE plpgsql IMMUTABLE
SET search_path = my_schema
COST 100;
Is there any more flexible way to do this?
Thanks
From | Date | Subject | |
---|---|---|---|
Next Message | Daniele Varrazzo | 2012-07-16 19:35:08 | Re: Can't figure out how to use now() in default for tsrange column (PG 9.2) |
Previous Message | Michael Nolan | 2012-07-16 18:53:01 | Re: Replication/cloning: rsync vs modification dates? |