Check constraint on foreign table using SQL function
От | Andreas Ulbrich |
---|---|
Тема | Check constraint on foreign table using SQL function |
Дата | |
Msg-id | 549BF59F.7000305@matheversum.de обсуждение исходный текст |
Ответы |
Re: Check constraint on foreign table using SQL function
(Adrian Klaver <adrian.klaver@aklaver.com>)
Re: Check constraint on foreign table using SQL function (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-general |
Salvete! I've made the following observation: I have a foreign table (postgres_fdw) to a remote table B. On the remote table is a check constraint using a SQL-function with access to an other table. In the remote DB both tables and the check-function resist in a seperate schema "andreas". This schema is in the search_path via ALTER DATABASE testDB SET search_path TO "$user", test, public, ext; If I set the search_path in the function definition (see comment), it works, if I use 'FROM andreas.tab_a' too. If I use the plPgSQL function it works and the raise prints psql:s_andreas.sql:39: WARNING: test_name_b called: "$user", test, public, ext Questions: Wy is the check constraint function in a select called? The search_path seams not to be set for the SQL function, is this behavior correct? Im using the 9.4.0 version, I havnt't checked in other versions Thanks and Merry Christmas Regards Andreas Here is a complete example: \connect testdb andreas BEGIN; CREATE SCHEMA andreas; CREATE TABLE IF NOT EXISTS tab_a ( id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE ); INSERT INTO tab_A SELECT i, md5(i::TEXT) FROM Generate_Series(1, 10) AS i; CREATE OR REPLACE FUNCTION test_name_b(INTEGER,TEXT) RETURNS BOOLEAN AS $$ SELECT $2 = name FROM tab_a WHERE id = $1 $$ LANGUAGE SQL -- SET search_path TO "$user", test, public, ext ; /* CREATE OR REPLACE FUNCTION test_name_b(INTEGER,TEXT) RETURNS BOOLEAN AS $$ DECLARE res BOOLEAN; path TEXT; BEGIN SHOW search_path INTO path; RAISE WARNING 'test_name_b called: %', path; SELECT $2 = name INTO res FROM tab_a WHERE id = $1; RETURN res; END $$ LANGUAGE plPgSQL; */ CREATE TABLE IF NOT EXISTS tab_b ( id INTEGER PRIMARY KEY, id_a INTEGER NOT NULL REFERENCES tab_a, name TEXT, CHECK(test_name_b(id_a, name)) ); INSERT INTO tab_B SELECT i, i, md5(i::TEXT) FROM Generate_Series(1, 10) AS i; END; -- For housekeeping: -- DROP SCHEMA IF EXISTS andreas CASCADE; \connect postgres postgres BEGIN; CREATE EXTENSION Postgres_FDW; CREATE SERVER testSRV FOREIGN DATA WRAPPER Postgres_FDW OPTIONS (host 'localhost', dbname 'testdb'); RESET ROLE; CREATE USER MAPPING FOR postgres SERVER testSRV OPTIONS (user 'andreas', password 'a6'); CREATE FOREIGN TABLE IF NOT EXISTS ftab_b ( id INTEGER NOT NULL, id_a INTEGER NOT NULL, name TEXT ) SERVER testSRV OPTIONS (table_name 'tab_b', schema_name 'andreas'); \det+ TABLE ftab_b; ROLLBACK; \connect testdb andreas DROP SCHEMA IF EXISTS andreas CASCADE; /* psql:s_andreas.sql:63: ERROR: relation "tab_a" does not exist CONTEXT: Remote SQL command: SELECT id, id_a, name FROM andreas.tab_b SQL function "test_name_b" during inlining Server log: 2014-12-24 13:11:27 CET andreas@testdb ERROR: relation "tab_a" does not exist at character 24 2014-12-24 13:11:27 CET andreas@testdb QUERY: SELECT $2 = name FROM tab_a WHERE id = $1 2014-12-24 13:11:27 CET andreas@testdb CONTEXT: SQL function "test_name_b" during inlining 2014-12-24 13:11:27 CET andreas@testdb STATEMENT: DECLARE c1 CURSOR FOR SELECT id, id_a, name FROM andreas.tab_b 2014-12-24 13:11:27 CET postgres@postgres ERROR: relation "tab_a" does not exist 2014-12-24 13:11:27 CET postgres@postgres CONTEXT: Remote SQL command: SELECT id, id_a, name FROM andreas.tab_b SQL function "test_name_b" during inlining 2014-12-24 13:11:27 CET postgres@postgres STATEMENT: TABLE ftab_b; */
В списке pgsql-general по дате отправления:
Следующее
От: Adrian KlaverДата:
Сообщение: Re: Check constraint on foreign table using SQL function