Hi,
I would like to propose support for per-column generic option, which is
defined in the SQL/MED standard. In 9.0 release, support for foreign
tables and per-table generic option have been added, but support for
per-column generic option hasn't.
Please examine the description below and attached patch
per_column_option_v1.patch. Any comments or questions are welcome.
Possible use cases
~~~~~~~~~~~~~~~~~~
Purpose of per-column generic option is passing column-specific settings
to the foreign-data wrapper.
1) For file_fdw, per-column generic option can be used to represent
per-column COPY option FORCE_NOT_NULL with boolean value (currently
file_fdw doesn't support FORCE_NOT_NULL option).
2) For postgresql_fdw (even though it has not been implemented yet),
per-column generic option could be used to represent the name of the
column on the foreign side. It is similar to per-table generic option
such as "nspname" and "relname" for namespace name/relation name,
proposed in the last development cycles. Such option would be named
"attname" after pg_attribute.attname.
Catalog design
~~~~~~~~~~~~~~
This proposal requires changing some catalogs.
1) To store per-column generic options, new attribute attfdwoptions
(text[]) was added at tail of pg_attribute. This is similar to the
generic option of other FDW objects such as FDW, server, user mapping
and foreign table. Existing attribute attoptions is not used for
generic options.
2) To conform the SQL/MED standard, an information_schema view
COLUMN_OPTIONS was added. Also underlying view
_pg_foreign_table_columns was added to show only columns which current
user has any access privilege. This fashion is same as other FDW views.
Syntax design
~~~~~~~~~~~~~
Per-column generic options can be operated via CREATE FOREIGN TABLE
statement and ALTER FOREIGN TABLE statement. Similar to other generic
options, ADD/SET/DROP can be specified for ALTER FOREIGN TABLE.
1) In CREATE FOREIGN TABLE statement, per-column generic options can be
specified in a column definition without operation qualifier such as
SET, ADD and DROP; all options are treated as ADD. Similar to other FDW
objects, multiple options can be specified for one column by separating
option-value pairs with comma.
-- multiple options can be specified for one column at once
CREATE FOREIGN TABLE foo (
c1 int OPTIONS (opt1 'value1'),
c2 text OPTIONS (opt2 'values2', opt3 'value3'),
c3 date OPTIONS (opt4 'value4) NOT NULL
) SERVER server;
To avoid syntax conflict between "OPTIONS (...)" and "DEFAULT b_expr"
(b_expr can end with a token "OPTION"), I placed OPTIONS (...) between
data type and any other column qualifier such as default values and
constraints.
The SQL/MED standard doesn't consider any column qualifier other than
data type, so it defines the syntax simply as below. I think new syntax
conforms the standard...
CREATE FOREIGN TABLE foo (
{ column_name data_type [ OPTIONS ( option 'value' [, ...] ) ] }
[, ... ]
) SERVER server [ OPTIONS (...) ]
Please note that CREATE FOREIGN TABLE shares the columnDef, a syntax
element for a column definition, with CREATE TABLE. I thought that they
should so, and I didn't introduce separated syntax for foreign tables.
2) Similar to other FDW objects' ALTER statement, ALTER FOREIGN TABLE
ALTER COLUMN accepts ADD/SET/DROP operation for each option. DROP
requires only option name.
ALTER FOREIGN TABLE foo ALTER COLUMN c1
OPTIONS (SET opt1 'VALUE1'); -- should be set in advance
ALTER FOREIGN TABLE foo ALTER COLUMN c1
OPTIONS (ADD opt2 'VALUE1', DROP opt1);
Similar to other ALTER FOREIGN TABLE commands, ALTER COLUMN ... OPTIONS
(...) can be contained in the list of ALTER commands.
ALTER FOREIGN TABLE foo
ALTER COLUMN col1 OPTIONS (opt1 'val1'),
ALTER COLUMN col2 SET NOT NULL;
psql support
~~~~~~~~~~~~
1) psql should support describing per-column generic options, so \dec
command was added. If the form \dec+ is used, generic options are also
displayed. Output sample is:
postgres=# \dec csv_branches
List of foreign table columns
Schema | Table | Column
--------+--------------+----------
public | csv_branches | bid
public | csv_branches | bbalance
public | csv_branches | filler
(3 rows)
postgres=# \dec+ csv_branches
List of foreign table columns
Schema | Table | Column | Options
--------+--------------+----------+------------------------
public | csv_branches | bid | {force_not_null=false}
public | csv_branches | bbalance | {force_not_null=true}
public | csv_branches | filler |
(3 rows)
Here I found an inconsistency about privilege to see generic options
(not only column but also FDW and server et al). The
information_schema.*_options only shows options which are associated to
objects that current user can access, but \de*+ doesn't have such
restriction. \de* commands should be fixed to hide forbidden objects?
2) psql can support tab-completion CREATE/ALTER FOREIGN TABLE statement
about OPTIONS, but the patch doesn't include this feature.
pg_dump support
~~~~~~~~~~~~~~~
Sorry, I overlooked this issue till writing this post... I'm going to
work on this and post revised patch soon. Please examine other parts first.
Documents
~~~~~~~~~
1) Is "generic options" proper term to mean FDW-specific option
associated to a FDW object? It's used in the SQL/MED standard, but
seems not popular... "FDW option" would be better than "generic option"?
Regards,
--
Shigeru Hanada
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 8504555..d1225e1 100644
*** a/doc/src/sgml/catalogs.sgml
--- b/doc/src/sgml/catalogs.sgml
***************
*** 1152,1157 ****
--- 1152,1166 ----
</entry>
</row>
+ <row>
+ <entry><structfield>attfdwoptions</structfield></entry>
+ <entry><type>text[]</type></entry>
+ <entry></entry>
+ <entry>
+ Attribute-level generic options, as <quote>keyword=value</> strings
+ </entry>
+ </row>
+
</tbody>
</tgroup>
</table>
diff --git a/doc/src/sgml/information_schema.sgml
b/doc/src/sgml/information_schema.sgml
index 6df69db..539f573 100644
*** a/doc/src/sgml/information_schema.sgml
--- b/doc/src/sgml/information_schema.sgml
***************
*** 958,963 ****
--- 958,1026 ----
</table>
</sect1>
+ <sect1 id="infoschema-column-options">
+ <title><literal>column_options</literal></title>
+
+ <para>
+ The view <literal>column_options</literal> contains all the
+ options defined for foreign table columns in the current database. Only
+ those foreign table columns are shown that the current user has access to
+ (by way of being the owner or having some privilege).
+ </para>
+
+ <table>
+ <title><literal>column_options</literal> Columns</title>
+
+ <tgroup cols="3">
+ <thead>
+ <row>
+ <entry>Name</entry>
+ <entry>Data Type</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry><literal>table_catalog</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of the database that contains the foreign table (always the
current database)</entry>
+ </row>
+
+ <row>
+ <entry><literal>table_schema</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of the schema that contains the foreign table</entry>
+ </row>
+
+ <row>
+ <entry><literal>table_name</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of the foreign table</entry>
+ </row>
+
+ <row>
+ <entry><literal>column_name</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of the column</entry>
+ </row>
+
+ <row>
+ <entry><literal>option_name</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of an option</entry>
+ </row>
+
+ <row>
+ <entry><literal>option_value</literal></entry>
+ <entry><type>character_data</type></entry>
+ <entry>Value of the option</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect1>
+
<sect1 id="infoschema-column-privileges">
<title><literal>column_privileges</literal></title>
diff --git a/doc/src/sgml/ref/alter_foreign_table.sgml
b/doc/src/sgml/ref/alter_foreign_table.sgml
index a45df02..95ae02a 100644
*** a/doc/src/sgml/ref/alter_foreign_table.sgml
--- b/doc/src/sgml/ref/alter_foreign_table.sgml
*************** ALTER FOREIGN TABLE <replaceable class="
*** 36,41 ****
--- 36,42 ----
DROP [ COLUMN ] [ IF EXISTS ] <replaceable
class="PARAMETER">column</replaceable> [ RESTRICT | CASCADE ]
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> [
SET DATA ] TYPE <replaceable class="PARAMETER">type</replaceable>
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> {
SET | DROP } NOT NULL
+ ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable>
OPTIONS ( [ ADD | SET | DROP ] <replaceable
class="PARAMETER">option</replaceable> ['<replaceable
class="PARAMETER">value</replaceable>'] [, ... ])
OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
OPTIONS ( [ ADD | SET | DROP ] <replaceable
class="PARAMETER">option</replaceable> ['<replaceable
class="PARAMETER">value</replaceable>'] [, ... ])
</synopsis>
*************** ALTER FOREIGN TABLE <replaceable class="
*** 125,136 ****
<term><literal>OPTIONS ( [ ADD | SET | DROP ] <replaceable
class="PARAMETER">option</replaceable> ['<replaceable
class="PARAMETER">value</replaceable>'] [, ... ] )</literal></term>
<listitem>
<para>
! Change options for the foreign table.
<literal>ADD</>, <literal>SET</>, and <literal>DROP</>
specify the action to be performed. <literal>ADD</> is assumed
! if no operation is explicitly specified. Option names must be
! unique; names and values are also validated using the foreign
! data wrapper library.
</para>
</listitem>
</varlistentry>
--- 126,137 ----
<term><literal>OPTIONS ( [ ADD | SET | DROP ] <replaceable
class="PARAMETER">option</replaceable> ['<replaceable
class="PARAMETER">value</replaceable>'] [, ... ] )</literal></term>
<listitem>
<para>
! Change options for the foreign table or the column of the foreign table.
<literal>ADD</>, <literal>SET</>, and <literal>DROP</>
specify the action to be performed. <literal>ADD</> is assumed
! if no operation is explicitly specified. Option names must be unique
! in each associated object; names and values are also validated using the
! foreign data wrapper library.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml
b/doc/src/sgml/ref/create_foreign_table.sgml
index ad91072..8863386 100644
*** a/doc/src/sgml/ref/create_foreign_table.sgml
--- b/doc/src/sgml/ref/create_foreign_table.sgml
***************
*** 19,25 ****
<refsynopsisdiv>
<synopsis>
CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable
class="PARAMETER">table_name</replaceable> ( [
! { <replaceable class="PARAMETER">column_name</replaceable> <replaceable
class="PARAMETER">data_type</replaceable> [ NULL | NOT NULL ] }
[, ... ]
] )
SERVER <replaceable class="parameter">server_name</replaceable>
--- 19,25 ----
<refsynopsisdiv>
<synopsis>
CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable
class="PARAMETER">table_name</replaceable> ( [
! { <replaceable class="PARAMETER">column_name</replaceable> <replaceable
class="PARAMETER">data_type</replaceable> [ OPTIONS ( <replaceable
class="PARAMETER">option</replaceable> '<replaceable
class="PARAMETER">value</replaceable>' [, ... ] ) ] [ NULL | NOT NULL ] }
[, ... ]
] )
SERVER <replaceable class="parameter">server_name</replaceable>
*************** CREATE FOREIGN TABLE [ IF NOT EXISTS ] <
*** 138,147 ****
<term><literal>OPTIONS ( <replaceable
class="PARAMETER">option</replaceable> '<replaceable
class="PARAMETER">value</replaceable>' [, ...] )</literal></term>
<listitem>
<para>
! Options to be associated with the new foreign table.
The allowed option names and values are specific to each foreign
data wrapper and are validated using the foreign-data wrapper's
! validator function. Option names must be unique.
</para>
</listitem>
</varlistentry>
--- 138,149 ----
<term><literal>OPTIONS ( <replaceable
class="PARAMETER">option</replaceable> '<replaceable
class="PARAMETER">value</replaceable>' [, ...] )</literal></term>
<listitem>
<para>
! Options to be associated with the new foreign table or the column of
! the foreign table.
The allowed option names and values are specific to each foreign
data wrapper and are validated using the foreign-data wrapper's
! validator function. Option names must be unique in each associated
! object.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index eaf901d..e9e9a61 100644
*** a/doc/src/sgml/ref/psql-ref.sgml
--- b/doc/src/sgml/ref/psql-ref.sgml
*************** testdb=>
*** 1109,1114 ****
--- 1109,1128 ----
<varlistentry>
+ <term><literal>\dec[+] [ <link
linkend="APP-PSQL-patterns"><replaceable
class="parameter">pattern</replaceable></link> ]</literal></term>
+ <listitem>
+ <para>
+ Lists foreign table columns (mnemonic: <quote>external
columns</quote>).
+ If <replaceable class="parameter">pattern</replaceable> is
+ specified, only entries whose table name or schema name matches
+ the pattern are listed. If the form <literal>\dec+</literal>
+ is used, generic options are also displayed.
+ </para>
+ </listitem>
+ </varlistentry>
+
+
+ <varlistentry>
<term><literal>\deu[+] [ <link
linkend="APP-PSQL-patterns"><replaceable
class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
diff --git a/src/backend/access/common/tupdesc.c
b/src/backend/access/common/tupdesc.c
index 16979c4..4ffedab 100644
*** a/src/backend/access/common/tupdesc.c
--- b/src/backend/access/common/tupdesc.c
*************** equalTupleDescs(TupleDesc tupdesc1, Tupl
*** 362,368 ****
return false;
if (attr1->attcollation != attr2->attcollation)
return false;
! /* attacl and attoptions are not even present... */
}
if (tupdesc1->constr != NULL)
--- 362,368 ----
return false;
if (attr1->attcollation != attr2->attcollation)
return false;
! /* attacl, attoptions and attfdwoptions are not even present...
*/
}
if (tupdesc1->constr != NULL)
*************** TupleDescInitEntry(TupleDesc desc,
*** 482,488 ****
att->attisdropped = false;
att->attislocal = true;
att->attinhcount = 0;
! /* attacl and attoptions are not present in tupledescs */
tuple = SearchSysCache1(TYPEOID, ObjectIdGetDatum(oidtypeid));
if (!HeapTupleIsValid(tuple))
--- 482,488 ----
att->attisdropped = false;
att->attislocal = true;
att->attinhcount = 0;
! /* attacl, attoptions and attfdwoptions are not present in tupledescs */
tuple = SearchSysCache1(TYPEOID, ObjectIdGetDatum(oidtypeid));
if (!HeapTupleIsValid(tuple))
diff --git a/src/backend/catalog/genbki.pl b/src/backend/catalog/genbki.pl
index 0aeaf5b..d91af52 100644
*** a/src/backend/catalog/genbki.pl
--- b/src/backend/catalog/genbki.pl
*************** sub emit_pgattr_row
*** 369,375 ****
attislocal => 't',
attinhcount => '0',
attacl => '_null_',
! attoptions => '_null_'
);
return {%PGATTR_DEFAULTS, %row};
}
--- 369,376 ----
attislocal => 't',
attinhcount => '0',
attacl => '_null_',
! attoptions => '_null_',
! attfdwoptions => '_null_'
);
return {%PGATTR_DEFAULTS, %row};
}
*************** sub emit_schemapg_row
*** 400,405 ****
--- 401,407 ----
# Only the fixed-size portions of the descriptors are ever used.
delete $row->{attacl};
delete $row->{attoptions};
+ delete $row->{attfdwoptions};
# Expand booleans from 'f'/'t' to 'false'/'true'.
# Some values might be other macros (eg FLOAT4PASSBYVAL), don't change.
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index a6e541d..f3090e2 100644
*** a/src/backend/catalog/heap.c
--- b/src/backend/catalog/heap.c
*************** static List *insert_ordered_unique_oid(L
*** 126,132 ****
*/
/*
! * The initializers below do not include the attoptions or attacl fields,
* but that's OK - we're never going to reference anything beyond the
* fixed-size portion of the structure anyway.
*/
--- 126,132 ----
*/
/*
! * The initializers below do not include trailing variable length fields,
* but that's OK - we're never going to reference anything beyond the
* fixed-size portion of the structure anyway.
*/
*************** InsertPgAttributeTuple(Relation pg_attri
*** 607,612 ****
--- 607,614 ----
/* start out with empty permissions and empty options */
nulls[Anum_pg_attribute_attacl - 1] = true;
nulls[Anum_pg_attribute_attoptions - 1] = true;
+ /* FIXME use content of OPTIONS (...) if any. */
+ nulls[Anum_pg_attribute_attfdwoptions - 1] = true;
tup = heap_form_tuple(RelationGetDescr(pg_attribute_rel), values,
nulls);
diff --git a/src/backend/catalog/information_schema.sql
b/src/backend/catalog/information_schema.sql
index 452a0ea..a8e8a12 100644
*** a/src/backend/catalog/information_schema.sql
--- b/src/backend/catalog/information_schema.sql
*************** GRANT SELECT ON element_types TO PUBLIC;
*** 2449,2454 ****
--- 2449,2487 ----
-- SQL/MED views; these use section numbers from part 9 of the standard.
+ /* Base view for foreign table columns */
+ CREATE VIEW _pg_foreign_table_columns AS
+ SELECT n.nspname,
+ c.relname,
+ a.attname,
+ a.attfdwoptions
+ FROM pg_foreign_table t, pg_authid u, pg_namespace n, pg_class c,
+ pg_attribute a
+ WHERE u.oid = c.relowner
+ AND (pg_has_role(c.relowner, 'USAGE')
+ OR has_column_privilege(c.oid, a.attnum, 'SELECT, INSERT,
UPDATE, REFERENCES'))
+ AND n.oid = c.relnamespace
+ AND c.oid = t.ftrelid
+ AND c.relkind = 'f'
+ AND a.attrelid = c.oid
+ AND a.attnum > 0;
+
+ /*
+ * 24.2
+ * COLUMN_OPTIONS view
+ */
+ CREATE VIEW column_options AS
+ SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
+ c.nspname AS table_schema,
+ c.relname AS table_name,
+ c.attname AS column_name,
+ CAST((pg_options_to_table(c.attfdwoptions)).option_name AS
sql_identifier) AS option_name,
+ CAST((pg_options_to_table(c.attfdwoptions)).option_value AS
character_data) AS option_value
+ FROM _pg_foreign_table_columns c;
+
+ GRANT SELECT ON column_options TO PUBLIC;
+
+
/* Base view for foreign-data wrappers */
CREATE VIEW _pg_foreign_data_wrappers AS
SELECT w.oid,
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 2c9f855..d604273 100644
*** a/src/backend/commands/tablecmds.c
--- b/src/backend/commands/tablecmds.c
*************** static void ATPrepAlterColumnType(List *
*** 343,348 ****
--- 343,349 ----
static bool ATColumnChangeRequiresRewrite(Node *expr, AttrNumber varattno);
static void ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
AlterTableCmd *cmd, LOCKMODE
lockmode);
+ static void ATExecAlterColumnGenericOptions(Relation rel, const char
*colName, List *options, LOCKMODE lockmode);
static void ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab,
LOCKMODE lockmode);
static void ATPostAlterTypeParse(char *cmd, List **wqueue, LOCKMODE lockmode);
static void change_owner_recurse_to_sequences(Oid relationOid,
*************** AlterTableGetLockLevel(List *cmds)
*** 2606,2611 ****
--- 2607,2613 ----
case AT_DropNotNull: /* may change some SQL
plans */
case AT_SetNotNull:
case AT_GenericOptions:
+ case AT_AlterColumnGenericOptions:
cmd_lockmode = AccessExclusiveLock;
break;
*************** ATPrepCmd(List **wqueue, Relation rel, A
*** 2880,2885 ****
--- 2882,2893 ----
ATPrepAlterColumnType(wqueue, tab, rel, recurse,
recursing, cmd, lockmode);
pass = AT_PASS_ALTER_TYPE;
break;
+ case AT_AlterColumnGenericOptions:
+ ATSimplePermissions(rel, ATT_FOREIGN_TABLE);
+ /* This command never recurses */
+ /* No command-specific prep needed */
+ pass = AT_PASS_MISC;
+ break;
case AT_ChangeOwner: /* ALTER OWNER */
/* This command never recurses */
/* No command-specific prep needed */
*************** ATExecCmd(List **wqueue, AlteredTableInf
*** 3113,3118 ****
--- 3121,3129 ----
case AT_AlterColumnType: /* ALTER COLUMN TYPE */
ATExecAlterColumnType(tab, rel, cmd, lockmode);
break;
+ case AT_AlterColumnGenericOptions: /* ALTER COLUMN OPTIONS
*/
+ ATExecAlterColumnGenericOptions(rel, cmd->name, (List
*) cmd->def, lockmode);
+ break;
case AT_ChangeOwner: /* ALTER OWNER */
ATExecChangeOwner(RelationGetRelid(rel),
get_role_oid(cmd->name, false),
*************** ATExecAlterColumnType(AlteredTableInfo *
*** 7162,7167 ****
--- 7173,7272 ----
heap_freetuple(heapTup);
}
+ static void
+ ATExecAlterColumnGenericOptions(Relation rel,
+ const char
*colName,
+ List *options,
+ LOCKMODE
lockmode)
+ {
+ Relation ftrel;
+ Relation attrel;
+ ForeignServer *server;
+ ForeignDataWrapper *fdw;
+ HeapTuple tuple;
+ HeapTuple newtuple;
+ bool isnull;
+ Datum repl_val[Natts_pg_attribute];
+ bool repl_null[Natts_pg_attribute];
+ bool repl_repl[Natts_pg_attribute];
+ Datum datum;
+ Form_pg_foreign_table fttableform;
+ Form_pg_attribute atttableform;
+
+ if (options == NIL)
+ return;
+
+ /* First, determine FDW validator associated to the foreign table. */
+ ftrel = heap_open(ForeignTableRelationId, AccessShareLock);
+ tuple = SearchSysCache1(FOREIGNTABLEREL, rel->rd_id);
+ if (!HeapTupleIsValid(tuple))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("foreign table \"%s\" does not exist",
+ RelationGetRelationName(rel))));
+ fttableform = (Form_pg_foreign_table) GETSTRUCT(tuple);
+ server = GetForeignServer(fttableform->ftserver);
+ fdw = GetForeignDataWrapper(server->fdwid);
+
+ heap_close(ftrel, AccessShareLock);
+ ReleaseSysCache(tuple);
+
+ attrel = heap_open(AttributeRelationId, RowExclusiveLock);
+ tuple = SearchSysCacheAttName(RelationGetRelid(rel), colName);
+ if (!HeapTupleIsValid(tuple))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ errmsg("column \"%s\" of relation \"%s\" does
not exist",
+ colName,
RelationGetRelationName(rel))));
+
+ /* Prevent them from altering a system attribute */
+ atttableform = (Form_pg_attribute) GETSTRUCT(tuple);
+ if (atttableform->attnum <= 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot alter system column \"%s\"",
colName)));
+
+
+ /* Initialize buffers for new tuple values */
+ memset(repl_val, 0, sizeof(repl_val));
+ memset(repl_null, false, sizeof(repl_null));
+ memset(repl_repl, false, sizeof(repl_repl));
+
+ /* Extract the current options */
+ datum = SysCacheGetAttr(ATTNAME,
+ tuple,
+
Anum_pg_attribute_attfdwoptions,
+ &isnull);
+ if (isnull)
+ datum = PointerGetDatum(NULL);
+
+ /* Transform the options */
+ datum = transformGenericOptions(AttributeRelationId,
+ datum,
+ options,
+
fdw->fdwvalidator);
+
+ if (PointerIsValid(DatumGetPointer(datum)))
+ repl_val[Anum_pg_attribute_attfdwoptions - 1] = datum;
+ else
+ repl_null[Anum_pg_attribute_attfdwoptions - 1] = true;
+
+ repl_repl[Anum_pg_attribute_attfdwoptions - 1] = true;
+
+ /* Everything looks good - update the tuple */
+
+ newtuple = heap_modify_tuple(tuple, RelationGetDescr(attrel),
+ repl_val,
repl_null, repl_repl);
+ ReleaseSysCache(tuple);
+
+ simple_heap_update(attrel, &newtuple->t_self, newtuple);
+ CatalogUpdateIndexes(attrel, newtuple);
+
+ heap_close(attrel, RowExclusiveLock);
+
+ heap_freetuple(newtuple);
+ }
+
/*
* Cleanup after we've finished all the ALTER TYPE operations for a
* particular relation. We have to drop and recreate all the indexes
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index c9133dd..148110f 100644
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
*************** _copyColumnDef(ColumnDef *from)
*** 2312,2317 ****
--- 2312,2318 ----
COPY_NODE_FIELD(collClause);
COPY_SCALAR_FIELD(collOid);
COPY_NODE_FIELD(constraints);
+ COPY_NODE_FIELD(fdwoptions);
return newnode;
}
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 681f5f8..cb9be13 100644
*** a/src/backend/nodes/outfuncs.c
--- b/src/backend/nodes/outfuncs.c
*************** _outColumnDef(StringInfo str, ColumnDef
*** 2101,2106 ****
--- 2101,2107 ----
WRITE_NODE_FIELD(collClause);
WRITE_OID_FIELD(collOid);
WRITE_NODE_FIELD(constraints);
+ WRITE_NODE_FIELD(fdwoptions);
}
static void
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 1d39674..c6c3c39 100644
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
*************** alter_table_cmd:
*** 1760,1765 ****
--- 1760,1774 ----
def->raw_default = $8;
$$ = (Node *)n;
}
+ /* ALTER FOREIGN TABLE <name> ALTER [COLUMN] <colname>
OPTIONS */
+ | ALTER opt_column ColId alter_generic_options
+ {
+ AlterTableCmd *n =
makeNode(AlterTableCmd);
+ n->subtype =
AT_AlterColumnGenericOptions;
+ n->name = $3;
+ n->def = (Node *) $4;
+ $$ = (Node *)n;
+ }
/* ALTER TABLE <name> ADD CONSTRAINT ... */
| ADD_P TableConstraint
{
*************** TypedTableElement:
*** 2488,2494 ****
| TableConstraint
{ $$ = $1; }
;
! columnDef: ColId Typename ColQualList
{
ColumnDef *n = makeNode(ColumnDef);
n->colname = $1;
--- 2497,2503 ----
| TableConstraint
{ $$ = $1; }
;
! columnDef: ColId Typename create_generic_options ColQualList
{
ColumnDef *n = makeNode(ColumnDef);
n->colname = $1;
*************** columnDef: ColId Typename ColQualList
*** 2501,2507 ****
n->raw_default = NULL;
n->cooked_default = NULL;
n->collOid = InvalidOid;
! SplitColQualList($3, &n->constraints,
&n->collClause,
yyscanner);
$$ = (Node *)n;
}
--- 2510,2517 ----
n->raw_default = NULL;
n->cooked_default = NULL;
n->collOid = InvalidOid;
! n->fdwoptions = $3;
! SplitColQualList($4, &n->constraints,
&n->collClause,
yyscanner);
$$ = (Node *)n;
}
*************** AlterFdwStmt: ALTER FOREIGN DATA_P WRAPP
*** 3681,3687 ****
/* Options definition for CREATE FDW, SERVER and USER MAPPING */
create_generic_options:
OPTIONS '(' generic_option_list ')'
{ $$ = $3; }
! | /*EMPTY*/
{ $$ = NIL; }
;
generic_option_list:
--- 3691,3697 ----
/* Options definition for CREATE FDW, SERVER and USER MAPPING */
create_generic_options:
OPTIONS '(' generic_option_list ')'
{ $$ = $3; }
! | /*EMPTY*/
{ $$ = NIL }
;
generic_option_list:
diff --git a/src/backend/parser/parse_utilcmd.c
b/src/backend/parser/parse_utilcmd.c
index efb8fdd..8deb7af 100644
*** a/src/backend/parser/parse_utilcmd.c
--- b/src/backend/parser/parse_utilcmd.c
*************** transformColumnDefinition(CreateStmtCont
*** 548,553 ****
--- 548,585 ----
break;
}
}
+
+ /*
+ * Generate ALTER FOREIGN TABLE ALTER COLUMN statement which adds
+ * per-column generic options for this column.
+ */
+ if (column->fdwoptions != NIL)
+ {
+ AlterTableStmt *stmt;
+ AlterTableCmd *cmd;
+
+ cmd = makeNode(AlterTableCmd);
+ cmd->subtype = AT_AlterColumnGenericOptions;
+ cmd->name = column->colname;
+ cmd->def = (Node *) column->fdwoptions;
+ cmd->behavior = DROP_RESTRICT;
+ cmd->missing_ok = false;
+ cmd->validated = true;
+
+ stmt = makeNode(AlterTableStmt);
+ stmt->relation = cxt->relation;
+ stmt->cmds = NIL;
+ stmt->relkind = OBJECT_FOREIGN_TABLE;
+ stmt->cmds = lappend(stmt->cmds, cmd);
+
+ cxt->alist = lappend(cxt->alist, stmt);
+
+ foreach (clist, column->fdwoptions)
+ {
+ DefElem *option = (DefElem *) lfirst(clist);
+ elog(DEBUG3, "%s=%s", option->defname,
strVal(option->arg));
+ }
+ }
}
/*
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 378330b..bd1ec80 100644
*** a/src/bin/psql/command.c
--- b/src/bin/psql/command.c
*************** exec_command(const char *cmd,
*** 493,498 ****
--- 493,501 ----
case 't':
success =
listForeignTables(pattern, show_verbose);
break;
+ case 'c':
+ success =
listForeignTableColumns(pattern, show_verbose);
+ break;
default:
status = PSQL_CMD_UNKNOWN;
break;
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index b2c54b5..c0860f9 100644
*** a/src/bin/psql/describe.c
--- b/src/bin/psql/describe.c
*************** listForeignTables(const char *pattern, b
*** 3817,3822 ****
--- 3817,3885 ----
}
/*
+ * \dec
+ *
+ * Describes foreign table columns.
+ */
+ bool
+ listForeignTableColumns(const char *pattern, bool verbose)
+ {
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+
+ /* FIXME should be modified to 90200 before posting patch */
+ if (pset.sversion < 90100)
+ {
+ fprintf(stderr, _("The server (version %d.%d) does not support
foreign table column options.\n"),
+ pset.sversion / 10000, (pset.sversion / 100) %
100);
+ return true;
+ }
+
+ initPQExpBuffer(&buf);
+ printfPQExpBuffer(&buf,
+ "SELECT n.nspname AS \"%s\",\n"
+ " c.relname AS \"%s\",\n"
+ " a.attname AS \"%s\"",
+ gettext_noop("Schema"),
+ gettext_noop("Table"),
+ gettext_noop("Column"));
+
+ if (verbose)
+ appendPQExpBuffer(&buf,
+ ",\n a.attfdwoptions AS
\"%s\"",
+ gettext_noop("Options"));
+
+ appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_foreign_table ft,");
+ appendPQExpBuffer(&buf, "\n pg_catalog.pg_class c,");
+ appendPQExpBuffer(&buf, "\n pg_catalog.pg_namespace n,");
+ appendPQExpBuffer(&buf, "\n pg_catalog.pg_attribute a\n");
+ appendPQExpBuffer(&buf, "\nWHERE c.oid = ft.ftrelid");
+ appendPQExpBuffer(&buf, "\nAND n.oid = c.relnamespace\n");
+ appendPQExpBuffer(&buf, "\nAND a.attrelid= c.oid\n");
+ appendPQExpBuffer(&buf, "\nAND a.attnum > 0\n");
+
+ processSQLNamePattern(pset.db, &buf, pattern, true, false,
+ NULL, "n.nspname",
"c.relname", NULL);
+
+ appendPQExpBuffer(&buf, "ORDER BY 1, 2, a.attnum;");
+
+ res = PSQLexec(buf.data, false);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ myopt.nullPrint = NULL;
+ myopt.title = _("List of foreign table columns");
+ myopt.translate_header = true;
+
+ printQuery(res, &myopt, pset.queryFout, pset.logfile);
+
+ PQclear(res);
+ return true;
+ }
+
+ /*
* \dx
*
* Briefly describes installed extensions.
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index fb86d1e..b6bc472 100644
*** a/src/bin/psql/describe.h
--- b/src/bin/psql/describe.h
*************** extern bool listUserMappings(const char
*** 87,92 ****
--- 87,95 ----
/* \det */
extern bool listForeignTables(const char *pattern, bool verbose);
+ /* \dec */
+ extern bool listForeignTableColumns(const char *pattern, bool verbose);
+
/* \dL */
extern bool listLanguages(const char *pattern, bool verbose, bool showSystem);
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index ac5edca..0c8d011 100644
*** a/src/bin/psql/help.c
--- b/src/bin/psql/help.c
*************** slashUsage(unsigned short int pager)
*** 200,205 ****
--- 200,206 ----
fprintf(output, _(" \\ddp [PATTERN] list default
privileges\n"));
fprintf(output, _(" \\dD[S] [PATTERN] list domains\n"));
fprintf(output, _(" \\det[+] [PATTERN] list foreign tables\n"));
+ fprintf(output, _(" \\dec[+] [PATTERN] list foreign table
columns\n"));
fprintf(output, _(" \\des[+] [PATTERN] list foreign servers\n"));
fprintf(output, _(" \\deu[+] [PATTERN] list user mappings\n"));
fprintf(output, _(" \\dew[+] [PATTERN] list foreign-data
wrappers\n"));
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 0200a81..52e99a8 100644
*** a/src/include/catalog/catversion.h
--- b/src/include/catalog/catversion.h
***************
*** 53,58 ****
*/
/* yyyymmddN */
! #define CATALOG_VERSION_NO 201105231
#endif
--- 53,58 ----
*/
/* yyyymmddN */
! #define CATALOG_VERSION_NO 201106141
#endif
diff --git a/src/include/catalog/pg_attribute.h
b/src/include/catalog/pg_attribute.h
index 409d6ea..3ea87e8 100644
*** a/src/include/catalog/pg_attribute.h
--- b/src/include/catalog/pg_attribute.h
*************** CATALOG(pg_attribute,1249) BKI_BOOTSTRAP
*** 156,161 ****
--- 156,164 ----
/* Column-level options */
text attoptions[1];
+
+ /* Column-level FDW options */
+ text attfdwoptions[1];
} FormData_pg_attribute;
/*
*************** typedef FormData_pg_attribute *Form_pg_a
*** 179,185 ****
* ----------------
*/
! #define Natts_pg_attribute 20
#define Anum_pg_attribute_attrelid 1
#define Anum_pg_attribute_attname 2
#define Anum_pg_attribute_atttypid 3
--- 182,188 ----
* ----------------
*/
! #define Natts_pg_attribute 21
#define Anum_pg_attribute_attrelid 1
#define Anum_pg_attribute_attname 2
#define Anum_pg_attribute_atttypid 3
*************** typedef FormData_pg_attribute *Form_pg_a
*** 200,205 ****
--- 203,209 ----
#define Anum_pg_attribute_attcollation 18
#define Anum_pg_attribute_attacl 19
#define Anum_pg_attribute_attoptions 20
+ #define Anum_pg_attribute_attfdwoptions 21
/* ----------------
diff --git a/src/include/catalog/pg_class.h b/src/include/catalog/pg_class.h
index ffcce3c..caa285e 100644
*** a/src/include/catalog/pg_class.h
--- b/src/include/catalog/pg_class.h
*************** typedef FormData_pg_class *Form_pg_class
*** 132,138 ****
/* Note: "3" in the relfrozenxid column stands for FirstNormalTransactionId */
DATA(insert OID = 1247 ( pg_type PGNSP 71 0 PGUID 0 0 0 0 0 0 0
f f p r 29 0 t f f f f 3 _null_ _null_ ));
DESCR("");
! DATA(insert OID = 1249 ( pg_attribute PGNSP 75 0 PGUID 0 0 0 0 0 0 0
f f p r 20 0 f f f f f 3 _null_ _null_ ));
DESCR("");
DATA(insert OID = 1255 ( pg_proc PGNSP 81 0 PGUID 0 0 0 0 0 0 0
f f p r 25 0 t f f f f 3 _null_ _null_ ));
DESCR("");
--- 132,138 ----
/* Note: "3" in the relfrozenxid column stands for FirstNormalTransactionId */
DATA(insert OID = 1247 ( pg_type PGNSP 71 0 PGUID 0 0 0 0 0 0 0
f f p r 29 0 t f f f f 3 _null_ _null_ ));
DESCR("");
! DATA(insert OID = 1249 ( pg_attribute PGNSP 75 0 PGUID 0 0 0 0 0 0 0
f f p r 21 0 f f f f f 3 _null_ _null_ ));
DESCR("");
DATA(insert OID = 1255 ( pg_proc PGNSP 81 0 PGUID 0 0 0 0 0 0 0
f f p r 25 0 t f f f f 3 _null_ _null_ ));
DESCR("");
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 14937d4..5e82220 100644
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
*************** typedef struct ColumnDef
*** 500,505 ****
--- 500,506 ----
CollateClause *collClause; /* untransformed COLLATE spec, if any */
Oid collOid; /* collation OID
(InvalidOid if not set) */
List *constraints; /* other constraints on column */
+ List *fdwoptions; /* per-column FDW options */
} ColumnDef;
/*
*************** typedef enum AlterTableType
*** 1196,1201 ****
--- 1197,1203 ----
AT_DropConstraint, /* drop constraint */
AT_DropConstraintRecurse, /* internal to commands/tablecmds.c */
AT_AlterColumnType, /* alter column type */
+ AT_AlterColumnGenericOptions, /* alter column OPTIONS (...) */
AT_ChangeOwner, /* change owner */
AT_ClusterOn, /* CLUSTER ON */
AT_DropCluster, /* SET WITHOUT CLUSTER */
diff --git a/src/test/regress/expected/foreign_data.out
b/src/test/regress/expected/foreign_data.out
index e18eed8..d447a2c 100644
*** a/src/test/regress/expected/foreign_data.out
--- b/src/test/regress/expected/foreign_data.out
*************** ERROR: syntax error at or near "WITH OI
*** 646,653 ****
LINE 1: CREATE FOREIGN TABLE ft1 () SERVER sc WITH OIDS;
^
CREATE FOREIGN TABLE ft1 (
! c1 integer NOT NULL,
! c2 text,
c3 date
) SERVER sc OPTIONS (delimiter ',', quote '"');
COMMENT ON FOREIGN TABLE ft1 IS 'ft1';
--- 646,653 ----
LINE 1: CREATE FOREIGN TABLE ft1 () SERVER sc WITH OIDS;
^
CREATE FOREIGN TABLE ft1 (
! c1 integer OPTIONS (param1 'val1') NOT NULL,
! c2 text OPTIONS (param2 'val2', param3 'val3'),
c3 date
) SERVER sc OPTIONS (delimiter ',', quote '"');
COMMENT ON FOREIGN TABLE ft1 IS 'ft1';
*************** ALTER FOREIGN TABLE ft1 ADD COLUMN c6 in
*** 687,693 ****
ALTER FOREIGN TABLE ft1 ADD COLUMN c7 integer NOT NULL;
ALTER FOREIGN TABLE ft1 ADD COLUMN c8 integer;
ALTER FOREIGN TABLE ft1 ADD COLUMN c9 integer;
! ALTER FOREIGN TABLE ft1 ADD COLUMN c10 integer;
ALTER FOREIGN TABLE ft1 ALTER COLUMN c4 SET DEFAULT 0; -- ERROR
ERROR: "ft1" is not a table or view
ALTER FOREIGN TABLE ft1 ALTER COLUMN c5 DROP DEFAULT; -- ERROR
--- 687,693 ----
ALTER FOREIGN TABLE ft1 ADD COLUMN c7 integer NOT NULL;
ALTER FOREIGN TABLE ft1 ADD COLUMN c8 integer;
ALTER FOREIGN TABLE ft1 ADD COLUMN c9 integer;
! ALTER FOREIGN TABLE ft1 ADD COLUMN c10 integer OPTIONS (p1 'v1');
ALTER FOREIGN TABLE ft1 ALTER COLUMN c4 SET DEFAULT 0; -- ERROR
ERROR: "ft1" is not a table or view
ALTER FOREIGN TABLE ft1 ALTER COLUMN c5 DROP DEFAULT; -- ERROR
*************** ALTER FOREIGN TABLE ft1 ALTER COLUMN c8
*** 698,703 ****
--- 698,723 ----
ERROR: ALTER TYPE USING is only supported on plain tables
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE char(10);
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET DATA TYPE text;
+ ALTER FOREIGN TABLE ft1 ALTER COLUMN xmin OPTIONS (ADD p1 'v1'); -- ERROR
+ ERROR: cannot alter system column "xmin"
+ ALTER FOREIGN TABLE ft1 ALTER COLUMN c7 OPTIONS (ADD p1 'v1', ADD p2 'v2'),
+ ALTER COLUMN c8 OPTIONS (ADD p1 'v1', ADD p2 'v2');
+ ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 OPTIONS (SET p2 'V2', DROP p1);
+ \dec+
+ List of foreign table columns
+ Schema | Table | Column | Options
+ --------+-------+--------+---------------------------
+ public | ft1 | c1 | {param1=val1}
+ public | ft1 | c2 | {param2=val2,param3=val3}
+ public | ft1 | c3 |
+ public | ft1 | c4 |
+ public | ft1 | c6 |
+ public | ft1 | c7 | {p1=v1,p2=v2}
+ public | ft1 | c8 | {p2=V2}
+ public | ft1 | c9 |
+ public | ft1 | c10 | {p1=v1}
+ (9 rows)
+
-- can't change the column type if it's used elsewhere
CREATE TABLE use_ft1_column_type (x ft1);
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET DATA TYPE integer; -- ERROR
diff --git a/src/test/regress/sql/foreign_data.sql
b/src/test/regress/sql/foreign_data.sql
index d323921..80d8d6b 100644
*** a/src/test/regress/sql/foreign_data.sql
--- b/src/test/regress/sql/foreign_data.sql
*************** CREATE FOREIGN TABLE ft1 () SERVER no_se
*** 264,271 ****
CREATE FOREIGN TABLE ft1 (c1 serial) SERVER sc; -- ERROR
CREATE FOREIGN TABLE ft1 () SERVER sc WITH OIDS; -- ERROR
CREATE FOREIGN TABLE ft1 (
! c1 integer NOT NULL,
! c2 text,
c3 date
) SERVER sc OPTIONS (delimiter ',', quote '"');
COMMENT ON FOREIGN TABLE ft1 IS 'ft1';
--- 264,271 ----
CREATE FOREIGN TABLE ft1 (c1 serial) SERVER sc; -- ERROR
CREATE FOREIGN TABLE ft1 () SERVER sc WITH OIDS; -- ERROR
CREATE FOREIGN TABLE ft1 (
! c1 integer OPTIONS (param1 'val1') NOT NULL,
! c2 text OPTIONS (param2 'val2', param3 'val3'),
c3 date
) SERVER sc OPTIONS (delimiter ',', quote '"');
COMMENT ON FOREIGN TABLE ft1 IS 'ft1';
*************** ALTER FOREIGN TABLE ft1 ADD COLUMN c6 in
*** 288,294 ****
ALTER FOREIGN TABLE ft1 ADD COLUMN c7 integer NOT NULL;
ALTER FOREIGN TABLE ft1 ADD COLUMN c8 integer;
ALTER FOREIGN TABLE ft1 ADD COLUMN c9 integer;
! ALTER FOREIGN TABLE ft1 ADD COLUMN c10 integer;
ALTER FOREIGN TABLE ft1 ALTER COLUMN c4 SET DEFAULT 0; -- ERROR
ALTER FOREIGN TABLE ft1 ALTER COLUMN c5 DROP DEFAULT; -- ERROR
--- 288,294 ----
ALTER FOREIGN TABLE ft1 ADD COLUMN c7 integer NOT NULL;
ALTER FOREIGN TABLE ft1 ADD COLUMN c8 integer;
ALTER FOREIGN TABLE ft1 ADD COLUMN c9 integer;
! ALTER FOREIGN TABLE ft1 ADD COLUMN c10 integer OPTIONS (p1 'v1');
ALTER FOREIGN TABLE ft1 ALTER COLUMN c4 SET DEFAULT 0; -- ERROR
ALTER FOREIGN TABLE ft1 ALTER COLUMN c5 DROP DEFAULT; -- ERROR
*************** ALTER FOREIGN TABLE ft1 ALTER COLUMN c7
*** 297,302 ****
--- 297,307 ----
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE char(10) using '0'; -- ERROR
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE char(10);
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET DATA TYPE text;
+ ALTER FOREIGN TABLE ft1 ALTER COLUMN xmin OPTIONS (ADD p1 'v1'); -- ERROR
+ ALTER FOREIGN TABLE ft1 ALTER COLUMN c7 OPTIONS (ADD p1 'v1', ADD p2 'v2'),
+ ALTER COLUMN c8 OPTIONS (ADD p1 'v1', ADD p2 'v2');
+ ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 OPTIONS (SET p2 'V2', DROP p1);
+ \dec+
-- can't change the column type if it's used elsewhere
CREATE TABLE use_ft1_column_type (x ft1);
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET DATA TYPE integer; -- ERROR
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
