Can postgresql stored procedures operate on a separate database?

Question

Can postgresql (specifically 9.6) stored procedures read/modify a separate database, or can they only operate on data within the local database where they are defined?

Answer 1

Postgres 9.6 does not have stored procedures, which were introduced with Postgres 11.

You probably mean Postgres functions, which have been there since time immemorial. It's a widespread misconception to call those "stored procedures".

Both are confined to the database in which they are executed - as Postgres will tell you if you try to prefix a database name to an object name:

ERROR:  cross-database references are not implemented:

Unless you use the Foreign Data Wrapper infrastructure (you probably want the additional module postgres_fdw with that) or dblink, which allow exactly that after all ...

• How to use (install) dblink in PostgreSQL?
• Persistent inserts in a UDF even if the function aborts
• How do I do large non-blocking updates in PostgreSQL?