Thanks for all the help, Joeri,
We might end up using your suggestion (y)
Luis Ángel Larios Cárdenas | Web Developer luis.larios@inpixon.com o: +1 (818) 654.6787 | m: +1 (323) 702.2746 Encino Office: 6345 Balboa Blvd. | Suite # 247 | Encino | CA 91316 US HQ: 2479 E. Bayshore Rd | Suite 195 | Palo Alto | CA 94303 US
-----Original Message----- From: users-list [mailto:users-list-bounces+luis.larios=sysorex.com@monetdb.org] On Behalf Of Joeri van Ruth Sent: Friday, March 17, 2017 9:07 AM To: Communication channel for MonetDB users users-list@monetdb.org Cc: Ron Edmeades Ron.Edmeades@sysorex.com; Austin Guthals Austin.Guthals@sysorex.com Subject: Re: How to execute a query string in MonetDB
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 _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list The information transmitted, including any attachments, is intended only for the individual or entity to which it is addressed, and may contain confidential and/or privileged information. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by individuals or entities other than the intended recipient is prohibited, and all liability arising therefrom is disclaimed. If you have received this communication in error, please delete the information from any computer and notify the sender.