diff options
Diffstat (limited to 'contrib/file_fdw/output/file_fdw.source')
| -rw-r--r-- | contrib/file_fdw/output/file_fdw.source | 55 |
1 files changed, 40 insertions, 15 deletions
diff --git a/contrib/file_fdw/output/file_fdw.source b/contrib/file_fdw/output/file_fdw.source index 52b4d5f1df7..891146fef38 100644 --- a/contrib/file_fdw/output/file_fdw.source +++ b/contrib/file_fdw/output/file_fdw.source @@ -1,6 +1,8 @@ -- -- 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; @@ -10,6 +12,21 @@ CREATE ROLE regress_file_fdw_user LOGIN; -- has priv and user map 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; @@ -77,32 +94,36 @@ CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', null ' ERROR: COPY null representation cannot use newline or carriage return 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 '@abs_srcdir@/data/agg.data', delimiter ' ', null '\N'); +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 '@abs_srcdir@/data/agg.csv', header 'true', delimiter ';', quote '@', escape '"', null ''); +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 '@abs_srcdir@/data/agg.bad', header 'true', delimiter ';', quote '@', escape '"', null ''); +OPTIONS (format 'csv', filename :'filename', header 'true', delimiter ';', quote '@', escape '"', null ''); ALTER FOREIGN TABLE agg_bad ADD CHECK (a >= 0); -- 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 '@abs_srcdir@/data/text.csv', null 'NULL'); +OPTIONS (format 'text', filename :'filename', null 'NULL'); SELECT * FROM text_csv; -- ERROR ERROR: COPY force not null available only in CSV mode ALTER FOREIGN TABLE text_csv OPTIONS (SET format 'csv'); @@ -176,10 +197,10 @@ ERROR: invalid input syntax for type real: "aaa" CONTEXT: COPY agg_bad, line 3, column b: "aaa" -- misc query tests \t on -EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv; +SELECT explain_filter('EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv'); Foreign Scan on public.agg_csv Output: a, b - Foreign File: @abs_srcdir@/data/agg.csv + Foreign File: .../agg.csv \t off PREPARE st(int) AS SELECT * FROM agg_csv WHERE a = $1; @@ -226,11 +247,11 @@ COPY agg_csv FROM STDIN; ERROR: cannot insert into foreign table "agg_csv" -- constraint exclusion tests \t on -EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0; +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: @abs_srcdir@/data/agg.csv + Foreign File: .../agg.csv \t off SELECT * FROM agg_csv WHERE a < 0; @@ -240,7 +261,7 @@ SELECT * FROM agg_csv WHERE a < 0; SET constraint_exclusion = 'on'; \t on -EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0; +SELECT explain_filter('EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0'); Result Output: a, b One-Time Filter: false @@ -295,8 +316,9 @@ 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 '@abs_srcdir@/data/list1.csv', delimiter ','); +OPTIONS (format 'csv', filename :'filename', delimiter ','); CREATE TABLE p2 partition of pt for values in (2); SELECT tableoid::regclass, * FROM pt; tableoid | a | b @@ -317,10 +339,12 @@ SELECT tableoid::regclass, * FROM p2; ----------+---+--- (0 rows) -COPY pt FROM '@abs_srcdir@/data/list2.bad' with (format 'csv', delimiter ','); -- ERROR +\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" -COPY pt FROM '@abs_srcdir@/data/list2.csv' with (format 'csv', delimiter ','); +\set filename :abs_srcdir '/data/list2.csv' +COPY pt FROM :'filename' with (format 'csv', delimiter ','); SELECT tableoid::regclass, * FROM pt; tableoid | a | b ----------+---+----- @@ -376,8 +400,9 @@ SELECT tableoid::regclass, * FROM p2; 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 '@abs_srcdir@/data/list1.csv', delimiter ','); +OPTIONS (format 'csv', filename :'filename', delimiter ','); SELECT a, c FROM gft1; a | c ---+-------- @@ -412,11 +437,11 @@ SELECT * FROM agg_text ORDER BY a; -- ERROR ERROR: permission denied for foreign table agg_text SET ROLE regress_file_fdw_user; \t on -EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_text WHERE a > 0; +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: @abs_srcdir@/data/agg.data + Foreign File: .../agg.data \t off -- file FDW allows foreign tables to be accessed without user mapping |