Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit86a3f2d

Browse files
committed
Add FORCE_NOT_NULL support to the file_fdw foreign data wrapper.
This is implemented as a per-column boolean option, rather than tryingto match COPY's convention of a single option listing the column names.Shigeru Hanada, reviewed by KaiGai Kohei
1 parent9d306c6 commit86a3f2d

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
staticboolis_valid_option(constchar*option,Oidcontext);
110109
staticvoidfileGetOptions(Oidforeigntableid,
111110
char**filename,List**other_options);
111+
staticList*get_file_fdw_attribute_options(Oidrelid);
112112
staticvoidestimate_costs(PlannerInfo*root,RelOptInfo*baserel,
113113
constchar*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
Oidcatalog=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+
elseif (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+
staticList*
333+
get_file_fdw_attribute_options(Oidrelid)
334+
{
335+
Relationrel;
336+
TupleDesctupleDesc;
337+
AttrNumbernatts;
338+
AttrNumberattnum;
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+
HeapTupletuple;
349+
Form_pg_attributeattr;
350+
Datumdatum;
351+
boolisnull;
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+
returnlist_make1(makeDefElem("force_not_null", (Node*)fnncolumns));
403+
else
404+
returnNIL;
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
bfloat4
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 to7 other objects
250+
NOTICE: drop cascades to8 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 theoptions 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)
[8]ページ先頭
©2009-2025 Movatter.jp