🌐 AI搜索 & 代理 主页
Skip to content

Commit 86a3f2d

Browse files
tglsfdctglsfdc
committed
Add FORCE_NOT_NULL support to the file_fdw foreign data wrapper.
This is implemented as a per-column boolean option, rather than trying to match COPY's convention of a single option listing the column names. Shigeru Hanada, reviewed by KaiGai Kohei
1 parent 9d306c6 commit 86a3f2d

File tree

5 files changed

+185
-12
lines changed

5 files changed

+185
-12
lines changed

‎contrib/file_fdw/data/text.csv‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,4 @@
1+
AAA,aaa
2+
XYZ,xyz
3+
NULL,NULL
4+
ABC,abc

‎contrib/file_fdw/file_fdw.c‎

Lines changed: 105 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -23,8 +23,10 @@
2323
#include "foreign/fdwapi.h"
2424
#include "foreign/foreign.h"
2525
#include "miscadmin.h"
26+
#include "nodes/makefuncs.h"
2627
#include "optimizer/cost.h"
2728
#include "utils/rel.h"
29+
#include "utils/syscache.h"
2830

2931
PG_MODULE_MAGIC;
3032

@@ -40,6 +42,8 @@ struct FileFdwOption
4042
/*
4143
* Valid options for file_fdw.
4244
* These options are based on the options for COPY FROM command.
45+
* But note that force_not_null is handled as a boolean option attached to
46+
* each column, not as a table option.
4347
*
4448
* Note: If you are adding new option for user mapping, you need to modify
4549
* fileGetOptions(), which currently doesn't bother to look at user mappings.
@@ -57,17 +61,12 @@ static struct FileFdwOption valid_options[] = {
5761
{"escape", ForeignTableRelationId},
5862
{"null", ForeignTableRelationId},
5963
{"encoding", ForeignTableRelationId},
64+
{"force_not_null", AttributeRelationId},
6065

6166
/*
6267
* force_quote is not supported by file_fdw because it's for COPY TO.
6368
*/
6469

65-
/*
66-
* force_not_null is not supported by file_fdw. It would need a parser
67-
* for list of columns, not to mention a way to check the column list
68-
* against the table.
69-
*/
70-
7170
/* Sentinel */
7271
{NULL, InvalidOid}
7372
};
@@ -109,6 +108,7 @@ static void fileEndForeignScan(ForeignScanState *node);
109108
static bool is_valid_option(const char *option, Oid context);
110109
static void fileGetOptions(Oid foreigntableid,
111110
char **filename, List **other_options);
111+
static List *get_file_fdw_attribute_options(Oid relid);
112112
static void estimate_costs(PlannerInfo *root, RelOptInfo *baserel,
113113
const char *filename,
114114
Cost *startup_cost, Cost *total_cost);
@@ -145,6 +145,7 @@ file_fdw_validator(PG_FUNCTION_ARGS)
145145
List *options_list = untransformRelOptions(PG_GETARG_DATUM(0));
146146
Oid catalog = PG_GETARG_OID(1);
147147
char *filename = NULL;
148+
DefElem *force_not_null = NULL;
148149
List *other_options = NIL;
149150
ListCell *cell;
150151

@@ -198,7 +199,11 @@ file_fdw_validator(PG_FUNCTION_ARGS)
198199
buf.data)));
199200
}
200201

201-
/* Separate out filename, since ProcessCopyOptions won't allow it */
202+
/*
203+
* Separate out filename and force_not_null, since ProcessCopyOptions
204+
* won't accept them. (force_not_null only comes in a boolean
205+
* per-column flavor here.)
206+
*/
202207
if (strcmp(def->defname, "filename") == 0)
203208
{
204209
if (filename)
@@ -207,6 +212,16 @@ file_fdw_validator(PG_FUNCTION_ARGS)
207212
errmsg("conflicting or redundant options")));
208213
filename = defGetString(def);
209214
}
215+
else if (strcmp(def->defname, "force_not_null") == 0)
216+
{
217+
if (force_not_null)
218+
ereport(ERROR,
219+
(errcode(ERRCODE_SYNTAX_ERROR),
220+
errmsg("conflicting or redundant options")));
221+
force_not_null = def;
222+
/* Don't care what the value is, as long as it's a legal boolean */
223+
(void) defGetBoolean(def);
224+
}
210225
else
211226
other_options = lappend(other_options, def);
212227
}
@@ -277,6 +292,7 @@ fileGetOptions(Oid foreigntableid,
277292
options = list_concat(options, wrapper->options);
278293
options = list_concat(options, server->options);
279294
options = list_concat(options, table->options);
295+
options = list_concat(options, get_file_fdw_attribute_options(foreigntableid));
280296

281297
/*
282298
* Separate out the filename.
@@ -306,6 +322,88 @@ fileGetOptions(Oid foreigntableid,
306322
*other_options = options;
307323
}
308324

325+
/*
326+
* Retrieve per-column generic options from pg_attribute and construct a list
327+
* of DefElems representing them.
328+
*
329+
* At the moment we only have "force_not_null", which should be combined into
330+
* a single DefElem listing all such columns, since that's what COPY expects.
331+
*/
332+
static List *
333+
get_file_fdw_attribute_options(Oid relid)
334+
{
335+
Relation rel;
336+
TupleDesc tupleDesc;
337+
AttrNumber natts;
338+
AttrNumber attnum;
339+
List *fnncolumns = NIL;
340+
341+
rel = heap_open(relid, AccessShareLock);
342+
tupleDesc = RelationGetDescr(rel);
343+
natts = tupleDesc->natts;
344+
345+
/* Retrieve FDW options for all user-defined attributes. */
346+
for (attnum = 1; attnum <= natts; attnum++)
347+
{
348+
HeapTuple tuple;
349+
Form_pg_attribute attr;
350+
Datum datum;
351+
bool isnull;
352+
353+
/* Skip dropped attributes. */
354+
if (tupleDesc->attrs[attnum - 1]->attisdropped)
355+
continue;
356+
357+
/*
358+
* We need the whole pg_attribute tuple not just what is in the
359+
* tupleDesc, so must do a catalog lookup.
360+
*/
361+
tuple = SearchSysCache2(ATTNUM,
362+
RelationGetRelid(rel),
363+
Int16GetDatum(attnum));
364+
if (!HeapTupleIsValid(tuple))
365+
elog(ERROR, "cache lookup failed for attribute %d of relation %u",
366+
attnum, RelationGetRelid(rel));
367+
attr = (Form_pg_attribute) GETSTRUCT(tuple);
368+
369+
datum = SysCacheGetAttr(ATTNUM,
370+
tuple,
371+
Anum_pg_attribute_attfdwoptions,
372+
&isnull);
373+
if (!isnull)
374+
{
375+
List *options = untransformRelOptions(datum);
376+
ListCell *lc;
377+
378+
foreach(lc, options)
379+
{
380+
DefElem *def = (DefElem *) lfirst(lc);
381+
382+
if (strcmp(def->defname, "force_not_null") == 0)
383+
{
384+
if (defGetBoolean(def))
385+
{
386+
char *attname = pstrdup(NameStr(attr->attname));
387+
388+
fnncolumns = lappend(fnncolumns, makeString(attname));
389+
}
390+
}
391+
/* maybe in future handle other options here */
392+
}
393+
}
394+
395+
ReleaseSysCache(tuple);
396+
}
397+
398+
heap_close(rel, AccessShareLock);
399+
400+
/* Return DefElem only when some column(s) have force_not_null */
401+
if (fnncolumns != NIL)
402+
return list_make1(makeDefElem("force_not_null", (Node *) fnncolumns));
403+
else
404+
return NIL;
405+
}
406+
309407
/*
310408
* filePlanForeignScan
311409
* Create a FdwPlan for a scan on the foreign table

‎contrib/file_fdw/input/file_fdw.source‎

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -78,6 +78,22 @@ CREATE FOREIGN TABLE agg_bad (
7878
) SERVER file_server
7979
OPTIONS (format 'csv', filename '@abs_srcdir@/data/agg.bad', header 'true', delimiter ';', quote '@', escape '"', null '');
8080

81+
-- per-column options tests
82+
CREATE FOREIGN TABLE text_csv (
83+
word1 text OPTIONS (force_not_null 'true'),
84+
word2 text OPTIONS (force_not_null 'off')
85+
) SERVER file_server
86+
OPTIONS (format 'text', filename '@abs_srcdir@/data/text.csv', null 'NULL');
87+
SELECT * FROM text_csv; -- ERROR
88+
ALTER FOREIGN TABLE text_csv OPTIONS (SET format 'csv');
89+
SELECT * FROM text_csv;
90+
91+
-- force_not_null is not allowed to be specified at any foreign object level:
92+
ALTER FOREIGN DATA WRAPPER file_fdw OPTIONS (ADD force_not_null '*'); -- ERROR
93+
ALTER SERVER file_server OPTIONS (ADD force_not_null '*'); -- ERROR
94+
CREATE USER MAPPING FOR public SERVER file_server OPTIONS (force_not_null '*'); -- ERROR
95+
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (force_not_null '*'); -- ERROR
96+
8197
-- basic query tests
8298
SELECT * FROM agg_text WHERE b > 10.0 ORDER BY a;
8399
SELECT * FROM agg_csv ORDER BY a;

‎contrib/file_fdw/output/file_fdw.source‎

Lines changed: 33 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -93,6 +93,37 @@ CREATE FOREIGN TABLE agg_bad (
9393
b float4
9494
) SERVER file_server
9595
OPTIONS (format 'csv', filename '@abs_srcdir@/data/agg.bad', header 'true', delimiter ';', quote '@', escape '"', null '');
96+
-- per-column options tests
97+
CREATE FOREIGN TABLE text_csv (
98+
word1 text OPTIONS (force_not_null 'true'),
99+
word2 text OPTIONS (force_not_null 'off')
100+
) SERVER file_server
101+
OPTIONS (format 'text', filename '@abs_srcdir@/data/text.csv', null 'NULL');
102+
SELECT * FROM text_csv; -- ERROR
103+
ERROR: COPY force not null available only in CSV mode
104+
ALTER FOREIGN TABLE text_csv OPTIONS (SET format 'csv');
105+
SELECT * FROM text_csv;
106+
word1 | word2
107+
-------+-------
108+
AAA | aaa
109+
XYZ | xyz
110+
NULL |
111+
ABC | abc
112+
(4 rows)
113+
114+
-- force_not_null is not allowed to be specified at any foreign object level:
115+
ALTER FOREIGN DATA WRAPPER file_fdw OPTIONS (ADD force_not_null '*'); -- ERROR
116+
ERROR: invalid option "force_not_null"
117+
HINT: Valid options in this context are:
118+
ALTER SERVER file_server OPTIONS (ADD force_not_null '*'); -- ERROR
119+
ERROR: invalid option "force_not_null"
120+
HINT: Valid options in this context are:
121+
CREATE USER MAPPING FOR public SERVER file_server OPTIONS (force_not_null '*'); -- ERROR
122+
ERROR: invalid option "force_not_null"
123+
HINT: Valid options in this context are:
124+
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (force_not_null '*'); -- ERROR
125+
ERROR: invalid option "force_not_null"
126+
HINT: Valid options in this context are: filename, format, header, delimiter, quote, escape, null, encoding
96127
-- basic query tests
97128
SELECT * FROM agg_text WHERE b > 10.0 ORDER BY a;
98129
a | b
@@ -216,12 +247,13 @@ SET ROLE file_fdw_superuser;
216247
-- cleanup
217248
RESET ROLE;
218249
DROP EXTENSION file_fdw CASCADE;
219-
NOTICE: drop cascades to 7 other objects
250+
NOTICE: drop cascades to 8 other objects
220251
DETAIL: drop cascades to server file_server
221252
drop cascades to user mapping for file_fdw_user
222253
drop cascades to user mapping for file_fdw_superuser
223254
drop cascades to user mapping for no_priv_user
224255
drop cascades to foreign table agg_text
225256
drop cascades to foreign table agg_csv
226257
drop cascades to foreign table agg_bad
258+
drop cascades to foreign table text_csv
227259
DROP ROLE file_fdw_superuser, file_fdw_user, no_priv_user;

‎doc/src/sgml/file-fdw.sgml‎

Lines changed: 27 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -111,14 +111,37 @@
111111
</variablelist>
112112

113113
<para>
114-
<command>COPY</>'s <literal>OIDS</literal>, <literal>FORCE_QUOTE</literal>,
115-
and <literal>FORCE_NOT_NULL</literal> options are currently not supported by
114+
A column of a foreign table created using this wrapper can have the
115+
following options:
116+
</para>
117+
118+
<variablelist>
119+
120+
<varlistentry>
121+
<term><literal>force_not_null</literal></term>
122+
123+
<listitem>
124+
<para>
125+
This is a boolean option. If true, it specifies that values of the
126+
column should not be matched against the null string (that is, the
127+
file-level <literal>null</literal> option). This has the same effect
128+
as listing the column in <command>COPY</>'s
129+
<literal>FORCE_NOT_NULL</literal> option.
130+
</para>
131+
</listitem>
132+
</varlistentry>
133+
134+
</variablelist>
135+
136+
<para>
137+
<command>COPY</>'s <literal>OIDS</literal> and
138+
<literal>FORCE_QUOTE</literal> options are currently not supported by
116139
<literal>file_fdw</>.
117140
</para>
118141

119142
<para>
120-
These options can only be specified for a foreign table, not in the
121-
options of the <literal>file_fdw</> foreign-data wrapper, nor in the
143+
These options can only be specified for a foreign table or its columns, not
144+
in the options of the <literal>file_fdw</> foreign-data wrapper, nor in the
122145
options of a server or user mapping using the wrapper.
123146
</para>
124147

0 commit comments

Comments
 (0)