How do write schema independent install files for functions.
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
Philip Couling <phil@pedal.me.uk> writes:
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.
CREATE FUNCTION's "SET search_path FROM CURRENT" option might help you.
regards, tom lane
2012/7/16 Philip Couling <phil@pedal.me.uk>
Is there any more flexible way to do this?
Hi,
in my opinion you should use fully qualified names instead of set
search_path
Your script should look like this:
CREATE OR REPLACE FUNCTION my_schema.foo()
RETURNS INTEGER AS
$BODY$
BEGIN
RETURN 42;
END;
$BODY$
LANGUAGE plpgsql IMMUTABLE
COST 100;
CREATE OR REPLACE FUNCTION another_schema.bar()
RETURNS INTEGER AS
$BODY$
BEGIN
RETURN my_schema.foo();
END;
$BODY$
LANGUAGE plpgsql IMMUTABLE
COST 100;
then script is readable and uses full qualified names.
http://www.postgresql.org/docs/9.1/static/ddl-schemas.html#sql-createschema.html
Regards,
Bartek
On 16/07/2012 20:44, Tom Lane wrote:
Philip Couling <phil@pedal.me.uk> writes:
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.CREATE FUNCTION's "SET search_path FROM CURRENT" option might help you.
regards, tom lane
Thanks tom. That was exactly what I was looking for and what I'd missed
in the manual.
For the sake of future readers "FROM CURRENT" is documented here:
http://www.postgresql.org/docs/current/static/sql-createfunction.html
Regards