Re: How can I include sql file in pgTAP unittest?

2018-03-02 14:52 GMT+01:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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;