Thread: Creating 'global' functions.

Is there a way, when a function is created (in language SQL for example),
for it to be available globally, as the pg_catalog functions are. All of
the ones I create are only available on the database they were created,
and I can't figure out a way to make it so they're available to all
without having to run the functions in every database. When you run a
select on pg_proc they won't even appear, but all of the 'catalog' ones
will.

These are generic cast functions that needed to be written when we
migrated from 7.2 to 7.3, so they don't reference any specific database
tables or anything like that, but all of the databases NEED these
functions to operate the way the queries were written. I tried to change
the proowner and pronamespace in pg_proc and these didn't seem to do
anything.

I worked with this a few days with no luck at all, and just ended up
running the creation scripts in all of our databases, but it would be nice
to know if it's possible.

Thanks!

On Mon, Jan 26, 2004 at 04:40:33PM -0500, Matthew Jones wrote:
> Is there a way, when a function is created (in language SQL for example),
> for it to be available globally, as the pg_catalog functions are. All of
> the ones I create are only available on the database they were created,
> and I can't figure out a way to make it so they're available to all
> without having to run the functions in every database. When you run a
> select on pg_proc they won't even appear, but all of the 'catalog' ones
> will.

If you create a function in template1 it will be automatically transferred
to new database created, but not to existing ones.

So just after installation, add them to template1, then create the other
databases.
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> (... have gone from d-i being barely usable even by its developers
> anywhere, to being about 20% done. Sweet. And the last 80% usually takes
> 20% of the time, too, right?) -- Anthony Towns, debian-devel-announce

If you know about these functions before
you create your databases, install them into
template1.  Then all subsequently created databases
will have them.

If the databases already exist, you need to do what you did.

--elein
============================================================
elein@varlena.com        Varlena, LLC        www.varlena.com

          PostgreSQL Consulting, Support & Training

PostgreSQL General Bits   http://www.varlena.com/GeneralBits/
=============================================================
Its a doggy dog world out there.


On Mon, Jan 26, 2004 at 04:40:33PM -0500, Matthew Jones wrote:
> Is there a way, when a function is created (in language SQL for example),
> for it to be available globally, as the pg_catalog functions are. All of
> the ones I create are only available on the database they were created,
> and I can't figure out a way to make it so they're available to all
> without having to run the functions in every database. When you run a
> select on pg_proc they won't even appear, but all of the 'catalog' ones
> will.
>
> These are generic cast functions that needed to be written when we
> migrated from 7.2 to 7.3, so they don't reference any specific database
> tables or anything like that, but all of the databases NEED these
> functions to operate the way the queries were written. I tried to change
> the proowner and pronamespace in pg_proc and these didn't seem to do
> anything.
>
> I worked with this a few days with no luck at all, and just ended up
> running the creation scripts in all of our databases, but it would be nice
> to know if it's possible.
>
> Thanks!
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend