Thanks Tom, that seemed to get me farther. I issued this on the remote db:
ALTER FUNCTION utils.concat_artists(character varying) SET search_path=public,utils;
And now my test errs on a different function, which probably needs the same:
piction@piction_transit> select * from piction.bampfa_test_fv ;
ERROR: relation "collectionobjects_common" does not exist
CONTEXT: Remote SQL command: SELECT objectcsid, idnumber, collections FROM piction.bampfa_test_v
PL/pgSQL function utils.get_first_blobcsid(character varying) line 7 at SQL statement
In case this breaks stuff, what is the default search_path for functions if they are created without one set explicitly?
"$user"?
Adam
On Fri, Aug 14, 2015 at 2:38 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Adam FUCHS <atman@xxxxxxxxxxxx> writes:
> Thanks Korry, how would I check the search_path that is being used by the
> FDW user?
postgres_fdw always does "SET search_path = pg_catalog" when opening the
connection. Probably this needs to be documented, since it's user-visible
if you try to attach a foreign table to a remote view.
Anyway the short answer is that you should fully schema-qualify references
in functions used by such a view, or else attach "SET search_path" options
to the functions.
regards, tom lane
Adam Fuchs
Database Administrator
UC Berkeley - Information Services & Technology
2195 Hearst Ave., Berkeley, CA 94120
510-664-4354
