Postgres Pro
Downloads
Home   >   mailing lists

Thread: How do write schema independent install files for functions.

How do write schema independent install files for functions.

From
Philip Couling
Date:
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

Re: How do write schema independent install files for functions.

From
Tom Lane
Date:
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

Re: How do write schema independent install files for functions.

From
Bartosz Dmytrak
Date:


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. 

Regards,
Bartek

Re: How do write schema independent install files for functions.

From
Philip Couling
Date:
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