Thread: 回复： [GENERAL] How to make the user-defined function or type beingaccessed in the global database server?

On 20/02/2011 2:13 PM, 页公倒龙 wrote:
> Thanks for your replying. But actually there are some difference
> database now, and I really want to define the user-defined function or
> type one time so that they can be accessed as system-defined function or
> type, how can I do?

It sounds like you want something like Oracle's packages. If so, you're
out of luck: postgresql doesn't support anything like that at this point.

Bundle the functions, type definitions, etc up in a .sql file and run
that on each database. It's cleanest if you do this by creating a new
schema called, say, 'shared', then SET search_path=shared before
creating the functions and types. That way they all go in one place
that's separated from the per-database content. To access them, SET
search_path=shared,public . You can "ALTER USER SET
search_path=shared,public" or "ALTER DATABASE SET
search_path=shared,public" to make it default.

See the manual for more information about schema, and the commands/options:

   SET
   search_path
   CREATE SCHEMA
   CREATE FUNCTION

--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/

On 02/19/11 10:13 PM, 页公倒龙 wrote:
> Thanks for your replying. But actually there are some difference
> database now, and I really want to define the user-defined function or
> type one time so that they can be accessed as system-defined function
> or type, how can I do? Thanks!
>

an alternative might be to define your functions in the template
database used to create your other databases, then it will be copied
into each of them at CREATE DATABASE time.

this of course is of no use if these databases already exist.