On Thu, Mar 16, 2017 at 04:38:22PM +0000, Luis Larios wrote:
It seems that any non-explicit-schema tables that appear in the body of a function are understood, by default, as belonging to the same function schema -- and therefore *stats_table* is not found in the *functions-schema*. And that's true, it's not there, and I'd like it to not be there.
It appears the limitation here is that if I have a function that uses data tables, both the function and the data tables must belong to the same schema. Please confirm if this is true.
If you know of another work around this issue, please let me know.
Hi Luis! I did a quick experiment which confirms your observation. The following MonetDB script returns 'schema b', indicating that the schema is taken to be the function's schema. -- preparation DROP SCHEMA IF EXISTS a CASCADE; DROP SCHEMA IF EXISTS b CASCADE; DROP SCHEMA IF EXISTS c CASCADE; CREATE SCHEMA a; CREATE SCHEMA b; CREATE SCHEMA c; CREATE TABLE a.t (schema_name VARCHAR(10)); INSERT INTO a.t VALUES ('schema a'); CREATE TABLE b.t (schema_name VARCHAR(10)); INSERT INTO b.t VALUES ('schema b'); CREATE TABLE c.t (schema_name VARCHAR(10)); INSERT INTO c.t VALUES ('schema c'); -- create the function in b while default schema is a SET SCHEMA a; CREATE FUNCTION b.foo() RETURNS VARCHAR(10) RETURN (SELECT * FROM t); -- execute it while default schema is c SET SCHEMA c; SELECT b.foo(); The following similar Postgres script returns 'schema c': -- preparation DROP SCHEMA IF EXISTS a CASCADE; DROP SCHEMA IF EXISTS b CASCADE; DROP SCHEMA IF EXISTS c CASCADE; CREATE SCHEMA a; CREATE SCHEMA b; CREATE SCHEMA c; CREATE TABLE a.t (schema_name VARCHAR(10)); INSERT INTO a.t VALUES ('schema a'); CREATE TABLE b.t (schema_name VARCHAR(10)); INSERT INTO b.t VALUES ('schema b'); CREATE TABLE c.t (schema_name VARCHAR(10)); INSERT INTO c.t VALUES ('schema c'); -- create the function in b while default schema is a SET SCHEMA 'a'; CREATE FUNCTION b.foo() RETURNS VARCHAR(10) AS 'SELECT * FROM t' LANGUAGE SQL; -- execute it while default schema is c SET SCHEMA 'c'; SELECT b.foo(); I think in general MonetDB's behaviour is preferrable, especially from a security perspective because it avoids any number unpredictable behaviours related to injecting unexpected objects into functions defined by some other role in another schema. It does however unfortunately block your clever approach to function sharing. I'm not aware of an alternative approach, maybe someone else can chime in. A very crude solution would be to forego the shared schema altogether and simply define all functions over and over in every 'data schema'. This might be a little painful but workable if the shared functions do not change very frequently. Joeri