2018-03-02 14:52 GMT+01:00 Adrian Klaver <adrian.kla...@aklaver.com>:
> On 03/02/2018 01:17 AM, Stéphane Klein wrote: > >> Hi, >> >> context: I would like to write UnitTest to test pgSQL triggers which use >> postgres_fdw extension. >> I use pgTAP <http://pgtap.org/> to write this UnitTest (I use this >> Docker environment poc-postgresql-pgTAP <https://github.com/harobed/po >> c-postgresql-pgTAP>). >> >> >> All works perfectly with this test file: >> >> BEGIN; >> SELECT plan(1); >> >> 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;
