I'm using postgres 12.2, with multiple identical schema per database
(each with a matching role). I can write public plpgsql functions
without using a schema identifier and let the runtime search_path find
the correct schema-dependent table. The same does not appear to be true
for plain sql functions. The 'parser'? does not recognize the tables
(sans schema qualifier):
ERROR: relation "<tablename>" does not exist.
I would rather not have to duplicate these across multiple schema - I'll
use plpgsql instead unless I've overlooked some other avenue.
I've found that I can create the function, in public, if I set the
search_path containing one schema (of course) and then successfully
access the function after resetting the search path to use a second
schema. My "build the world" scripting has so far avoided needing to
know/use any specific role. Another pipe dream vaporized?
Thanks,
rjs
