0

I have a following situation.

In database A on server I, let's call it Host DB, there is a table, that has a following sample create script:

CREATE TABLE public.some_table (
    id           SERIAL PRIMARY KEY,
    some_field   TEXT
);

CREATE INDEX public.some_field_index
ON public.some_table USING btree
(my_custom_function(some_field));

As you can see, the index is created on a result of some custom, stored in database A, function my_custom_function.

Now I want to declare some_table as foreign table on other server, in database B. After creating the server, user mappings etc. I declare foreign table as:

CREATE FOREIGN TABLE public.some_table (
    id           SERIAL PRIMARY KEY,
    some_field   TEXT
)
SERVER host_server
OPTIONS (
    schema_name 'public',
    table_name  'some_table'
);

The table is created nicely, however I cannot query it. Instead I am getting following error:

ERROR: function my_custom_function(text) does not exist.
No function matches the given name and argument type.
You might need to add explcit type casts.

CONTEXT: Remote SQL command: SELECT id, some_field FROM public.some_table
SQL fuction my_custom_function during inlining.

I believe the problem is related to function my_custom_function not being declared on the server B, in the "guest" database. For some reasons i don't want to create this function. Is there any solution to overcome this problem?

Thanks for all your answers in advance.

Paweł Sopel
  • 528
  • 4
  • 17
  • What if you add explicit schema to the function `CREATE INDEX some_field_index ON public.some_table USING btree (public.my_custom_function(some_field));` – Julia Leder Aug 10 '17 at 16:12
  • You should be able to do the above without declaring the function in Database B. Can you confirm that you can actually perform a select in the Database A? – Julia Leder Aug 10 '17 at 16:22
  • @JuliaLeder, yes, I can confim that. It also works fine after removing the index. – Paweł Sopel Aug 11 '17 at 11:15
  • have you tried adding schema to the function name? – Julia Leder Aug 11 '17 at 13:37
  • Unfortunately I can not do that as I am not allowed to modify any indices in this particular DB. – Paweł Sopel Aug 11 '17 at 16:03
  • There is a good chance this is a search_path issue. As the post here discusses: https://www.postgresql.org/message-id/549C94D2.7060500%40aklaver.com fdw calls have limited search_path and may require explicit schema declaration – Julia Leder Aug 11 '17 at 16:14

0 Answers0