--
-- Test foreign-data wrapper file_fdw.
--
-- directory paths are passed to us in environment variables
\getenv abs_srcdir PG_ABS_SRCDIR
-- Clean up in case a prior regression run failed
SET client_min_messages TO 'warning';
DROP ROLE IF EXISTS regress_file_fdw_superuser, regress_file_fdw_user, regress_no_priv_user;
RESET client_min_messages;
CREATE ROLE regress_file_fdw_superuser LOGIN SUPERUSER; -- is a superuser
CREATE ROLE regress_file_fdw_user LOGIN;                -- has priv and user mapping
CREATE ROLE regress_no_priv_user LOGIN;                 -- has priv but no user mapping
-- Install file_fdw
CREATE EXTENSION file_fdw;
-- create function to filter unstable results of EXPLAIN
CREATE FUNCTION explain_filter(text) RETURNS setof text
LANGUAGE plpgsql AS
$$
declare
    ln text;
begin
    for ln in execute $1
    loop
        -- Remove the path portion of foreign file names
        ln := regexp_replace(ln, 'Foreign File: .*/([a-z.]+)$', 'Foreign File: .../\1');
        return next ln;
    end loop;
end;
$$;
-- regress_file_fdw_superuser owns fdw-related objects
SET ROLE regress_file_fdw_superuser;
CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;
-- privilege tests
SET ROLE regress_file_fdw_user;
CREATE FOREIGN DATA WRAPPER file_fdw2 HANDLER file_fdw_handler VALIDATOR file_fdw_validator;   -- ERROR
ERROR:  permission denied to create foreign-data wrapper "file_fdw2"
HINT:  Must be superuser to create a foreign-data wrapper.
CREATE SERVER file_server2 FOREIGN DATA WRAPPER file_fdw;   -- ERROR
ERROR:  permission denied for foreign-data wrapper file_fdw
CREATE USER MAPPING FOR regress_file_fdw_user SERVER file_server;   -- ERROR
ERROR:  permission denied for foreign server file_server
SET ROLE regress_file_fdw_superuser;
GRANT USAGE ON FOREIGN SERVER file_server TO regress_file_fdw_user;
SET ROLE regress_file_fdw_user;
CREATE USER MAPPING FOR regress_file_fdw_user SERVER file_server;
-- create user mappings and grant privilege to test users
SET ROLE regress_file_fdw_superuser;
CREATE USER MAPPING FOR regress_file_fdw_superuser SERVER file_server;
CREATE USER MAPPING FOR regress_no_priv_user SERVER file_server;
-- validator tests
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (foo 'bar');  -- ERROR
ERROR:  invalid option "foo"
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS ("a=b" 'true');  -- ERROR
ERROR:  invalid option name "a=b": must not contain "="
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'xml');  -- ERROR
ERROR:  COPY format "xml" not recognized
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', quote ':');          -- ERROR
ERROR:  COPY QUOTE requires CSV mode
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', escape ':');         -- ERROR
ERROR:  COPY ESCAPE requires CSV mode
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'binary', header 'true');    -- ERROR
ERROR:  cannot specify HEADER in BINARY mode
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'binary', quote ':');        -- ERROR
ERROR:  COPY QUOTE requires CSV mode
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'binary', escape ':');       -- ERROR
ERROR:  COPY ESCAPE requires CSV mode
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', delimiter 'a');      -- ERROR
ERROR:  COPY delimiter cannot be "a"
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', escape '-');         -- ERROR
ERROR:  COPY ESCAPE requires CSV mode
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', quote '-', null '=-=');   -- ERROR
ERROR:  CSV quote character must not appear in the NULL specification
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', delimiter '-', null '=-=');    -- ERROR
ERROR:  COPY delimiter character must not appear in the NULL specification
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', delimiter '-', quote '-');    -- ERROR
ERROR:  COPY delimiter and quote must be different
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', delimiter '---');     -- ERROR
ERROR:  COPY delimiter must be a single one-byte character
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', quote '---');         -- ERROR
ERROR:  COPY quote must be a single one-byte character
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', escape '---');        -- ERROR
ERROR:  COPY escape must be a single one-byte character
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', delimiter '\');       -- ERROR
ERROR:  COPY delimiter cannot be "\"
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', delimiter '.');       -- ERROR
ERROR:  COPY delimiter cannot be "."
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', delimiter '1');       -- ERROR
ERROR:  COPY delimiter cannot be "1"
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', delimiter 'a');       -- ERROR
ERROR:  COPY delimiter cannot be "a"
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', delimiter '
');       -- ERROR
ERROR:  COPY delimiter cannot be newline or carriage return
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', null '
');       -- ERROR
ERROR:  COPY null representation cannot use newline or carriage return
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (on_error 'unsupported');       -- ERROR
ERROR:  COPY ON_ERROR "unsupported" not recognized
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'binary', on_error 'ignore');       -- ERROR
ERROR:  only ON_ERROR STOP is allowed in BINARY mode
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (log_verbosity 'unsupported');       -- ERROR
ERROR:  COPY LOG_VERBOSITY "unsupported" not recognized
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (reject_limit '1');       -- ERROR
ERROR:  COPY REJECT_LIMIT requires ON_ERROR to be set to IGNORE
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (on_error 'ignore', reject_limit '0');       -- ERROR
ERROR:  REJECT_LIMIT (0) must be greater than zero
CREATE FOREIGN TABLE tbl () SERVER file_server;  -- ERROR
ERROR:  either filename or program is required for file_fdw foreign tables
\set filename :abs_srcdir '/data/agg.data'
CREATE FOREIGN TABLE agg_text (
	a	int2 CHECK (a >= 0),
	b	float4
) SERVER file_server
OPTIONS (format 'text', filename :'filename', delimiter '	', null '\N');
GRANT SELECT ON agg_text TO regress_file_fdw_user;
\set filename :abs_srcdir '/data/agg.csv'
CREATE FOREIGN TABLE agg_csv (
	a	int2,
	b	float4
) SERVER file_server
OPTIONS (format 'csv', filename :'filename', header 'true', delimiter ';', quote '@', escape '"', null '');
ALTER FOREIGN TABLE agg_csv ADD CHECK (a >= 0);
\set filename :abs_srcdir '/data/agg.bad'
CREATE FOREIGN TABLE agg_bad (
	a	int2,
	b	float4
) SERVER file_server
OPTIONS (format 'csv', filename :'filename', header 'true', delimiter ';', quote '@', escape '"', null '');
ALTER FOREIGN TABLE agg_bad ADD CHECK (a >= 0);
-- test header matching
\set filename :abs_srcdir '/data/list1.csv'
CREATE FOREIGN TABLE header_match ("1" int, foo text) SERVER file_server
OPTIONS (format 'csv', filename :'filename', delimiter ',', header 'match');
SELECT * FROM header_match;
 1 | foo 
---+-----
 1 | bar
(1 row)

CREATE FOREIGN TABLE header_doesnt_match (a int, foo text) SERVER file_server
OPTIONS (format 'csv', filename :'filename', delimiter ',', header 'match');
SELECT * FROM header_doesnt_match; -- ERROR
ERROR:  column name mismatch in header line field 1: got "1", expected "a"
CONTEXT:  COPY header_doesnt_match, line 1: "1,foo"
-- per-column options tests
\set filename :abs_srcdir '/data/text.csv'
CREATE FOREIGN TABLE text_csv (
    word1 text OPTIONS (force_not_null 'true'),
    word2 text OPTIONS (force_not_null 'off'),
    word3 text OPTIONS (force_null 'true'),
    word4 text OPTIONS (force_null 'off')
) SERVER file_server
OPTIONS (format 'text', filename :'filename', null 'NULL');
SELECT * FROM text_csv; -- ERROR
ERROR:  COPY FORCE_NOT_NULL requires CSV mode
ALTER FOREIGN TABLE text_csv OPTIONS (SET format 'csv');
\pset null _null_
SELECT * FROM text_csv;
 word1 | word2  | word3  | word4  
-------+--------+--------+--------
 AAA   | aaa    | 123    | 
 XYZ   | xyz    |        | 321
 NULL  | _null_ | _null_ | _null_
 NULL  | _null_ | _null_ | _null_
 ABC   | abc    |        | 
(5 rows)

-- force_not_null and force_null can be used together on the same column
ALTER FOREIGN TABLE text_csv ALTER COLUMN word1 OPTIONS (force_null 'true');
ALTER FOREIGN TABLE text_csv ALTER COLUMN word3 OPTIONS (force_not_null 'true');
-- force_not_null is not allowed to be specified at any foreign object level:
ALTER FOREIGN DATA WRAPPER file_fdw OPTIONS (ADD force_not_null '*'); -- ERROR
ERROR:  invalid option "force_not_null"
HINT:  There are no valid options in this context.
ALTER SERVER file_server OPTIONS (ADD force_not_null '*'); -- ERROR
ERROR:  invalid option "force_not_null"
HINT:  There are no valid options in this context.
CREATE USER MAPPING FOR public SERVER file_server OPTIONS (force_not_null '*'); -- ERROR
ERROR:  invalid option "force_not_null"
HINT:  There are no valid options in this context.
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (force_not_null '*'); -- ERROR
ERROR:  invalid option "force_not_null"
-- force_null is not allowed to be specified at any foreign object level:
ALTER FOREIGN DATA WRAPPER file_fdw OPTIONS (ADD force_null '*'); -- ERROR
ERROR:  invalid option "force_null"
HINT:  There are no valid options in this context.
ALTER SERVER file_server OPTIONS (ADD force_null '*'); -- ERROR
ERROR:  invalid option "force_null"
HINT:  There are no valid options in this context.
CREATE USER MAPPING FOR public SERVER file_server OPTIONS (force_null '*'); -- ERROR
ERROR:  invalid option "force_null"
HINT:  There are no valid options in this context.
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (force_null '*'); -- ERROR
ERROR:  invalid option "force_null"
-- basic query tests
SELECT * FROM agg_text WHERE b > 10.0 ORDER BY a;
  a  |   b    
-----+--------
  42 | 324.78
 100 | 99.097
(2 rows)

SELECT * FROM agg_csv ORDER BY a;
  a  |    b    
-----+---------
   0 | 0.09561
  42 |  324.78
 100 |  99.097
(3 rows)

SELECT * FROM agg_csv c JOIN agg_text t ON (t.a = c.a) ORDER BY c.a;
  a  |    b    |  a  |    b    
-----+---------+-----+---------
   0 | 0.09561 |   0 | 0.09561
  42 |  324.78 |  42 |  324.78
 100 |  99.097 | 100 |  99.097
(3 rows)

-- error context report tests
SELECT * FROM agg_bad;               -- ERROR
ERROR:  invalid input syntax for type real: "aaa"
CONTEXT:  COPY agg_bad, line 3, column b: "aaa"
-- on_error, log_verbosity and reject_limit tests
ALTER FOREIGN TABLE agg_bad OPTIONS (ADD on_error 'ignore');
SELECT * FROM agg_bad;
NOTICE:  2 rows were skipped due to data type incompatibility
  a  |   b    
-----+--------
 100 | 99.097
  42 | 324.78
(2 rows)

ALTER FOREIGN TABLE agg_bad OPTIONS (ADD log_verbosity 'silent');
SELECT * FROM agg_bad;
  a  |   b    
-----+--------
 100 | 99.097
  42 | 324.78
(2 rows)

ALTER FOREIGN TABLE agg_bad OPTIONS (ADD reject_limit '1'); -- ERROR
SELECT * FROM agg_bad;
ERROR:  skipped more than REJECT_LIMIT (1) rows due to data type incompatibility
CONTEXT:  COPY agg_bad, line 5, column b: "bbb"
ALTER FOREIGN TABLE agg_bad OPTIONS (SET reject_limit '2');
SELECT * FROM agg_bad;
  a  |   b    
-----+--------
 100 | 99.097
  42 | 324.78
(2 rows)

ANALYZE agg_bad;
-- misc query tests
\t on
SELECT explain_filter('EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv');
 Foreign Scan on public.agg_csv
   Output: a, b
   Foreign File: .../agg.csv

\t off
PREPARE st(int) AS SELECT * FROM agg_csv WHERE a = $1;
EXECUTE st(100);
  a  |   b    
-----+--------
 100 | 99.097
(1 row)

EXECUTE st(100);
  a  |   b    
-----+--------
 100 | 99.097
(1 row)

DEALLOCATE st;
-- tableoid
SELECT tableoid::regclass, b FROM agg_csv;
 tableoid |    b    
----------+---------
 agg_csv  |  99.097
 agg_csv  | 0.09561
 agg_csv  |  324.78
(3 rows)

-- updates aren't supported
INSERT INTO agg_csv VALUES(1,2.0);
ERROR:  cannot insert into foreign table "agg_csv"
UPDATE agg_csv SET a = 1;
ERROR:  cannot update foreign table "agg_csv"
DELETE FROM agg_csv WHERE a = 100;
ERROR:  cannot delete from foreign table "agg_csv"
TRUNCATE agg_csv;
ERROR:  cannot truncate foreign table "agg_csv"
-- but this should be allowed
SELECT * FROM agg_csv FOR UPDATE;
  a  |    b    
-----+---------
 100 |  99.097
   0 | 0.09561
  42 |  324.78
(3 rows)

-- copy from isn't supported either
COPY agg_csv FROM STDIN;
ERROR:  cannot insert into foreign table "agg_csv"
-- constraint exclusion tests
\t on
SELECT explain_filter('EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0');
 Foreign Scan on public.agg_csv
   Output: a, b
   Filter: (agg_csv.a < 0)
   Foreign File: .../agg.csv

\t off
SELECT * FROM agg_csv WHERE a < 0;
 a | b 
---+---
(0 rows)

SET constraint_exclusion = 'on';
\t on
SELECT explain_filter('EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0');
 Result
   Output: a, b
   One-Time Filter: false

\t off
SELECT * FROM agg_csv WHERE a < 0;
 a | b 
---+---
(0 rows)

RESET constraint_exclusion;
-- table inheritance tests
CREATE TABLE agg (a int2, b float4);
ALTER FOREIGN TABLE agg_csv INHERIT agg;
SELECT tableoid::regclass, * FROM agg;
 tableoid |  a  |    b    
----------+-----+---------
 agg_csv  | 100 |  99.097
 agg_csv  |   0 | 0.09561
 agg_csv  |  42 |  324.78
(3 rows)

SELECT tableoid::regclass, * FROM agg_csv;
 tableoid |  a  |    b    
----------+-----+---------
 agg_csv  | 100 |  99.097
 agg_csv  |   0 | 0.09561
 agg_csv  |  42 |  324.78
(3 rows)

SELECT tableoid::regclass, * FROM ONLY agg;
 tableoid | a | b 
----------+---+---
(0 rows)

-- updates aren't supported
UPDATE agg SET a = 1;
ERROR:  cannot update foreign table "agg_csv"
DELETE FROM agg WHERE a = 100;
ERROR:  cannot delete from foreign table "agg_csv"
-- but this should be allowed
SELECT tableoid::regclass, * FROM agg FOR UPDATE;
 tableoid |  a  |    b    
----------+-----+---------
 agg_csv  | 100 |  99.097
 agg_csv  |   0 | 0.09561
 agg_csv  |  42 |  324.78
(3 rows)

ALTER FOREIGN TABLE agg_csv NO INHERIT agg;
DROP TABLE agg;
-- declarative partitioning tests
SET ROLE regress_file_fdw_superuser;
CREATE TABLE pt (a int, b text) partition by list (a);
\set filename :abs_srcdir '/data/list1.csv'
CREATE FOREIGN TABLE p1 partition of pt for values in (1) SERVER file_server
OPTIONS (format 'csv', filename :'filename', delimiter ',');
CREATE TABLE p2 partition of pt for values in (2);
SELECT tableoid::regclass, * FROM pt;
 tableoid | a |  b  
----------+---+-----
 p1       | 1 | foo
 p1       | 1 | bar
(2 rows)

SELECT tableoid::regclass, * FROM p1;
 tableoid | a |  b  
----------+---+-----
 p1       | 1 | foo
 p1       | 1 | bar
(2 rows)

SELECT tableoid::regclass, * FROM p2;
 tableoid | a | b 
----------+---+---
(0 rows)

\set filename :abs_srcdir '/data/list2.bad'
COPY pt FROM :'filename' with (format 'csv', delimiter ','); -- ERROR
ERROR:  cannot insert into foreign table "p1"
CONTEXT:  COPY pt, line 2: "1,qux"
\set filename :abs_srcdir '/data/list2.csv'
COPY pt FROM :'filename' with (format 'csv', delimiter ',');
SELECT tableoid::regclass, * FROM pt;
 tableoid | a |  b  
----------+---+-----
 p1       | 1 | foo
 p1       | 1 | bar
 p2       | 2 | baz
 p2       | 2 | qux
(4 rows)

SELECT tableoid::regclass, * FROM p1;
 tableoid | a |  b  
----------+---+-----
 p1       | 1 | foo
 p1       | 1 | bar
(2 rows)

SELECT tableoid::regclass, * FROM p2;
 tableoid | a |  b  
----------+---+-----
 p2       | 2 | baz
 p2       | 2 | qux
(2 rows)

INSERT INTO pt VALUES (1, 'xyzzy'); -- ERROR
ERROR:  cannot insert into foreign table "p1"
INSERT INTO pt VALUES (2, 'xyzzy');
UPDATE pt set a = 1 where a = 2; -- ERROR
ERROR:  cannot insert into foreign table "p1"
SELECT tableoid::regclass, * FROM pt;
 tableoid | a |   b   
----------+---+-------
 p1       | 1 | foo
 p1       | 1 | bar
 p2       | 2 | baz
 p2       | 2 | qux
 p2       | 2 | xyzzy
(5 rows)

SELECT tableoid::regclass, * FROM p1;
 tableoid | a |  b  
----------+---+-----
 p1       | 1 | foo
 p1       | 1 | bar
(2 rows)

SELECT tableoid::regclass, * FROM p2;
 tableoid | a |   b   
----------+---+-------
 p2       | 2 | baz
 p2       | 2 | qux
 p2       | 2 | xyzzy
(3 rows)

DROP TABLE pt;
-- generated column tests
\set filename :abs_srcdir '/data/list1.csv'
CREATE FOREIGN TABLE gft1 (a int, b text, c text GENERATED ALWAYS AS ('foo') STORED) SERVER file_server
OPTIONS (format 'csv', filename :'filename', delimiter ',');
SELECT a, c FROM gft1;
 a |   c    
---+--------
 1 | _null_
 1 | _null_
(2 rows)

DROP FOREIGN TABLE gft1;
-- copy default tests
\set filename :abs_srcdir '/data/copy_default.csv'
CREATE FOREIGN TABLE copy_default (
	id integer,
	text_value text not null default 'test',
	ts_value timestamp without time zone not null default '2022-07-05'
) SERVER file_server
OPTIONS (format 'csv', filename :'filename', default '\D');
SELECT id, text_value, ts_value FROM copy_default;
 id | text_value |         ts_value         
----+------------+--------------------------
  1 | value      | Mon Jul 04 00:00:00 2022
  2 | test       | Sun Jul 03 00:00:00 2022
  3 | test       | Tue Jul 05 00:00:00 2022
(3 rows)

DROP FOREIGN TABLE copy_default;
-- privilege tests
SET ROLE regress_file_fdw_superuser;
SELECT * FROM agg_text ORDER BY a;
  a  |    b    
-----+---------
   0 | 0.09561
  42 |  324.78
  56 |     7.8
 100 |  99.097
(4 rows)

SET ROLE regress_file_fdw_user;
SELECT * FROM agg_text ORDER BY a;
  a  |    b    
-----+---------
   0 | 0.09561
  42 |  324.78
  56 |     7.8
 100 |  99.097
(4 rows)

SET ROLE regress_no_priv_user;
SELECT * FROM agg_text ORDER BY a;   -- ERROR
ERROR:  permission denied for foreign table agg_text
SET ROLE regress_file_fdw_user;
\t on
SELECT explain_filter('EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_text WHERE a > 0');
 Foreign Scan on public.agg_text
   Output: a, b
   Filter: (agg_text.a > 0)
   Foreign File: .../agg.data

\t off
-- file FDW allows foreign tables to be accessed without user mapping
DROP USER MAPPING FOR regress_file_fdw_user SERVER file_server;
SELECT * FROM agg_text ORDER BY a;
  a  |    b    
-----+---------
   0 | 0.09561
  42 |  324.78
  56 |     7.8
 100 |  99.097
(4 rows)

-- privilege tests for object
SET ROLE regress_file_fdw_superuser;
ALTER FOREIGN TABLE agg_text OWNER TO regress_file_fdw_user;
ALTER FOREIGN TABLE agg_text OPTIONS (SET format 'text');
SET ROLE regress_file_fdw_user;
ALTER FOREIGN TABLE agg_text OPTIONS (SET format 'text');
ERROR:  permission denied to set the "filename" option of a file_fdw foreign table
DETAIL:  Only roles with privileges of the "pg_read_server_files" role may set this option.
SET ROLE regress_file_fdw_superuser;
-- cleanup
RESET ROLE;
DROP EXTENSION file_fdw CASCADE;
NOTICE:  drop cascades to 9 other objects
DETAIL:  drop cascades to server file_server
drop cascades to user mapping for regress_file_fdw_superuser on server file_server
drop cascades to user mapping for regress_no_priv_user on server file_server
drop cascades to foreign table agg_text
drop cascades to foreign table agg_csv
drop cascades to foreign table agg_bad
drop cascades to foreign table header_match
drop cascades to foreign table header_doesnt_match
drop cascades to foreign table text_csv
DROP ROLE regress_file_fdw_superuser, regress_file_fdw_user, regress_no_priv_user;