CREATE EXTENSION IF NOT EXISTS postgres_fdw; DROP SERVER IF EXISTS db2 CASCADE; CREATE SERVER kea FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'db2', dbname 'db2'); CREATE USER MAPPING FOR USER SERVER db2 OPTIONS (user 'db2', password 'password');
CREATE SCHEMA IF NOT EXISTS db2;
IMPORT FOREIGN SCHEMA public FROM SERVER db2 INTO db2;
SELECT ok( (SELECT COUNT(host_id) FROM db2.hosts) = 1, 'foobar' );
-- ; ROLLBACK;
Now, I would like to extract db2 initialization in separate file "/test/init.sql" with this content:
CREATE EXTENSION IF NOT EXISTS postgres_fdw; DROP SERVER IF EXISTS db2 CASCADE; CREATE SERVER kea FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'db2', dbname 'db2'); CREATE USER MAPPING FOR USER SERVER db2 OPTIONS (user 'db2', password 'password');
CREATE SCHEMA IF NOT EXISTS db2;
IMPORT FOREIGN SCHEMA public FROM SERVER db2 INTO db2;
Now, my test file is:
BEGIN; SELECT plan(1);
\i /test/init.sql
SELECT ok( (SELECT COUNT(host_id) FROM db2.hosts) = 1, 'foobar' );
In log I see that "init.sql" file is loaded with success:
Running tests: /test/*.sql -v /test/init.sql ................... No subtests run
But I have this error:
ERROR: user mapping not found for "db2"
Question: where is my mistake? How can I include some sql file in my test?
It is early morning here and I may be missing something, but I do not see where you actually create server db2. I do see you creating server kea.
Sorry, it is not kea but db2 in my example. I did a mistake when I replace the db name in my example.
You need to read:
CREATE EXTENSION IF NOT EXISTS postgres_fdw; DROP SERVER IF EXISTS db2 CASCADE; CREATE SERVER db2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'db2', dbname 'db2'); CREATE USER MAPPING FOR USER SERVER db2 OPTIONS (user 'db2', password 'password');
CREATE SCHEMA IF NOT EXISTS db2;
IMPORT FOREIGN SCHEMA public FROM SERVER db2 INTO db2;