How can I make a postgres user-defined function available across all databases?

Question

I have a large set of user-defined procedures which need to be used on several separate catalogs (databases).

The built-in procedures normally seen in pg_catalog.pg_proc appear to be reflected in the pg_catalog schemas of all the databases. I was hoping that creating a function in the postgres database might make it accessible universally, but no, whether created in the postgres.pg_catalog or postgres.public schema.

Does anyone know how I can make user-defined procedures available universally?

I've tried faking namespace and owner, granting execute to public, and holding a black cat over my left shoulder (but not during a full moon).

Will setting it up as an extension work? (That may take some negotiating and finagling for my environment is the only reason I haven't tried it yet.)

Thanks in advance

you can put the function in the template db and any new databases would have it. otherwise an extension is your best bet — Neil McGuigan
– Neil McGuigan, Commented Aug 10, 2017 at 19:02
Having it in the template would get it half way there, but leaves the maintenance headache of updating so many copies of the same thing. I'll try the extension. Thanks, Neil. — user8446599
– user8446599, Commented Aug 10, 2017 at 20:21
Successfully installed my first postgres extension, but it still has the same problem. I created it in the postgres schema, and can call it successfully there, but calling from sandbox schema evokes the 'ERROR: function...does not exist.' complaint. I can't help but think I'm missing something else that needs to be done. In postgres.pg_catalog.pg_available_extensions, plpgsql is listed with 1.0 as the value of 'installed_version'--which makes me think the plpgsql itself is loaded this way. (And obviously it's available everywhere.) Any suggestions? — user8446599
– user8446599, Commented Aug 10, 2017 at 21:37
plpgsql is available by default because the extension is installed in template1; there's no cross-database magic going on. You'll need to roll this out to each database individually. Fortunately, as you can see from @Neil's answer, it's pretty straightforward. — Nick Barnes
– Nick Barnes, Commented Aug 10, 2017 at 23:41
Thanks Nick. I see how extensions can be propagated at db create time by having them in template1. (For future db's this seems like a good approach.) I still wonder if perhaps I'm not asking the right question. — user8446599
– user8446599, Commented Aug 14, 2017 at 16:41

Neil McGuigan · Accepted Answer · 2017-08-10 21:41:42Z

2

PostgreSQL databases are quite segregated. You'll need to install your extension in each one. Here's a shell script to ease your pain:

for DB in $(psql -t -c "SELECT datname FROM pg_database WHERE datname NOT IN ('postgres', 'template0', 'template1')"); do
  psql -d $DB -c "CREATE EXTENSION IF NOT EXISTS foo"
done

Or is this one database, with multiple schemas that you're talking about?

answered Aug 10, 2017 at 21:41

Neil McGuigan

48.5k12 gold badges130 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user8446599 Over a year ago

Thanks Neil, there are several databases, each with multiple schemas, so the shell script would work in theory, though fitting it into our security model might take a minute.;)

beldaz Over a year ago

Good approach. You could replace the nested -c "CREATE EXTENSION... with -f user_func.ddl to create a function defined in said file to all databases.

Collectives™ on Stack Overflow

How can I make a postgres user-defined function available across all databases?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related