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
