-- !!! If you want to run regression tests, you need to create a database named "test" in MonetDB with the default port 50000.
SET timezone = 'UTC';
CREATE EXTENSION pg_monetdb;
CREATE SERVER foreign_server FOREIGN DATA WRAPPER pg_monetdb
OPTIONS (host '127.0.0.1', port '50000', dbname 'test');
CREATE USER MAPPING FOR CURRENT_USER SERVER foreign_server OPTIONS (user 'monetdb', password 'monetdb');
SELECT pg_monetdb_execute('foreign_server', $$DROP USER test_u$$);
ERROR:  [MonetDB RESULT ERROR] DROP USER: no such user role 'test_u'

SELECT pg_monetdb_execute('foreign_server', $$DROP SCHEMA test_u$$);
ERROR:  [MonetDB RESULT ERROR] DROP SCHEMA: name test_u does not exist

SELECT pg_monetdb_execute('foreign_server', $$CREATE USER "test_u" WITH PASSWORD 'test_u' NAME 'test_user' SCHEMA "sys"$$);
 pg_monetdb_execute 
--------------------
 
(1 row)

SELECT pg_monetdb_execute('foreign_server', $$CREATE SCHEMA IF NOT EXISTS "test_u" AUTHORIZATION "test_u"$$);
 pg_monetdb_execute 
--------------------
 
(1 row)

SELECT pg_monetdb_execute('foreign_server', $$ALTER USER "test_u" SET SCHEMA "test_u"$$);
 pg_monetdb_execute 
--------------------
 
(1 row)

DROP USER MAPPING FOR CURRENT_USER SERVER foreign_server;
CREATE USER MAPPING FOR CURRENT_USER SERVER foreign_server OPTIONS (user 'test_u', password 'test_u');
SELECT pg_monetdb_execute('foreign_server', $$CREATE TABLE emp( name VARCHAR(20), age INTEGER)$$);
 pg_monetdb_execute 
--------------------
 
(1 row)

CREATE FOREIGN TABLE emp(
        name VARCHAR(20),
        age INTEGER
)
SERVER foreign_server
OPTIONS (schema_name 'test_u', table_name 'emp');
SELECT s.srvname AS "Name",
  f.fdwname AS "Foreign-data wrapper"
FROM pg_catalog.pg_foreign_server s
     JOIN pg_catalog.pg_foreign_data_wrapper f ON f.oid=s.srvfdw
ORDER BY 1;
      Name      | Foreign-data wrapper 
----------------+----------------------
 foreign_server | pg_monetdb
(1 row)

-- Verify foreign table is correctly created
SELECT c.relname as "Table",
       s.srvname as "Server",
       array_to_string(ft.ftoptions, ', ') as "FDW options"
FROM pg_foreign_table ft
     JOIN pg_class c ON c.oid = ft.ftrelid
     JOIN pg_foreign_server s ON s.oid = ft.ftserver
WHERE c.relname = 'emp';
 Table |     Server     |            FDW options             
-------+----------------+------------------------------------
 emp   | foreign_server | schema_name=test_u, table_name=emp
(1 row)

-- test insert 
INSERT INTO emp VALUES('John', 23);
INSERT INTO emp VALUES('Mary', 22);
-- test select
SELECT * FROM emp;
 name | age 
------+-----
 John |  23
 Mary |  22
(2 rows)

SELECT monetdb_execute('foreign_server', $$SELECT * FROM emp$$);
INFO:  
name,age
[ "John",	23	]
[ "Mary",	22	]

 monetdb_execute 
-----------------
 
(1 row)

SELECT * FROM monet_query('foreign_server', $$SELECT * FROM emp$$);
        monet_query        
---------------------------
 [ "John",       23      ]
 [ "Mary",       22      ]
(2 rows)

SELECT * FROM monet_query_to_array('foreign_server', $$SELECT * FROM emp$$);
 monet_query_to_array 
----------------------
 {John,23}
 {Mary,22}
(2 rows)

SELECT *
FROM monet_query_to_jsonb(
     'foreign_server',
     $$SELECT * FROM emp$$,
     ARRAY['name', 'age']
);
     monet_query_to_jsonb      
-------------------------------
 {"age": "23", "name": "John"}
 {"age": "22", "name": "Mary"}
(2 rows)

-- test explain
-- EXPLAIN (COSTS OFF) SELECT * FROM emp;
EXPLAIN (COSTS OFF) INSERT INTO emp VALUES('Mary test', 21);
                              QUERY PLAN                              
----------------------------------------------------------------------
 Insert on emp
   MonetDB statement: INSERT INTO test_u.emp(name, age) VALUES (?, ?)
   ->  Result
(3 rows)

-- test truncate
TRUNCATE emp;
SELECT * FROM emp;
 name | age 
------+-----
(0 rows)

INSERT INTO emp VALUES('John', 23);
INSERT INTO emp VALUES('Mary', 22);
TRUNCATE TABLE emp;
SELECT * FROM emp;
 name | age 
------+-----
(0 rows)

SELECT monetdb_execute('foreign_server', $$CREATE TABLE delete_emp(name VARCHAR(20) PRIMARY KEY, age INTEGER)$$);
 monetdb_execute 
-----------------
 
(1 row)

CREATE FOREIGN TABLE delete_emp(
        name VARCHAR(20),
        age INTEGER
)
SERVER foreign_server
OPTIONS (schema_name 'test_u', table_name 'delete_emp');
INSERT INTO delete_emp VALUES('John', 23);
INSERT INTO delete_emp VALUES('Mary', 22);
SELECT * FROM delete_emp;
 name | age 
------+-----
 John |  23
 Mary |  22
(2 rows)

INSERT INTO delete_emp VALUES('Mary', 22); -- error
ERROR:  [MonetDB RESULT ERROR] INSERT INTO: PRIMARY KEY constraint 'delete_emp.delete_emp_name_pkey' violated

INSERT INTO delete_emp VALUES('Mary2', 22);
SELECT * FROM delete_emp;
 name  | age 
-------+-----
 John  |  23
 Mary  |  22
 Mary2 |  22
(3 rows)

-- test delete
DELETE FROM delete_emp WHERE name = 'John'; -- error, need set key
ERROR:  no primary key column specified for foreign MonetDB table
DETAIL:  For UPDATE or DELETE, at least one foreign table column must be marked as primary key column.
ALTER FOREIGN TABLE delete_emp ALTER name OPTIONS (ADD key 'true');
DELETE FROM delete_emp WHERE name = 'John';
SELECT * FROM delete_emp;
 name  | age 
-------+-----
 Mary  |  22
 Mary2 |  22
(2 rows)

DELETE FROM delete_emp WHERE age = 22;
SELECT * FROM delete_emp;
 name | age 
------+-----
(0 rows)

set client_min_messages = 'debug2';
INSERT INTO emp VALUES('John', 23);
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
DEBUG:  monetdb_fdw: begin remote transaction
DEBUG:  monetdb_fdw batch INSERT: INSERT INTO test_u.emp(name, age) VALUES (?, ?)
DEBUG:  monetdb_fdw batch bind[0]: John
DEBUG:  monetdb_fdw batch bind[1]: 23
DEBUG:  monetdb_fdw: commit remote transaction
INSERT INTO emp VALUES('Mary', 22);
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
DEBUG:  monetdb_fdw: begin remote transaction
DEBUG:  monetdb_fdw batch INSERT: INSERT INTO test_u.emp(name, age) VALUES (?, ?)
DEBUG:  monetdb_fdw batch bind[0]: Mary
DEBUG:  monetdb_fdw batch bind[1]: 22
DEBUG:  monetdb_fdw: commit remote transaction
SELECT * FROM emp;
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
DEBUG:  monetdb_fdw: begin remote transaction
DEBUG:  BeginForeignScan result col 1: name=name type=1043 (character varying)
DEBUG:  BeginForeignScan result col 2: name=age type=23 (integer)
DEBUG:  BeginForeignScan scan col 1: name=name type=1043 (character varying)
DEBUG:  BeginForeignScan scan col 2: name=age type=23 (integer)
DEBUG:  monetdb_fdw: commit remote transaction
 name | age 
------+-----
 John |  23
 Mary |  22
(2 rows)

INSERT INTO delete_emp VALUES('John', 23);
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
DEBUG:  monetdb_fdw: begin remote transaction
DEBUG:  monetdb_fdw batch INSERT: INSERT INTO test_u.delete_emp(name, age) VALUES (?, ?)
DEBUG:  monetdb_fdw batch bind[0]: John
DEBUG:  monetdb_fdw batch bind[1]: 23
DEBUG:  monetdb_fdw: commit remote transaction
INSERT INTO delete_emp VALUES('Mary', 22);
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
DEBUG:  monetdb_fdw: begin remote transaction
DEBUG:  monetdb_fdw batch INSERT: INSERT INTO test_u.delete_emp(name, age) VALUES (?, ?)
DEBUG:  monetdb_fdw batch bind[0]: Mary
DEBUG:  monetdb_fdw batch bind[1]: 22
DEBUG:  monetdb_fdw: commit remote transaction
SELECT * FROM delete_emp;
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
DEBUG:  monetdb_fdw: begin remote transaction
DEBUG:  BeginForeignScan result col 1: name=name type=1043 (character varying)
DEBUG:  BeginForeignScan result col 2: name=age type=23 (integer)
DEBUG:  BeginForeignScan scan col 1: name=name type=1043 (character varying)
DEBUG:  BeginForeignScan scan col 2: name=age type=23 (integer)
DEBUG:  monetdb_fdw: commit remote transaction
 name | age 
------+-----
 John |  23
 Mary |  22
(2 rows)

-- test update
UPDATE emp SET name = 'Mary2' WHERE name = 'Mary'; -- error
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
ERROR:  no primary key column specified for foreign MonetDB table
DETAIL:  For UPDATE or DELETE, at least one foreign table column must be marked as primary key column.
UPDATE delete_emp SET name = 'Mary2' WHERE name = 'Mary'; -- ok
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
DEBUG:  monetdb_fdw: begin remote transaction
DEBUG:  monetdb_fdw: commit remote transaction
SELECT * FROM delete_emp;
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
DEBUG:  monetdb_fdw: begin remote transaction
DEBUG:  BeginForeignScan result col 1: name=name type=1043 (character varying)
DEBUG:  BeginForeignScan result col 2: name=age type=23 (integer)
DEBUG:  BeginForeignScan scan col 1: name=name type=1043 (character varying)
DEBUG:  BeginForeignScan scan col 2: name=age type=23 (integer)
DEBUG:  monetdb_fdw: commit remote transaction
 name  | age 
-------+-----
 John  |  23
 Mary2 |  22
(2 rows)

UPDATE delete_emp SET name = 'John2' WHERE age = 23; 
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
DEBUG:  monetdb_fdw: begin remote transaction
DEBUG:  monetdb_fdw: commit remote transaction
SELECT * FROM delete_emp;
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
DEBUG:  monetdb_fdw: begin remote transaction
DEBUG:  BeginForeignScan result col 1: name=name type=1043 (character varying)
DEBUG:  BeginForeignScan result col 2: name=age type=23 (integer)
DEBUG:  BeginForeignScan scan col 1: name=name type=1043 (character varying)
DEBUG:  BeginForeignScan scan col 2: name=age type=23 (integer)
DEBUG:  monetdb_fdw: commit remote transaction
 name  | age 
-------+-----
 John2 |  23
 Mary2 |  22
(2 rows)

-- test returning
INSERT INTO delete_emp VALUES('John', 23) RETURNING *;
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
DEBUG:  monetdb_fdw: begin remote transaction
DEBUG:  monetdb_fdw batch INSERT: INSERT INTO test_u.delete_emp(name, age) VALUES (?, ?)
DEBUG:  monetdb_fdw batch bind[0]: John
DEBUG:  monetdb_fdw batch bind[1]: 23
DEBUG:  monetdb_fdw: commit remote transaction
 name | age 
------+-----
(0 rows)

DELETE FROM delete_emp WHERE name = 'John' RETURNING *;
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
DEBUG:  monetdb_fdw: begin remote transaction
DEBUG:  BeginForeignScan result col 1: name=name type=1043 (character varying)
DEBUG:  BeginForeignScan scan col 1: name=name type=1043 (character varying)
DEBUG:  BeginForeignScan scan col 2: name=age type=23 (integer)
DEBUG:  monetdb_fdw remote prepare query is: DELETE FROM test_u.delete_emp WHERE name = ? RETURNING name, age
DEBUG:  monetdb_fdw bind value[0]: John
DEBUG:  monetdb_fdw: commit remote transaction
 name | age 
------+-----
 John |  23
(1 row)

SELECT * FROM delete_emp;
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
DEBUG:  monetdb_fdw: begin remote transaction
DEBUG:  BeginForeignScan result col 1: name=name type=1043 (character varying)
DEBUG:  BeginForeignScan result col 2: name=age type=23 (integer)
DEBUG:  BeginForeignScan scan col 1: name=name type=1043 (character varying)
DEBUG:  BeginForeignScan scan col 2: name=age type=23 (integer)
DEBUG:  monetdb_fdw: commit remote transaction
 name  | age 
-------+-----
 John2 |  23
 Mary2 |  22
(2 rows)

-- UPDATE delete_emp SET name = 'Mary' WHERE name = 'Mary2' RETURNING *; -- error, MonetDB "UPDATE ... RETURNING" has bug
SELECT * FROM delete_emp;
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
DEBUG:  monetdb_fdw: begin remote transaction
DEBUG:  BeginForeignScan result col 1: name=name type=1043 (character varying)
DEBUG:  BeginForeignScan result col 2: name=age type=23 (integer)
DEBUG:  BeginForeignScan scan col 1: name=name type=1043 (character varying)
DEBUG:  BeginForeignScan scan col 2: name=age type=23 (integer)
DEBUG:  monetdb_fdw: commit remote transaction
 name  | age 
-------+-----
 John2 |  23
 Mary2 |  22
(2 rows)

DROP FOREIGN TABLE emp;
DEBUG:  drop auto-cascades to type emp
DEBUG:  drop auto-cascades to type emp[]
DROP FOREIGN TABLE delete_emp;
DEBUG:  drop auto-cascades to type delete_emp
DEBUG:  drop auto-cascades to type delete_emp[]
SELECT monetdb_execute('foreign_server', $$CREATE TABLE test_default(name VARCHAR(20) default 'zm', age INTEGER)$$);
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
DEBUG:  monetdb_fdw remote query is: CREATE TABLE test_default(name VARCHAR(20) default 'zm', age INTEGER)
DEBUG:  monetdb_fdw: begin remote transaction
DEBUG:  monetdb_fdw: commit remote transaction
 monetdb_execute 
-----------------
 
(1 row)

-- test IMPORT FOREIGN SCHEMA
IMPORT FOREIGN SCHEMA "test_u" from server foreign_server into public;
DEBUG:  monetdb_fdw: begin remote transaction
DEBUG:  monetdb_fdw remote query is: SELECT 1 FROM sys.schemas WHERE name = 'test_u'
DEBUG:  monetdb_fdw remote query is: 
SELECT t.name as table_name, 
  c.name as col_name, 
  sys.sql_datatype(c.type, c.type_digits, c.type_scale, false, false) as type, 
  CASE WHEN c."null" THEN 'false' ELSE 'true' END as expr, 
  sys.ifthenelse(c."default" IS NOT NULL, c."default", NULL) as default_expr, 
 (SELECT true FROM sys.objects kc, sys.keys k where kc.id = k.id and k.table_id = t.id and kc.name = c.name AND k.type = 0) AS pk, 
 c.number as attnum 
 FROM sys.tables t, sys.schemas s, sys.columns c  
 WHERE t.schema_id = s.id 
  AND t.type = 0 
  AND c.table_id = t.id 
  AND s.name = 'test_u' ORDER BY table_name, attnum
DEBUG:  postgres execute query is: 
CREATE FOREIGN TABLE delete_emp (
  name CHARACTER VARYING(20) OPTIONS (key 'true')  NOT NULL,
  age INTEGER
) SERVER foreign_server
OPTIONS (schema_name 'test_u', table_name 'delete_emp');
DEBUG:  postgres execute query is: 
CREATE FOREIGN TABLE emp (
  name CHARACTER VARYING(20),
  age INTEGER
) SERVER foreign_server
OPTIONS (schema_name 'test_u', table_name 'emp');
DEBUG:  postgres execute query is: 
CREATE FOREIGN TABLE test_default (
  name CHARACTER VARYING(20) DEFAULT 'zm',
  age INTEGER
) SERVER foreign_server
OPTIONS (schema_name 'test_u', table_name 'test_default');
DEBUG:  monetdb_fdw: commit remote transaction
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('f','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname !~ '^pg_toast'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
 Schema |     Name     |     Type      
--------+--------------+---------------
 public | delete_emp   | foreign table
 public | emp          | foreign table
 public | test_default | foreign table
(3 rows)

SELECT * FROM emp;
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
DEBUG:  monetdb_fdw: begin remote transaction
DEBUG:  BeginForeignScan result col 1: name=name type=1043 (character varying)
DEBUG:  BeginForeignScan result col 2: name=age type=23 (integer)
DEBUG:  BeginForeignScan scan col 1: name=name type=1043 (character varying)
DEBUG:  BeginForeignScan scan col 2: name=age type=23 (integer)
DEBUG:  monetdb_fdw: commit remote transaction
 name | age 
------+-----
 John |  23
 Mary |  22
(2 rows)

SELECT * FROM delete_emp;
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
DEBUG:  monetdb_fdw: begin remote transaction
DEBUG:  BeginForeignScan result col 1: name=name type=1043 (character varying)
DEBUG:  BeginForeignScan result col 2: name=age type=23 (integer)
DEBUG:  BeginForeignScan scan col 1: name=name type=1043 (character varying)
DEBUG:  BeginForeignScan scan col 2: name=age type=23 (integer)
DEBUG:  monetdb_fdw: commit remote transaction
 name  | age 
-------+-----
 John2 |  23
 Mary2 |  22
(2 rows)

EXPLAIN VERBOSE INSERT INTO test_default(age) values(22);
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
DEBUG:  monetdb_fdw: begin remote transaction
DEBUG:  monetdb_fdw: commit remote transaction
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Insert on public.test_default  (cost=0.00..0.01 rows=0 width=0)
   MonetDB statement: INSERT INTO test_u.test_default(name, age) VALUES (?, ?)
   ->  Result  (cost=0.00..0.01 rows=1 width=62)
         Output: 'zm'::character varying(20), 22
(4 rows)

INSERT INTO test_default(age) values(22);
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
DEBUG:  monetdb_fdw: begin remote transaction
DEBUG:  monetdb_fdw batch INSERT: INSERT INTO test_u.test_default(name, age) VALUES (?, ?)
DEBUG:  monetdb_fdw batch bind[0]: zm
DEBUG:  monetdb_fdw batch bind[1]: 22
DEBUG:  monetdb_fdw: commit remote transaction
SELECT * FROM test_default;
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
DEBUG:  monetdb_fdw: begin remote transaction
DEBUG:  BeginForeignScan result col 1: name=name type=1043 (character varying)
DEBUG:  BeginForeignScan result col 2: name=age type=23 (integer)
DEBUG:  BeginForeignScan scan col 1: name=name type=1043 (character varying)
DEBUG:  BeginForeignScan scan col 2: name=age type=23 (integer)
DEBUG:  monetdb_fdw: commit remote transaction
 name | age 
------+-----
 zm   |  22
(1 row)

DROP FOREIGN TABLE test_default;
DEBUG:  drop auto-cascades to type test_default
DEBUG:  drop auto-cascades to type test_default[]
DEBUG:  drop auto-cascades to default value for column name of foreign table test_default
-- test IMPORT FOREIGN SCHEMA ... LIMIT TO
IMPORT FOREIGN SCHEMA "test_u" limit to (test_default) from server foreign_server into public;
DEBUG:  monetdb_fdw: begin remote transaction
DEBUG:  monetdb_fdw remote query is: SELECT 1 FROM sys.schemas WHERE name = 'test_u'
DEBUG:  monetdb_fdw remote query is: 
SELECT t.name as table_name, 
  c.name as col_name, 
  sys.sql_datatype(c.type, c.type_digits, c.type_scale, false, false) as type, 
  CASE WHEN c."null" THEN 'false' ELSE 'true' END as expr, 
  sys.ifthenelse(c."default" IS NOT NULL, c."default", NULL) as default_expr, 
 (SELECT true FROM sys.objects kc, sys.keys k where kc.id = k.id and k.table_id = t.id and kc.name = c.name AND k.type = 0) AS pk, 
 c.number as attnum 
 FROM sys.tables t, sys.schemas s, sys.columns c  
 WHERE t.schema_id = s.id 
  AND t.type = 0 
  AND c.table_id = t.id 
  AND s.name = 'test_u' AND t.name IN ('test_default') ORDER BY table_name, attnum
DEBUG:  postgres execute query is: 
CREATE FOREIGN TABLE test_default (
  name CHARACTER VARYING(20) DEFAULT 'zm',
  age INTEGER
) SERVER foreign_server
OPTIONS (schema_name 'test_u', table_name 'test_default');
DEBUG:  monetdb_fdw: commit remote transaction
-- Verify foreign table is correctly created
SELECT c.relname as "Table",
       s.srvname as "Server",
       array_to_string(ft.ftoptions, ', ') as "FDW options"
FROM pg_foreign_table ft
     JOIN pg_class c ON c.oid = ft.ftrelid
     JOIN pg_foreign_server s ON s.oid = ft.ftserver
WHERE c.relname = 'test_default';
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
    Table     |     Server     |                 FDW options                 
--------------+----------------+---------------------------------------------
 test_default | foreign_server | schema_name=test_u, table_name=test_default
(1 row)

SELECT * FROM test_default;
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
DEBUG:  monetdb_fdw: begin remote transaction
DEBUG:  BeginForeignScan result col 1: name=name type=1043 (character varying)
DEBUG:  BeginForeignScan result col 2: name=age type=23 (integer)
DEBUG:  BeginForeignScan scan col 1: name=name type=1043 (character varying)
DEBUG:  BeginForeignScan scan col 2: name=age type=23 (integer)
DEBUG:  monetdb_fdw: commit remote transaction
 name | age 
------+-----
 zm   |  22
(1 row)

-- test the joint primary key 
SELECT monetdb_execute('foreign_server', $$CREATE TABLE orders (
    order_id NUMERIC,
    product_id NUMERIC,
    customer_email VARCHAR(100),
    order_date DATE,
    quantity NUMERIC,
    CONSTRAINT pk_orders PRIMARY KEY (order_id, product_id)
)$$);
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
DEBUG:  monetdb_fdw remote query is: CREATE TABLE orders (
    order_id NUMERIC,
    product_id NUMERIC,
    customer_email VARCHAR(100),
    order_date DATE,
    quantity NUMERIC,
    CONSTRAINT pk_orders PRIMARY KEY (order_id, product_id)
)
DEBUG:  monetdb_fdw: begin remote transaction
DEBUG:  monetdb_fdw: commit remote transaction
 monetdb_execute 
-----------------
 
(1 row)

IMPORT FOREIGN SCHEMA "test_u" limit to (orders) from server foreign_server into public;
DEBUG:  monetdb_fdw: begin remote transaction
DEBUG:  monetdb_fdw remote query is: SELECT 1 FROM sys.schemas WHERE name = 'test_u'
DEBUG:  monetdb_fdw remote query is: 
SELECT t.name as table_name, 
  c.name as col_name, 
  sys.sql_datatype(c.type, c.type_digits, c.type_scale, false, false) as type, 
  CASE WHEN c."null" THEN 'false' ELSE 'true' END as expr, 
  sys.ifthenelse(c."default" IS NOT NULL, c."default", NULL) as default_expr, 
 (SELECT true FROM sys.objects kc, sys.keys k where kc.id = k.id and k.table_id = t.id and kc.name = c.name AND k.type = 0) AS pk, 
 c.number as attnum 
 FROM sys.tables t, sys.schemas s, sys.columns c  
 WHERE t.schema_id = s.id 
  AND t.type = 0 
  AND c.table_id = t.id 
  AND s.name = 'test_u' AND t.name IN ('orders') ORDER BY table_name, attnum
DEBUG:  postgres execute query is: 
CREATE FOREIGN TABLE orders (
  order_id DECIMAL(18,3) OPTIONS (key 'true')  NOT NULL,
  product_id DECIMAL(18,3) OPTIONS (key 'true')  NOT NULL,
  customer_email CHARACTER VARYING(100),
  order_date DATE,
  quantity DECIMAL(18,3)
) SERVER foreign_server
OPTIONS (schema_name 'test_u', table_name 'orders');
DEBUG:  monetdb_fdw: commit remote transaction
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
  '' as "Owner",
  CASE c.relpersistence WHEN 'p' THEN 'permanent' WHEN 't' THEN 'temporary' WHEN 'u' THEN 'unlogged' END as "Persistence",
  pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
  pg_catalog.obj_description(c.oid, 'pg_class') as "Description"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('f','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname !~ '^pg_toast'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
 Schema |     Name     |     Type      | Owner | Persistence |  Size   | Description 
--------+--------------+---------------+-------+-------------+---------+-------------
 public | delete_emp   | foreign table |       | permanent   | 0 bytes | 
 public | emp          | foreign table |       | permanent   | 0 bytes | 
 public | orders       | foreign table |       | permanent   | 0 bytes | 
 public | test_default | foreign table |       | permanent   | 0 bytes | 
(4 rows)

-- Verify foreign table is correctly created with primary key options
SELECT c.relname as "Table",
       s.srvname as "Server",
       array_to_string(ft.ftoptions, ', ') as "FDW options"
FROM pg_foreign_table ft
     JOIN pg_class c ON c.oid = ft.ftrelid
     JOIN pg_foreign_server s ON s.oid = ft.ftserver
WHERE c.relname = 'orders';
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
 Table  |     Server     |              FDW options              
--------+----------------+---------------------------------------
 orders | foreign_server | schema_name=test_u, table_name=orders
(1 row)

INSERT INTO orders (order_id, product_id, customer_email, order_date, quantity)
VALUES (1, 101, 'john.doe@example.com', '2025-01-01', 5);
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
DEBUG:  monetdb_fdw: begin remote transaction
DEBUG:  monetdb_fdw batch INSERT: INSERT INTO test_u.orders(order_id, product_id, customer_email, order_date, quantity) VALUES (?, ?, ?, ?, ?)
DEBUG:  monetdb_fdw batch bind[0]: 1.000
DEBUG:  monetdb_fdw batch bind[1]: 101.000
DEBUG:  monetdb_fdw batch bind[2]: john.doe@example.com
DEBUG:  monetdb_fdw batch bind[3]: 2025-01-01
DEBUG:  monetdb_fdw batch bind[4]: 5.000
DEBUG:  monetdb_fdw: commit remote transaction
INSERT INTO orders (order_id, product_id, customer_email, order_date, quantity)
VALUES (2, 102, 'jane.smith@example.com', '2025-02-15', 3);
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
DEBUG:  monetdb_fdw: begin remote transaction
DEBUG:  monetdb_fdw batch INSERT: INSERT INTO test_u.orders(order_id, product_id, customer_email, order_date, quantity) VALUES (?, ?, ?, ?, ?)
DEBUG:  monetdb_fdw batch bind[0]: 2.000
DEBUG:  monetdb_fdw batch bind[1]: 102.000
DEBUG:  monetdb_fdw batch bind[2]: jane.smith@example.com
DEBUG:  monetdb_fdw batch bind[3]: 2025-02-15
DEBUG:  monetdb_fdw batch bind[4]: 3.000
DEBUG:  monetdb_fdw: commit remote transaction
UPDATE orders
SET quantity = 10
WHERE order_id = 1 AND product_id = 101;
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
DEBUG:  monetdb_fdw: begin remote transaction
DEBUG:  monetdb_fdw: commit remote transaction
DELETE FROM orders
WHERE order_id = 2 AND product_id = 102;
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
DEBUG:  monetdb_fdw: begin remote transaction
DEBUG:  monetdb_fdw: commit remote transaction
SELECT * FROM orders; 
DEBUG:  pg_monetdb_planner called: cteList_len=0
DEBUG:  pg_monetdb_try_inline_reused_ctes: parse=non-null cteList_len=0
DEBUG:  monetdb_fdw: begin remote transaction
DEBUG:  BeginForeignScan result col 1: name=order_id type=1700 (numeric)
DEBUG:  BeginForeignScan result col 2: name=product_id type=1700 (numeric)
DEBUG:  BeginForeignScan result col 3: name=customer_email type=1043 (character varying)
DEBUG:  BeginForeignScan result col 4: name=order_date type=1082 (date)
DEBUG:  BeginForeignScan result col 5: name=quantity type=1700 (numeric)
DEBUG:  BeginForeignScan scan col 1: name=order_id type=1700 (numeric)
DEBUG:  BeginForeignScan scan col 2: name=product_id type=1700 (numeric)
DEBUG:  BeginForeignScan scan col 3: name=customer_email type=1043 (character varying)
DEBUG:  BeginForeignScan scan col 4: name=order_date type=1082 (date)
DEBUG:  BeginForeignScan scan col 5: name=quantity type=1700 (numeric)
DEBUG:  monetdb_fdw: commit remote transaction
 order_id | product_id |    customer_email    | order_date | quantity 
----------+------------+----------------------+------------+----------
    1.000 |    101.000 | john.doe@example.com | 01-01-2025 |   10.000
(1 row)

set client_min_messages = 'INFO';
DROP FOREIGN TABLE emp;
DROP FOREIGN TABLE delete_emp;
DROP FOREIGN TABLE test_default;
DROP FOREIGN TABLE orders;
SELECT monetdb_execute('foreign_server', $$DROP TABLE orders$$);
 monetdb_execute 
-----------------
 
(1 row)

SELECT monetdb_execute('foreign_server', $$DROP TABLE test_default$$);
 monetdb_execute 
-----------------
 
(1 row)

SELECT monetdb_execute('foreign_server', $$DROP TABLE delete_emp$$);
 monetdb_execute 
-----------------
 
(1 row)

SELECT monetdb_execute('foreign_server', $$DROP TABLE emp$$);
 monetdb_execute 
-----------------
 
(1 row)