From: | Andreas Ulbrich <andreas(dot)ulbrich(at)matheversum(dot)de> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Check constraint on foreign table using SQL function |
Date: | 2014-12-25 11:31:43 |
Message-ID: | 549BF59F.7000305@matheversum.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)testdb ERROR: relation "tab_a" does not
exist at character 24
2014-12-24 13:11:27 CET andreas(at)testdb QUERY: SELECT $2 = name FROM
tab_a WHERE id = $1
2014-12-24 13:11:27 CET andreas(at)testdb CONTEXT: SQL function
"test_name_b" during inlining
2014-12-24 13:11:27 CET andreas(at)testdb STATEMENT: DECLARE c1 CURSOR FOR
SELECT id, id_a, name FROM andreas.tab_b
2014-12-24 13:11:27 CET postgres(at)postgres ERROR: relation "tab_a" does
not exist
2014-12-24 13:11:27 CET postgres(at)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(at)postgres STATEMENT: TABLE ftab_b;
*/
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2014-12-25 17:16:14 | Re: Check constraint on foreign table using SQL function |
Previous Message | Adrian Klaver | 2014-12-24 17:36:36 | Re: logging of Logical Decoding |