How can I include sql file in pgTAP unittest?

[Stéphane Klein]

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/poc-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?

Best regards,
Stéphane
-- 
Stéphane Klein 
blog: http://stephane-klein.info
cv : http://cv.stephane-klein.info
Twitter: http://twitter.com/klein_stephane

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

[Stéphane Klein]

2018-03-02 14:52 GMT+01:00 Adrian Klaver :

> 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;

Do you know a json_populate_record alternative method to create a ROW with a named field notation?

[Stéphane Klein]

Hello,

In this example:

```
CREATE TYPE contact AS (
   firstname VARCHAR,
   lastname VARCHAR
);

postgres=# SELECT json_populate_record(NULL::contact,
postgres(#   '{
postgres'#  "firstname": "John",
postgres'#  "lastname": "Doe"
postgres'#}'
postgres'# );
 json_populate_record
--
 (John,Doe)
(1 row)
```

**Question:** do you know a method like json_populate_record (
https://www.postgresql.org/docs/13/functions-json.html), which allows
creating a `ROW` with named field notation without using a json format?

I know the `ROW` syntax expression:

```
postgres=# SELECT ROW('John', 'Doe')::contact;
row

 (John,Doe)
(1 row)
```

But I didn't find a `ROW` constructors (
https://www.postgresql.org/docs/13/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS)
syntax allowing a named field notation.

Best regards,
Stéphane
-- 
Stéphane Klein 
Homepage: http://stephane-klein.info