Hi,
Please find the attached patch to fix the RM #1351 - Foreign table column
options in pgAdmin 4.
Added the functionality to add column level options in Foreign Table.
Thanks,
Khushboo
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/__init__.py
index d1cec86..a6d52b3 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/__init__.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/__init__.py
@@ -891,6 +891,32 @@ AND relkind != 'c'))"""
data['columns']['changed'] = self._format_columns(
data['columns']['changed'])
+ # Parse Column Options
+ for c in data['columns']['changed']:
+ old_col_options = c['attfdwoptions'] if ('attfdwoptions' in c and c['attfdwoptions']) else []
+ old_col_frmt_options = {}
+
+ for o in old_col_options:
+ col_opt = o.split("=")
+ old_col_frmt_options[col_opt[0]] = col_opt[1]
+
+ c['coloptions_updated'] = {'added': [],
+ 'changed': [],
+ 'deleted': []}
+
+ if 'coloptions' in c and len(c['coloptions']) > 0:
+ for o in c['coloptions']:
+ if o['option'] in old_col_frmt_options and o['value'] != old_col_frmt_options[o['option']]:
+ c['coloptions_updated']['changed'].append(o)
+ elif o['option'] not in old_col_frmt_options:
+ c['coloptions_updated']['added'].append(o)
+ if o['option'] in old_col_frmt_options:
+ del old_col_frmt_options[o['option']]
+
+ for o in old_col_frmt_options:
+ c['coloptions_updated']['deleted'].append({'option': o})
+
+
# Parse Privileges
if 'acl' in data and 'added' in data['acl']:
data['acl']['added'] = parse_priv_to_db(data['acl']['added'],
@@ -1020,7 +1046,7 @@ AND relkind != 'c'))"""
if 'seclabels' in data:
data.update(parse_sec_labels_from_db(data['seclabels']))
- # Get formatted Variables
+ # Get formatted Options
if 'ftoptions' in data:
data.update({'strftoptions': data['ftoptions']})
data.update(self._parse_variables_from_db(data['ftoptions']))
@@ -1054,6 +1080,11 @@ AND relkind != 'c'))"""
c['typlen'] = int(typlen[0])
c['precision'] = None
+ # Get formatted Column Options
+ if 'attfdwoptions' in c and c['attfdwoptions'] != '':
+ att_opt = self._parse_variables_from_db(c['attfdwoptions'])
+ c['coloptions'] = att_opt['ftoptions']
+
if cols and 'rows' in cols:
data['columns'] = cols['rows']
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/js/foreign_tables.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/js/foreign_tables.js
index 31ef55f..33c41cf 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/js/foreign_tables.js
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/js/foreign_tables.js
@@ -36,6 +36,36 @@ function($, _, S, pgAdmin, pgBrowser, alertify) {
});
+ // Options Model
+ var ColumnOptionsModel = pgBrowser.Node.Model.extend({
+ idAttribute: 'option',
+ defaults: {
+ option: undefined,
+ value: undefined
+ },
+ schema: [
+ {id: 'option', label:'Option', type:'text', editable: true, cellHeaderClasses: 'width_percent_30'},
+ {
+ id: 'value', label:'Value', type: 'text', editable: true, cellHeaderClasses: 'width_percent_50'
+ }
+ ],
+ validate: function() {
+ if (_.isUndefined(this.get('value')) ||
+ _.isNull(this.get('value')) ||
+ String(this.get('value')).replace(/^\s+|\s+$/g, '') == '') {
+ var msg = 'Please enter some value!';
+
+ this.errorModel.set('value', msg);
+
+ return msg;
+ } else {
+ this.errorModel.unset('value');
+ }
+
+ return null;
+ }
+ });
+
// Columns Model
var ColumnsModel = pgBrowser.Node.Model.extend({
idAttribute: 'attnum',
@@ -50,30 +80,32 @@ function($, _, S, pgAdmin, pgBrowser, alertify) {
attnum: undefined,
inheritedfrom: undefined,
inheritedid: undefined,
- attstattarget: undefined
+ attstattarget: undefined,
+ coloptions: []
},
type_options: undefined,
schema: [{
id: 'attname', label:'{{ _('Name') }}', cell: 'string', type: 'text',
- editable: 'is_editable_column', cellHeaderClasses: 'width_percent_20'
+ editable: 'is_editable_column', cellHeaderClasses: 'width_percent_40'
},{
id: 'datatype', label:'{{ _('Data Type') }}', cell: 'node-ajax-options',
control: 'node-ajax-options', type: 'text', url: 'get_types',
- editable: 'is_editable_column', cellHeaderClasses: 'width_percent_20',
+ editable: 'is_editable_column', cellHeaderClasses: 'width_percent_0',
+ group: '{{ _('Definition') }}',
transform: function(d, self){
self.model.type_options = d;
return d;
}
},{
id: 'typlen', label:'{{ _('Length') }}',
- cell: IntegerDepCell,
- type: 'text', deps: ['datatype'],
- editable: function(m) {
+ cell: 'string', group: '{{ _('Definition') }}',
+ type: 'int', deps: ['datatype'],
+ disabled: function(m) {
// We will store type from selected from combobox
if(!(_.isUndefined(m.get('inheritedid'))
|| _.isNull(m.get('inheritedid'))
|| _.isUndefined(m.get('inheritedfrom'))
- || _.isNull(m.get('inheritedfrom')))) { return false; }
+ || _.isNull(m.get('inheritedfrom')))) { return true; }
var of_type = m.get('datatype');
if(m.type_options) {
@@ -94,20 +126,20 @@ function($, _, S, pgAdmin, pgBrowser, alertify) {
}
}
});
- return m.get('is_tlength');
+ return !(m.get('is_tlength'));
}
return true;
},
cellHeaderClasses: 'width_percent_10'
},{
id: 'precision', label:'{{ _('Precision') }}',
- type: 'text', deps: ['datatype'],
- cell: IntegerDepCell,
- editable: function(m) {
+ type: 'int', deps: ['datatype'],
+ cell: 'string', group: '{{ _('Definition') }}',
+ disabled: function(m) {
if(!(_.isUndefined(m.get('inheritedid'))
|| _.isNull(m.get('inheritedid'))
|| _.isUndefined(m.get('inheritedfrom'))
- || _.isNull(m.get('inheritedfrom')))) { return false; }
+ || _.isNull(m.get('inheritedfrom')))) { return true; }
var of_type = m.get('datatype');
if(m.type_options) {
@@ -127,13 +159,13 @@ function($, _, S, pgAdmin, pgBrowser, alertify) {
}
}
});
- return m.get('is_precision');
+ return !(m.get('is_precision'));
}
return true;
}, cellHeaderClasses: 'width_percent_10'
},{
id: 'typdefault', label:'{{ _('Default') }}', type: 'text',
- cell: 'string', min_version: 90300,
+ cell: 'string', min_version: 90300, group: '{{ _('Definition') }}',
placeholder: "Enter an expression or a value.",
cellHeaderClasses: 'width_percent_10',
editable: function(m) {
@@ -149,16 +181,17 @@ function($, _, S, pgAdmin, pgBrowser, alertify) {
},{
id: 'attnotnull', label:'{{ _('Not Null') }}',
cell: 'boolean',type: 'switch', editable: 'is_editable_column',
- cellHeaderClasses: 'width_percent_10'
+ cellHeaderClasses: 'width_percent_10', group: '{{ _('Definition') }}'
},{
id: 'attstattarget', label:'{{ _('Statistics') }}', min_version: 90200,
- cell: 'integer', type: 'int', editable: function(m) {
- if (_.isUndefined(m.isNew) || m.isNew()) { return false; }
- if (this.get('node_info').server.version < 90200){
+ cell: 'integer', type: 'int', group: '{{ _('Definition') }}',
+ editable: function(m) {
+ if (_.isUndefined(m.isNew) || m.isNew()) { return false; }
+ if (this.get('node_info').server.version < 90200){
return false;
- }
- return (_.isUndefined(m.get('inheritedid')) || _.isNull(m.get('inheritedid'))
- || _.isUndefined(m.get('inheritedfrom')) || _.isNull(m.get('inheritedfrom'))) ? true : false
+ }
+ return (_.isUndefined(m.get('inheritedid')) || _.isNull(m.get('inheritedid'))
+ || _.isUndefined(m.get('inheritedfrom')) || _.isNull(m.get('inheritedfrom'))) ? true : false
}, cellHeaderClasses: 'width_percent_10'
},{
id: 'collname', label:'{{ _('Collation') }}', cell: 'node-ajax-options',
@@ -168,14 +201,20 @@ function($, _, S, pgAdmin, pgBrowser, alertify) {
return (_.isUndefined(m.get('inheritedid')) || _.isNull(m.get('inheritedid'))
|| _.isUndefined(m.get('inheritedfrom')) || _.isNull(m.get('inheritedfrom'))) ? true : false
},
- cellHeaderClasses: 'width_percent_20'
+ cellHeaderClasses: 'width_percent_20', group: '{{ _('Definition') }}'
},{
id: 'attnum', cell: 'string',type: 'text', visible: false
},{
id: 'inheritedfrom', label:'{{ _('Inherited From') }}', cell: 'string',
type: 'text', visible: false, mode: ['properties', 'edit'],
cellHeaderClasses: 'width_percent_10'
- }],
+ },{
+ id: 'coloptions', label:'{{ _('Options') }}', cell: 'string',
+ type: 'collection', group: 'Options', mode: ['edit', 'create'],
+ model: ColumnOptionsModel, canAdd: true, canDelete: true, canEdit: false,
+ control: Backform.UniqueColCollectionControl, uniqueCol : ['option'],
+ min_version: 90200
+ }],
validate: function() {
var err = {},
errmsg;
@@ -330,14 +369,14 @@ function($, _, S, pgAdmin, pgBrowser, alertify) {
if (typeof(inherits) == "string"){ inherits = JSON.parse(inherits); }
// Remove Columns if inherit option is deselected from the combobox
- if(_.size(JSON.parse(value)) < _.size(inherits)) {
+ if(_.size(value) < _.size(inherits)) {
var dif = _.difference(inherits, JSON.parse(value));
var rmv_columns = columns.where({inheritedid: parseInt(dif[0])});
columns.remove(rmv_columns);
}
else
{
- _.each(JSON.parse(value), function(i) {
+ _.each(value, function(i) {
// Fetch Columns from server
var fnd_columns = columns.where({inheritedid: parseInt(i)});
if (fnd_columns && fnd_columns.length <= 0) {
@@ -592,8 +631,8 @@ function($, _, S, pgAdmin, pgBrowser, alertify) {
id: 'ftsrvname', label:'{{ _('Foreign server') }}', cell: 'string', control: 'node-ajax-options',
type: 'text', group: 'Definition', url: 'get_foreign_servers', disabled: function(m) { return !m.isNew(); }
},{
- id: 'inherits', label:'{{ _('Inherits') }}', cell: 'string', group: 'Definition',
- type: 'list', min_version: 90500, control: 'node-ajax-options-multiple',
+ id: 'inherits', label:'{{ _('Inherits') }}', group: 'Definition',
+ type: 'array', min_version: 90500, control: 'node-ajax-options-multiple',
url: 'get_tables', select2: {multiple: true},
'cache_level': 'database',
transform: function(d, self){
@@ -612,11 +651,15 @@ function($, _, S, pgAdmin, pgBrowser, alertify) {
},{
id: 'columns', label:'{{ _('Columns') }}', cell: 'string',
type: 'collection', group: 'Columns', visible: false, mode: ['edit', 'create'],
- model: ColumnsModel, canAdd: true, canDelete: true, canEdit: false,
- columns: ['attname', 'datatype', 'typlen', 'precision', 'typdefault', 'attnotnull', 'attstattarget', 'collname', 'inheritedfrom'],
+ model: ColumnsModel, canAdd: true, canDelete: true, canEdit: true,
+ columns: ['attname', 'datatype', 'inheritedfrom'],
canDeleteRow: function(m) {
return (_.isUndefined(m.get('inheritedid')) || _.isNull(m.get('inheritedid'))
|| _.isUndefined(m.get('inheritedfrom')) || _.isNull(m.get('inheritedfrom'))) ? true : false
+ },
+ canEditRow: function(m) {
+ return (_.isUndefined(m.get('inheritedid')) || _.isNull(m.get('inheritedid'))
+ || _.isUndefined(m.get('inheritedfrom')) || _.isNull(m.get('inheritedfrom'))) ? true : false
}
},
{
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/create.sql
index 5582eb8..139cc8f 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/create.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/create.sql
@@ -4,7 +4,10 @@
CREATE FOREIGN TABLE {{ conn|qtIdent(data.basensp, data.name) }}(
{% if data.columns %}
{% for c in data.columns %}
- {{conn|qtIdent(c.attname)}} {{conn|qtTypeIdent(c.datatype) }}{% if c.typlen %}({{c.typlen}}{% if c.precision %}, {{c.precision}}{% endif %}){% endif %}{% if c.isArrayType %}[]{% endif %}{% if c.attnotnull %}
+ {{conn|qtIdent(c.attname)}} {{conn|qtTypeIdent(c.datatype) }}{% if c.typlen %}({{c.typlen}}{% if c.precision %}, {{c.precision}}{% endif %}){% endif %}{% if c.isArrayType %}[]{% endif %}{% if c.coloptions %}
+{% for o in c.coloptions %}{% if o.option and o.value %}
+{% if loop.first %} OPTIONS ({% endif %}{% if not loop.first %}, {% endif %}{{o.option}} {{o.value|qtLiteral}}{% if loop.last %}){% endif %}{% endif %}
+{% endfor %}{% endif %}{% if c.attnotnull %}
NOT NULL{% else %} NULL{% endif %}{% if c.typdefault %}
DEFAULT {{c.typdefault}}{% endif %}{% if c.collname %}
COLLATE {{c.collname}}{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/get_columns.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/get_columns.sql
index 4c03ad7..4270058 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/get_columns.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/get_columns.sql
@@ -1,5 +1,5 @@
SELECT
- attname, attndims, atttypmod, format_type(t.oid,NULL) AS datatype,
+ attname, attndims, atttypmod, attoptions, attfdwoptions, format_type(t.oid,NULL) AS datatype,
attnotnull, attstattarget, attnum, format_type(t.oid, att.atttypmod) AS fulltype,
CASE WHEN length(cn.nspname) > 0 AND length(cl.collname) > 0 THEN
concat(cn.nspname, '."', cl.collname,'"') ELSE '' END AS collname,
@@ -7,12 +7,21 @@ SELECT
pg_catalog.pg_get_expr(def.adbin, def.adrelid) AS typdefault,
(
attname || ' ' || format_type(t.oid, att.atttypmod) || ' ' ||
- (CASE WHEN attnotnull='true'
- THEN 'NOT NULL' ELSE 'NULL'
- END) || ' ' ||
- (CASE WHEN pg_catalog.pg_get_expr(def.adbin, def.adrelid)<>''
- THEN 'DEFAULT ' || pg_catalog.pg_get_expr(def.adbin, def.adrelid)
- ELSE '' END)
+ (
+ CASE WHEN array_length(attfdwoptions, 1)>0
+ THEN concat('OPTIONS (', array_to_string(attfdwoptions, ', '), ')') ELSE ''
+ END
+ ) || ' ' ||
+ (
+ CASE WHEN attnotnull='true'
+ THEN 'NOT NULL' ELSE 'NULL'
+ END
+ ) || ' ' ||
+ (
+ CASE WHEN pg_catalog.pg_get_expr(def.adbin, def.adrelid)<>''
+ THEN 'DEFAULT ' || pg_catalog.pg_get_expr(def.adbin, def.adrelid)
+ ELSE '' END
+ )
) as strcolumn
FROM
pg_attribute att
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/update.sql
index d5b62d4..078cea6 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/update.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/update.sql
@@ -24,6 +24,10 @@ ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
ADD COLUMN {{conn|qtIdent(c.attname)}} {{ conn|qtTypeIdent(c.datatype) }}{% if c.typlen %}({{c.typlen}}{% if c.precision %}, {{c.precision}}{% endif %}){% endif %}{% if c.isArrayType %}[]{% endif %}
+{% if c.coloptions %}
+{% for o in c.coloptions %}{% if o.option and o.value %}
+{% if loop.first %} OPTIONS ({% endif %}{% if not loop.first %}, {% endif %}{{o.option}} {{o.value|qtLiteral}}{% if loop.last %}){% endif %}{% endif %}
+{% endfor %}{% endif %}
{% if c.attnotnull %} NOT NULL{% else %} NULL{% endif %}
{% if c.typdefault %} DEFAULT {{c.typdefault}}{% endif %}
{% if c.collname %} COLLATE {{c.collname}}{% endif %};
@@ -57,6 +61,27 @@ ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
ALTER COLUMN {{conn|qtIdent(col_name)}} SET STATISTICS {% if c.attstattarget %}{{c.attstattarget}}{% else %}-1{% endif %};
{% endif %}
+{% if c.coloptions_updated %}
+
+{% for o in c.coloptions_updated.deleted %}
+{% if o.option %}
+{% if loop.first %}ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ ALTER COLUMN {{conn|qtIdent(col_name)}} OPTIONS (DROP {% endif %}{% if not loop.first %}, {% endif %}{{o.option}}{% if loop.last %}){% endif %};
+{% endif %}
+{% endfor %}
+{% for o in c.coloptions_updated.added %}
+{% if o.option and o.value %}
+{% if loop.first %}ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ ALTER COLUMN {{conn|qtIdent(col_name)}} OPTIONS (ADD {% endif %}{% if not loop.first %}, {% endif %}{{o.option}} {{o.value|qtLiteral}}{% if loop.last %});{% endif %}
+{% endif %}
+{% endfor %}
+{% for o in c.coloptions_updated.changed %}
+{% if o.option and o.value %}
+{% if loop.first %}ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ ALTER COLUMN {{conn|qtIdent(col_name)}} OPTIONS (SET {% endif %}{% if not loop.first %}, {% endif %}{{o.option}} {{o.value|qtLiteral}}{% if loop.last %});{% endif %}
+{% endif %}
+{% endfor %}
+{% endif -%}
{% endfor %}
{% endif -%}
{% if data.constraints %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/create.sql
index 6902740..5e1d4e3 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/create.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/create.sql
@@ -7,7 +7,10 @@ CREATE FOREIGN TABLE {{ conn|qtIdent(data.basensp, data.name) }}(
{% for c in data.columns %}
{% if (not c.inheritedfrom or c.inheritedfrom =='' or c.inheritedfrom == None or c.inheritedfrom == 'None' ) %}
{% if is_columns.append('1') %}{% endif %}
- {{conn|qtIdent(c.attname)}} {{conn|qtTypeIdent(c.datatype) }}{% if c.typlen %}({{c.typlen}}{% if c.precision %}, {{c.precision}}{% endif %}){% endif %}{% if c.isArrayType %}[]{% endif %}
+ {{conn|qtIdent(c.attname)}} {{conn|qtTypeIdent(c.datatype) }}{% if c.typlen %}({{c.typlen}}{% if c.precision %}, {{c.precision}}{% endif %}){% endif %}{% if c.isArrayType %}[]{% endif %}{% if c.coloptions %}
+{% for o in c.coloptions %}{% if o.option and o.value %}
+{% if loop.first %} OPTIONS ({% endif %}{% if not loop.first %}, {% endif %}{{o.option}} {{o.value|qtLiteral}}{% if loop.last %}){% endif %}{% endif %}
+{% endfor %}{% endif %}
{% if c.attnotnull %} NOT NULL{% else %} NULL{% endif %}
{% if c.typdefault %} DEFAULT {{c.typdefault}}{% endif %}
{% if c.collname %} COLLATE {{c.collname}}{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_columns.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_columns.sql
index 18f70e8..64d6d13 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_columns.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_columns.sql
@@ -13,7 +13,7 @@ WITH INH_TABLES AS
GROUP BY at.attname, ph.inhparent, ph.inhseqno, inheritedfrom
ORDER BY at.attname, ph.inhparent, ph.inhseqno, inheritedfrom
)
-SELECT INH.inheritedfrom, INH.inheritedid,
+SELECT INH.inheritedfrom, INH.inheritedid, att.attoptions, attfdwoptions,
att.attname, att.attndims, att.atttypmod, format_type(t.oid,NULL) AS datatype,
att.attnotnull, att.attstattarget, att.attnum, format_type(t.oid, att.atttypmod) AS fulltype,
CASE WHEN length(cn.nspname) > 0 AND length(cl.collname) > 0 THEN
@@ -22,12 +22,21 @@ SELECT INH.inheritedfrom, INH.inheritedid,
pg_catalog.pg_get_expr(def.adbin, def.adrelid) AS typdefault,
(
att.attname || ' ' || format_type(t.oid, att.atttypmod) || ' ' ||
- (CASE WHEN attnotnull='true'
- THEN 'NOT NULL' ELSE 'NULL'
- END) || ' ' ||
- (CASE WHEN pg_catalog.pg_get_expr(def.adbin, def.adrelid)<>''
- THEN 'DEFAULT ' || pg_catalog.pg_get_expr(def.adbin, def.adrelid)
- ELSE '' END)
+ (
+ CASE WHEN array_length(attfdwoptions, 1)>0
+ THEN concat('OPTIONS (', array_to_string(attfdwoptions, ', '), ')') ELSE ''
+ END
+ ) || ' ' ||
+ (
+ CASE WHEN attnotnull='true'
+ THEN 'NOT NULL' ELSE 'NULL'
+ END
+ ) || ' ' ||
+ (
+ CASE WHEN pg_catalog.pg_get_expr(def.adbin, def.adrelid)<>''
+ THEN 'DEFAULT ' || pg_catalog.pg_get_expr(def.adbin, def.adrelid)
+ ELSE '' END
+ )
) as strcolumn,
(SELECT COUNT(1) from pg_type t2 WHERE t2.typname=t.typname) > 1 AS isdup
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/update.sql
index 4a49622..1f3d507 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/update.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/update.sql
@@ -24,6 +24,10 @@ ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
ADD COLUMN {{conn|qtIdent(c.attname)}} {{ conn|qtTypeIdent(c.datatype) }}{% if c.typlen %}({{c.typlen}}{% if c.precision %}, {{c.precision}}{% endif %}){% endif %}{% if c.isArrayType %}[]{% endif %}
+{% if c.coloptions %}
+{% for o in c.coloptions %}{% if o.option and o.value %}
+{% if loop.first %} OPTIONS ({% endif %}{% if not loop.first %}, {% endif %}{{o.option}} {{o.value|qtLiteral}}{% if loop.last %}){% endif %}{% endif %}
+{% endfor %}{% endif %}
{% if c.attnotnull %} NOT NULL{% else %} NULL{% endif %}
{% if c.typdefault %} DEFAULT {{c.typdefault}}{% endif %}
{% if c.collname %} COLLATE {{c.collname}}{% endif %};
@@ -58,6 +62,27 @@ ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
ALTER COLUMN {{conn|qtIdent(col_name)}} SET STATISTICS {% if c.attstattarget %}{{c.attstattarget}}{% else %}-1{% endif %};
{% endif %}
+{% if c.coloptions_updated %}
+
+{% for o in c.coloptions_updated.deleted %}
+{% if o.option %}
+{% if loop.first %}ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ ALTER COLUMN {{conn|qtIdent(col_name)}} OPTIONS (DROP {% endif %}{% if not loop.first %}, {% endif %}{{o.option}}{% if loop.last %}){% endif %};
+{% endif %}
+{% endfor %}
+{% for o in c.coloptions_updated.added %}
+{% if o.option and o.value %}
+{% if loop.first %}ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ ALTER COLUMN {{conn|qtIdent(col_name)}} OPTIONS (ADD {% endif %}{% if not loop.first %}, {% endif %}{{o.option}} {{o.value|qtLiteral}}{% if loop.last %});{% endif %}
+{% endif %}
+{% endfor %}
+{% for o in c.coloptions_updated.changed %}
+{% if o.option and o.value %}
+{% if loop.first %}ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ ALTER COLUMN {{conn|qtIdent(col_name)}} OPTIONS (SET {% endif %}{% if not loop.first %}, {% endif %}{{o.option}} {{o.value|qtLiteral}}{% if loop.last %});{% endif %}
+{% endif %}
+{% endfor %}
+{% endif -%}
{% endfor %}
{% endif %}
{% if data.inherits and data.inherits|length > 0%}
--
Sent via pgadmin-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers
