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: logging of Logical Decoding
Следующее
От: Adrian Klaver
Дата:
Сообщение: Re: Check constraint on foreign table using SQL function