Function Execution and search_path

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
gdexter
Posts: 4
Joined: Thu 03 Jun 2010 19:48

Function Execution and search_path

Post by gdexter » Mon 11 Jun 2018 17:16

Hello,
We are having an issue with executing functions in a multi schema postgres database using the Entity Framework.

Problem Domain
  • function_exec is Imported from Shechma_A
  • The function calls another function function_test in Schema_B during the execution
  • An error is generated indicating the function_test could not be found.
I think this occurs because the function is executing within the context of Schema_A and cannot see the function is Schema_B.

I have tried adding all of the schemas in the Initial Schema property of the connection string but it has not made any difference. I would think there would be a way to do this.

NOTE: I am able to run the function_exec from within PGAdmin using the same credentials being used for the EF connection, so there are no permission problems.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Function Execution and search_path

Post by Shalex » Wed 13 Jun 2018 12:39

We cannot reproduce the problem.

1. Please download https://www.devart.com/pub/37283_forum.zip . It includes the SQL script and test project created basing on your description.

2. Create test schemas in your database:

Code: Select all

create schema "Schema_A";
create schema "Schema_B";

CREATE OR REPLACE FUNCTION "Schema_B".function_test(name TEXT)
RETURNS text AS $$
BEGIN
       RETURN CAST('new ' AS text) ||  name;
END;
$$  LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION "Schema_A".function_exec(name text)
  RETURNS text AS $$
BEGIN
       RETURN "Schema_B".function_test(name);
END;
$$  LANGUAGE plpgsql;

select "Schema_A".function_exec('abcd');
3. Open the project in Visual Studio, right click *.edml > Open With > Entity Developer. Then navigate to Tools > Entity Developer > Database Explorer and Edit Connection Properties to adjust them to your environment. Save the *.edml. Run the code. Does it work?

Post Reply