Hi,
Please find attached patch for the Foreign Table Module.
The patch will be modified after Types module implementation as we need to
populate Base Type and some Type related validations from the Types module.
Please review it and let me know the feedback.
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
new file mode 100644
index 0000000..8d6727b
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/__init__.py
@@ -0,0 +1,1002 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2016, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+
+"""The Foreign Table Module."""
+
+import json
+from flask import render_template, make_response, request, jsonify, \
+ current_app
+from flask.ext.babel import gettext
+from pgadmin.utils.ajax import make_json_response, \
+ make_response as ajax_response, internal_server_error
+from pgadmin.browser.utils import PGChildNodeView
+from pgadmin.browser.collection import CollectionNodeModule
+import pgadmin.browser.server_groups.servers.databases.schemas as schemas
+from pgadmin.utils.ajax import precondition_required
+from pgadmin.utils.driver import get_driver
+from config import PG_DEFAULT_DRIVER
+from functools import wraps
+import sys
+import traceback
+
+
+class ForeignTableModule(CollectionNodeModule):
+ """
+ class ForeignTableModule(CollectionNodeModule):
+
+ This class represents The Foreign Table Module.
+
+ Methods:
+ -------
+ * __init__(*args, **kwargs)
+ - Initialize the Foreign Table Module.
+
+ * get_nodes(gid, sid, did, scid)
+ - Generate the Foreign Table collection node.
+
+ * node_inode():
+ - Override this property to make the Foreign Table node as leaf node.
+
+ * script_load()
+ - Load the module script for Foreign Table, when schema node is
+ initialized.
+ """
+ NODE_TYPE = 'foreign-table'
+ COLLECTION_LABEL = gettext("Foreign Tables")
+
+ def __init__(self, *args, **kwargs):
+ super(ForeignTableModule, self).__init__(*args, **kwargs)
+ self.min_ver = None
+ self.max_ver = None
+
+ def get_nodes(self, gid, sid, did, scid):
+ """
+ Generate the Foreign Table collection node.
+ """
+ yield self.generate_browser_collection_node(scid)
+
+ @property
+ def node_inode(self):
+ """
+ Override this property to make the node as leaf node.
+ """
+ return False
+
+ @property
+ def script_load(self):
+ """
+ Load the module script for foreign table, when the
+ schema node is initialized.
+ """
+ return schemas.SchemaModule.NODE_TYPE
+
+
+blueprint = ForeignTableModule(__name__)
+
+
+class ForeignTableView(PGChildNodeView):
+ """
+ class ForeignTableView(PGChildNodeView)
+
+ This class inherits PGChildNodeView to get the different routes for
+ the module.
+
+ The class is responsible to Create, Read, Update and Delete operations for
+ the Foreign Table.
+
+ Methods:
+ -------
+ * validate_request(f):
+ - Works as a decorator.
+ Validating request on the request of create, update and modified SQL.
+
+ * module_js():
+ - Overrides this property to define javascript for Foreign Table node.
+
+ * check_precondition(f):
+ - Works as a decorator.
+ - Checks database connection status.
+ - Attach connection object and template path.
+
+ * list(gid, sid, did, scid):
+ - List the Foreign Table.
+
+ * nodes(gid, sid, did, scid):
+ - Returns all the Foreign Table to generate Nodes in the browser.
+
+ * properties(gid, sid, did, scid, foid):
+ - Returns the Foreign Table properties.
+
+ * get_schemas(gid, sid, did, scid, foid=None):
+ - Returns Schemas for the particular database.
+
+ * get_collations(gid, sid, did, scid, foid=None):
+ - Returns Collations.
+
+ * get_types(gid, sid, did, scid, foid=None):
+ - Returns Data Types.
+
+ * get_foreign_servers(gid, sid, did, scid, foid=None):
+ - Returns the Foreign Servers.
+
+ * get_tables(gid, sid, did, scid, foid=None):
+ - Returns the Foreign Tables as well as Plain Tables.
+
+ * get_columns(gid, sid, did, scid, foid=None):
+ - Returns the Table Columns.
+
+ * create(gid, sid, did, scid):
+ - Creates a new Foreign Table object.
+
+ * update(gid, sid, did, scid, foid):
+ - Updates the Foreign Table object.
+
+ * delete(gid, sid, did, scid, foid):
+ - Drops the Foreign Table object.
+
+ * sql(gid, sid, did, scid, foid):
+ - Returns the SQL for the Foreign Table object.
+
+ * msql(gid, sid, did, scid, foid=None):
+ - Returns the modified SQL.
+
+ * get_sql(gid, sid, data, scid, foid=None):
+ - Generates the SQL statements to create/update the Foreign Table object.
+
+ * dependents(gid, sid, did, scid, foid):
+ - Returns the dependents for the Foreign Table object.
+
+ * dependencies(gid, sid, did, scid, foid):
+ - Returns the dependencies for the Foreign Table object.
+
+ * get_properties(gid, sid, did, scid, foid, inherits=False):
+ - Returns the Foreign Table properties which will be used in
+ properties, sql and get_sql functions.
+ """
+
+ node_type = blueprint.node_type
+
+ parent_ids = [
+ {'type': 'int', 'id': 'gid'},
+ {'type': 'int', 'id': 'sid'},
+ {'type': 'int', 'id': 'did'},
+ {'type': 'int', 'id': 'scid'}
+ ]
+ ids = [
+ {'type': 'int', 'id': 'foid'}
+ ]
+
+ operations = dict({
+ 'obj': [
+ {'get': 'properties', 'delete': 'delete', 'put': 'update'},
+ {'get': 'list', 'post': 'create'}
+ ],
+ 'delete': [{'delete': 'delete'}],
+ 'children': [{'get': 'children'}],
+ 'nodes': [{'get': 'node'}, {'get': 'nodes'}],
+ 'sql': [{'get': 'sql'}],
+ 'msql': [{'get': 'msql'}, {'get': 'msql'}],
+ 'stats': [{'get': 'statistics'}],
+ 'dependency': [{'get': 'dependencies'}],
+ 'dependent': [{'get': 'dependents'}],
+ 'module.js': [{}, {}, {'get': 'module_js'}],
+ 'get_schemas': [{'get': 'get_schemas'}, {'get': 'get_schemas'}],
+ 'get_collations': [
+ {'get': 'get_collations'},
+ {'get': 'get_collations'}
+ ],
+ 'get_types': [{'get': 'get_types'}, {'get': 'get_types'}],
+ 'get_foreign_servers': [{'get': 'get_foreign_servers'},
+ {'get': 'get_foreign_servers'}],
+ 'get_tables': [{'get': 'get_tables'}, {'get': 'get_tables'}],
+ 'get_columns': [{'get': 'get_columns'}, {'get': 'get_columns'}]
+ })
+
+ def validate_request(f):
+ """
+ Works as a decorator.
+ Validating request on the request of create, update and modified SQL.
+
+ Required Args:
+ name: Name of the Foreign Table
+ ftsrvname: Foreign Server Name
+
+ Above both the arguments will not be validated in the update action.
+ """
+ @wraps(f)
+ def wrap(self, **kwargs):
+
+ data = {}
+ if request.data:
+ req = json.loads(request.data)
+ else:
+ req = request.args or request.form
+
+ if 'foid' not in kwargs:
+ required_args = [
+ 'name',
+ 'ftsrvname'
+ ]
+
+ for arg in required_args:
+ if arg not in req or req[arg] == '':
+ return make_json_response(
+ status=410,
+ success=0,
+ errormsg=gettext(
+ "Couldn't find the required parameter \
+ (%s)." % arg
+ )
+ )
+
+ try:
+ list_params = []
+ if request.method == 'GET':
+ list_params = ['constraints', 'columns', 'ftoptions',
+ 'seclabels', 'inherits']
+ else:
+ list_params = ['inherits']
+
+ for key in req:
+ if key in list_params and req[key] != '' \
+ and req[key] is not None:
+ # Coverts string into python list as expected.
+ data[key] = json.loads(req[key])
+
+ if key == 'inherits':
+ # Convert Table ids from unicode/string to int
+ # and make tuple for 'IN' query.
+ inherits = tuple([int(x) for x in data[key]])
+
+ if len(inherits) == 1:
+ # Python tupple has , after the first param
+ # in case of single parameter.
+ # So, we need to make it tuple explicitly.
+ inherits = "(" + str(inherits[0]) + ")"
+ if inherits:
+ # Fetch Table Names from their respective Ids,
+ # as we need Table names to generate the SQL.
+ SQL = render_template(
+ "/".join([self.template_path,
+ 'get_tables.sql']),
+ attrelid=inherits)
+ status, res = self.conn.execute_dict(SQL)
+
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ if 'inherits' in res['rows'][0]:
+ data[key] = res['rows'][0]['inherits']
+ else:
+ data[key] = []
+
+ elif key == 'typnotnull':
+ data[key] = True if (req[key] == 'true' or req[key]
+ is True) else False if\
+ (req[key] == 'false' or req[key]) is False else ''
+ else:
+ data[key] = req[key]
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ self.request = data
+ return f(self, **kwargs)
+
+ return wrap
+
+ def module_js(self):
+ """
+ Load JS file (foreign_tables.js) for this module.
+ """
+ return make_response(
+ render_template(
+ "foreign_tables/js/foreign_tables.js",
+ _=gettext
+ ),
+ 200, {'Content-Type': 'application/x-javascript'}
+ )
+
+ def check_precondition(f):
+ """
+ Works as a decorator.
+ Checks the database connection status.
+ Attaches the connection object and template path to the class object.
+ """
+ @wraps(f)
+ def wrap(*args, **kwargs):
+ self = args[0]
+ driver = get_driver(PG_DEFAULT_DRIVER)
+ self.manager = driver.connection_manager(kwargs['sid'])
+
+ # Get database connection
+ self.conn = self.manager.connection(did=kwargs['did'])
+
+ self.qtIdent = driver.qtIdent
+
+ if not self.conn.connected():
+ return precondition_required(
+ gettext(
+ "Connection to the server has been lost!"
+ )
+ )
+
+ ver = self.manager.version
+ server_type = self.manager.server_type
+
+ # Set template path for sql scripts depending
+ # on the server version.
+
+ if ver >= 90500:
+ self.template_path = 'foreign_tables/sql/9.5_plus'
+ elif ver >= 90300:
+ self.template_path = 'foreign_tables/sql/9.3_plus'
+ else:
+ self.template_path = 'foreign_tables/sql/9.1_plus'
+
+ return f(*args, **kwargs)
+
+ return wrap
+
+ @check_precondition
+ def list(self, gid, sid, did, scid):
+ """
+ List all the Foreign Tables.
+
+ Args:
+ gid: Server Group Id
+ sid: Server Id
+ did: Database Id
+ scid: Schema Id
+ """
+ SQL = render_template("/".join([self.template_path, 'node.sql']),
+ scid=scid)
+ status, res = self.conn.execute_dict(SQL)
+
+ if not status:
+ return internal_server_error(errormsg=res)
+ return ajax_response(
+ response=res['rows'],
+ status=200
+ )
+
+ @check_precondition
+ def nodes(self, gid, sid, did, scid):
+ """
+ Returns the Foreign Tables to generate the Nodes.
+
+ Args:
+ gid: Server Group Id
+ sid: Server Id
+ did: Database Id
+ scid: Schema Id
+ """
+
+ res = []
+ SQL = render_template("/".join([self.template_path,
+ 'node.sql']), scid=scid)
+ status, rset = self.conn.execute_2darray(SQL)
+
+ if not status:
+ return internal_server_error(errormsg=rset)
+
+ for row in rset['rows']:
+ res.append(
+ self.blueprint.generate_browser_node(
+ row['oid'],
+ scid,
+ row['name'],
+ icon="icon-foreign-table"
+ ))
+
+ return make_json_response(
+ data=res,
+ status=200
+ )
+
+ @check_precondition
+ def properties(self, gid, sid, did, scid, foid):
+ """
+ Returns the Foreign Table properties.
+
+ Args:
+ gid: Server Group Id
+ sid: Server Id
+ did: Database Id
+ scid: Schema Id
+ foid: Foreign Table Id
+ """
+ data = self.get_properties(gid, sid, did, scid, foid)
+
+ return ajax_response(
+ response=data,
+ status=200
+ )
+
+ @check_precondition
+ def get_schemas(self, gid, sid, did, scid, foid=None):
+ """
+ Returns Schemas for the particular database.
+
+ Args:
+ gid: Server Group Id
+ sid: Server Id
+ did: Database Id
+ scid: Schema Id
+ foid: Foreign Table Id
+ """
+
+ res = [{'label': '', 'value': ''}]
+ try:
+ SQL = render_template("/".join([self.template_path,
+ 'get_schemas.sql']), scid=scid)
+ status, rset = self.conn.execute_2darray(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ for row in rset['rows']:
+ res.append(
+ {'label': row['nspname'], 'value': row['nspname']}
+ )
+
+ return make_json_response(
+ data=res,
+ status=200
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def get_collations(self, gid, sid, did, scid, foid=None):
+ """
+ Returns the Collations.
+
+ Args:
+ gid: Server Group Id
+ sid: Server Id
+ did: Database Id
+ scid: Schema Id
+ foid: Foreign Table Id
+ """
+
+ res = [{'label': '', 'value': ''}]
+ try:
+ SQL = render_template("/".join([self.template_path,
+ 'get_collations.sql']))
+ status, rset = self.conn.execute_2darray(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ for row in rset['rows']:
+ res.append(
+ {'label': row['copy_collation'],
+ 'value': row['copy_collation']}
+ )
+
+ return make_json_response(
+ data=res,
+ status=200
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def get_types(self, gid, sid, did, scid, foid=None):
+ """
+ Returns the Types.
+
+ Args:
+ gid: Server Group Id
+ sid: Server Id
+ did: Database Id
+ scid: Schema Id
+ foid: Foreign Table Id
+ """
+ # TODO: This function should be removed once Types module
+ # will be completed.
+
+ res = [{'label': '', 'value': ''}]
+ try:
+ SQL = render_template("/".join([self.template_path,
+ 'get_types.sql']))
+ status, rset = self.conn.execute_2darray(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ for row in rset['rows']:
+ res.append(
+ {'label': row['typname'], 'value': row['typname']}
+ )
+ return make_json_response(
+ data=res,
+ status=200
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def get_foreign_servers(self, gid, sid, did, scid, foid=None):
+ """
+ Returns the Foreign Servers.
+
+ Args:
+ gid: Server Group Id
+ sid: Server Id
+ did: Database Id
+ scid: Schema Id
+ foid: Foreign Table Id
+ """
+ res = [{'label': '', 'value': ''}]
+ try:
+ SQL = render_template("/".join([self.template_path,
+ 'get_foreign_servers.sql']))
+ status, rset = self.conn.execute_2darray(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ for row in rset['rows']:
+ res.append(
+ {'label': row['srvname'], 'value': row['srvname']}
+ )
+ return make_json_response(
+ data=res,
+ status=200
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def get_tables(self, gid, sid, did, scid, foid=None):
+ """
+ Returns the Foreign Tables as well as Plain Tables.
+
+ Args:
+ gid: Server Group Id
+ sid: Server Id
+ did: Database Id
+ scid: Schema Id
+ foid: Foreign Table Id
+ """
+ res = []
+ try:
+ SQL = render_template("/".join([self.template_path,
+ 'get_tables.sql']))
+ status, rset = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ return make_json_response(
+ data=rset['rows'],
+ status=200
+ )
+
+ except:
+ exc_type, exc_value, exc_traceback = sys.exc_info()
+ current_app.logger.error(
+ traceback.print_exception(exc_type,
+ exc_value, exc_traceback, limit=2))
+
+ return internal_server_error(errormsg=str(exc_value))
+
+ @check_precondition
+ def get_columns(self, gid, sid, did, scid, foid=None):
+ """
+ Returns the Table Columns.
+
+ Args:
+ gid: Server Group Id
+ sid: Server Id
+ did: Database Id
+ scid: Schema Id
+ foid: Foreign Table Id
+ attrelid: Table oid
+
+ Returns:
+ JSON Array with below parameters.
+ attname: Column Name
+ datatype: Column Data Type
+ inherited_from: Parent Table from which the related column
+ is inheritted.
+ """
+ res = []
+ data = request.args if request.args else None
+ try:
+ if data and 'attrelid' in data:
+ SQL = render_template("/".join([self.template_path,
+ 'get_table_columns.sql']),
+ attrelid=data['attrelid'])
+ status, res = self.conn.execute_dict(SQL)
+
+ if not status:
+ return internal_server_error(errormsg=res)
+ return make_json_response(
+ data=res['rows'],
+ status=200
+ )
+ except:
+ exc_type, exc_value, exc_traceback = sys.exc_info()
+ current_app.logger.error(traceback.print_exception(
+ exc_type,
+ exc_value,
+ exc_traceback,
+ limit=2
+ )
+ )
+
+ return internal_server_error(errormsg=str(exc_value))
+
+ @check_precondition
+ @validate_request
+ def create(self, gid, sid, did, scid):
+ """
+ Creates a new Foreign Table object.
+
+ Args:
+ gid: Server Group Id
+ sid: Server Id
+ did: Database Id
+ scid: Schema Id
+ foid: Foreign Table Id
+ name: Foreign Table Name
+ basensp: Schema Name
+ ftsrvname: Foreign Server Name
+
+ Returns:
+ Foreign Table object in json format.
+ """
+ try:
+ # Get SQL to create Foreign Table
+ status, SQL = self.get_sql(gid, sid, did, scid, self.request)
+ if not status:
+ return internal_server_error(errormsg=SQL)
+
+ status, res = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ # Need oid to add object in the tree at browser.
+ basensp = self.request['basensp'] if ('basensp' in self.request) \
+ else None
+ SQL = render_template("/".join([self.template_path,
+ 'get_oid.sql']),
+ basensp=basensp,
+ name=self.request['name'],
+ scid=scid)
+ status, res = self.conn.execute_2darray(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ foid, scid = res['rows'][0]
+
+ return jsonify(
+ node=self.blueprint.generate_browser_node(
+ foid,
+ scid,
+ self.request['name'],
+ icon="icon-foreign-table"
+ )
+ )
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def delete(self, gid, sid, did, scid, foid):
+ """
+ Drops the Foreign Table.
+
+ Args:
+ gid: Server Group Id
+ sid: Server Id
+ did: Database Id
+ scid: Schema Id
+ foid: Foreign Table Id
+ """
+ if self.cmd == 'delete':
+ # This is a cascade operation
+ cascade = True
+ else:
+ cascade = False
+
+ try:
+ # Fetch Name and Schema Name to delete the foreign table.
+ SQL = render_template("/".join([self.template_path,
+ 'delete.sql']), scid=scid, foid=foid)
+ status, res = self.conn.execute_2darray(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ name, basensp = res['rows'][0]
+
+ SQL = render_template("/".join([self.template_path,
+ 'delete.sql']),
+ name=name,
+ basensp=basensp,
+ cascade=cascade)
+ status, res = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ return make_json_response(
+ success=1,
+ info=gettext("Foreign Table dropped"),
+ data={
+ 'id': foid,
+ 'scid': scid,
+ 'sid': sid,
+ 'gid': gid,
+ 'did': did
+ }
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ @validate_request
+ def update(self, gid, sid, did, scid, foid):
+ """
+ Updates the Foreign Table.
+
+ Args:
+ gid: Server Group Id
+ sid: Server Id
+ did: Database Id
+ scid: Schema Id
+ foid: Foreign Table Id
+ """
+ status, SQL = self.get_sql(gid, sid, did, scid, self.request, foid)
+
+ if not status:
+ return internal_server_error(errormsg=SQL)
+
+ try:
+ if SQL and SQL.strip('\n') and SQL.strip(' '):
+ status, res = self.conn.execute_scalar(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ return make_json_response(
+ success=1,
+ info="Foreign Table updated",
+ data={
+ 'id': foid,
+ 'scid': scid,
+ 'sid': sid,
+ 'gid': gid,
+ 'did': did
+ }
+ )
+ else:
+ return make_json_response(
+ success=1,
+ info="Nothing to update",
+ data={
+ 'id': foid,
+ 'scid': scid,
+ 'sid': sid,
+ 'gid': gid,
+ 'did': did
+ }
+ )
+
+ except Exception as e:
+ return internal_server_error(errormsg=str(e))
+
+ @check_precondition
+ def sql(self, gid, sid, did, scid, foid=None):
+ """
+ Returns the SQL for the Foreign Table object.
+
+ Args:
+ gid: Server Group Id
+ sid: Server Id
+ did: Database Id
+ scid: Schema Id
+ foid: Foreign Table Id
+ """
+ data = self.get_properties(gid, sid, did, scid, foid, inherits=True)
+
+ col_data = []
+ for c in data['columns']:
+ if (not 'inheritedfrom' in c) or (c['inheritedfrom'] is None):
+ col_data.append(c)
+
+ data['columns'] = col_data
+
+ SQL = render_template("/".join([self.template_path,
+ 'create.sql']), data=data)
+
+ return ajax_response(response=SQL)
+
+ @check_precondition
+ @validate_request
+ def msql(self, gid, sid, did, scid, foid=None):
+ """
+ Returns the modified SQL.
+
+ Args:
+ gid: Server Group Id
+ sid: Server Id
+ did: Database Id
+ scid: Schema Id
+ foid: Foreign Table Id
+ name: Foreign Table Name
+ ftsrvname: Foreign Server Name
+
+ Returns:
+ SQL statements to create/update the Foreign Table.
+ """
+ status, SQL = self.get_sql(gid, sid, did, scid, self.request, foid)
+ if status:
+ return make_json_response(
+ data=SQL,
+ status=200
+ )
+ else:
+ return SQL
+
+ def get_sql(self, gid, sid, did, scid, data, foid=None):
+ """
+ Genrates the SQL statements to create/update the Foreign Table.
+
+ Args:
+ gid: Server Group Id
+ sid: Server Id
+ did: Database Id
+ scid: Schema Id
+ foid: Foreign Table Id
+ """
+ try:
+ if foid is not None:
+ old_data = self.get_properties(gid, sid, did, scid, foid,
+ inherits=True)
+
+ # Prepare dict of columns with key = column's attnum
+ # Will use this in the update template when any column is
+ # changed, to identify the columns.
+ col_data = {}
+ for c in old_data['columns']:
+ col_data[c['attnum']] = c
+
+ old_data['columns'] = col_data
+
+ SQL = render_template(
+ "/".join([self.template_path, 'update.sql']),
+ data=data, o_data=old_data
+ )
+ else:
+ SQL = render_template("/".join([self.template_path,
+ 'create.sql']), data=data)
+ return True, SQL
+
+ except Exception as e:
+ return False, e
+
+ @check_precondition
+ def dependents(self, gid, sid, did, scid, foid):
+ """
+ This function get the dependents and return ajax response
+ for the Foreign Table object.
+
+ Args:
+ gid: Server Group Id
+ sid: Server Id
+ did: Database Id
+ scid: Schema Id
+ foid: Foreign Table Id
+ """
+ dependents_result = self.get_dependents(self.conn, foid)
+ return ajax_response(
+ response=dependents_result,
+ status=200
+ )
+
+ @check_precondition
+ def dependencies(self, gid, sid, did, scid, foid):
+ """
+ This function get the dependencies and return ajax response
+ for the Foreign Table object.
+
+ Args:
+ gid: Server Group Id
+ sid: Server Id
+ did: Database Id
+ scid: Schema Id
+ foid: Foreign Table Id
+ """
+ dependencies_result = self.get_dependencies(self.conn, foid)
+ return ajax_response(
+ response=dependencies_result,
+ status=200
+ )
+
+ def get_properties(self, gid, sid, did, scid, foid, inherits=False):
+ """
+ Returns the Foreign Table properties which will be used in
+ properties, sql and get_sql functions.
+
+ Args:
+ gid: Server Group Id
+ sid: Server Id
+ did: Database Id
+ scid: Schema Id
+ foid: Foreign Table Id
+ inherits: If True then inherited table will be fetched from
+ database
+
+ Returns:
+
+ """
+ SQL = render_template("/".join([self.template_path,
+ 'properties.sql']),
+ scid=scid, foid=foid)
+ status, res = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ data = res['rows'][0]
+
+ SQL = render_template("/".join([self.template_path,
+ 'get_constraints.sql']), foid=foid)
+ status, cons = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=cons)
+
+ if cons and 'rows' in cons:
+ data['constraints'] = cons['rows']
+
+ SQL = render_template("/".join([self.template_path,
+ 'get_columns.sql']), foid=foid)
+ status, cols = self.conn.execute_dict(SQL)
+ if not status:
+ return internal_server_error(errormsg=cols)
+
+ # The Length and the precision of the Datatype should be separated.
+ # The Format we getting from database is: numeric(1,1)
+ # So, we need to separate it as Length: 1, Precision: 1
+ for c in cols['rows']:
+ if c['fulltype'] != '' and c['fulltype'].find("(") > 0:
+ substr = c['fulltype'][c['fulltype'].find("(") + 1:len(
+ c['fulltype']) - 1]
+ typlen = substr.split(",")
+ if len(typlen) > 1:
+ c['typlen'] = typlen[0]
+ c['precision'] = typlen[1]
+ else:
+ c['typlen'] = typlen
+ c['precision'] = ''
+
+ if cols and 'rows' in cols:
+ data['columns'] = cols['rows']
+
+ # Get Inherited table names from their OID
+ if inherits:
+ if 'inherits' in data and data['inherits']:
+ inherits = tuple([int(x) for x in data['inherits']])
+ if len(inherits) == 1:
+ inherits = "(" + str(inherits[0]) + ")"
+
+ SQL = render_template("/".join([self.template_path,
+ 'get_tables.sql']),
+ attrelid=inherits)
+ status, res = self.conn.execute_dict(SQL)
+
+ if not status:
+ return internal_server_error(errormsg=res)
+
+ if 'inherits' in res['rows'][0]:
+ data['inherits'] = res['rows'][0]['inherits']
+
+ return data
+
+ForeignTableView.register_node_view(blueprint)
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/static/img/coll-foreign-table.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/static/img/coll-foreign-table.png
new file mode 100644
index 0000000000000000000000000000000000000000..dc1f742bbca05980f3c4cbf3c16af0e0cf9afa41
GIT binary patch
literal 492
zcmV<I0Tcd-P)<h;3K|Lk000e1NJLTq000mG000mO0{{R3C@l|D00001b5ch_0Itp)
z=>Px#x=>71MPs@4A(iKf&i<6o`K{IR%G~PS<LBn*=jP_-#n|R{x#%#Q=!nh!Qn2!~
z)$QHg-Gj^iddB;2!1-3P^GU1lJ*Dk2p6e%>>E7PngUkI&tMEOf?I@Y)-{0Tj;^O1u
z<K^Y$hrs9H;NYjHr>v~3?Ck9B?(XU7>EYqwrlzK-sHm;2t*@`I>gwu{%Jb*v=j-e1
z=;-LFsj2Vp@A2{R?d|QVs;aQCu(GnU<mBY=@bG)a`)|PbD4FPqpT#2p0004WQchC<
zK<3zH00001VoOIv0Eh)0NB{r;32;bRa{vGf6951U69E94oEQKA00(qQO+^RW0~{47
zCVc;%cK`qYo=HSOR2b7;&%F-9Fbqb~d^Ca9F5y=Q3JhUtmQ1dM!u!7$P76ZR<IR!f
z82rYJgGFWCa#hz2@f6zizd<`Q@H2zW$2=nuHC%$4?T(m2Al7g$=E5Y|iAXfK7sEj;
zPLi`2Jc=Qy04vsM=+&^dH+YdDJCl+cEQbyUeIH!8q}L#F1ok2?4QkNnPT(qT`XOTQ
i<DH48klNUNZ+`=M(hUmmK;RYt0000<MNUMnLSTX=Tlvud
literal 0
HcmV?d00001
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/static/img/foreign-table.png b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/static/img/foreign-table.png
new file mode 100644
index 0000000000000000000000000000000000000000..53c133885a0c0c7050e4c8580adfd75f8ceaa852
GIT binary patch
literal 563
zcmV-30?hr1P)<h;3K|Lk000e1NJLTq000mG000mO0{{R3C@l|D00001b5ch_0Itp)
z=>Px#)lf`SMdak<A(iKi(EpOp`=ZnJy4dZ}-|64u=jG<-=H}+w;pME(=VP(xE}Q6!
z&;NzY{(i{(b;J5>zW8Cd_Exg<ORVrfr|vhP>@A$?CYb4k%>8S=_(7)bHlge;oa(gJ
z?%mzp-rnBd-{0Wi;Njun;^N}t<KyM!<%hrL=;-L_>FMX^=c%cwtgNi;?CkCB?eFjJ
z>gwvJr>Cf>sI9H7udlD`>+A6F@bdEV?(XiZtE;fEu(7eRva+)A@$vKX^Y!)hwY9bO
z_V)Pr__nsTxVX6Y_xDt#KRo~d00DGTPE!Ct=GbNc0004EOGiWihy@);00009a7bBm
z000XU000XU0RWnu7ytkO2XskIMF-mh92F=elJWmr0002WNkl<Z7}HhH-EM+F5C!0!
z!a`SOE!5&qON&%(w?ITOaT9H_G4uZK<Sexp&fR=DnamLa?nVd!4?!rS_))1*g4)CQ
zDb1cIFZpyfUkr)yYnr_=6z?B@P%f9%=W4zA`c`Iu+kWX0jT}HVKkWuJCI&TMg{nWa
ztu|yMBI+4@9u0*sBWsCaRo8o@03dXwbPVhEq?2+1l3Zsa2G!IMcR5VT$aNl?orRdF
z0LaLt4E6b!;~_UINb+ZJ+;+W@L)Soi@Pqk(|0_yz6w|&QQ{?~v002ovPDHLkV1nFh
BCnEp=
literal 0
HcmV?d00001
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
new file mode 100644
index 0000000..8258544
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/js/foreign_tables.js
@@ -0,0 +1,493 @@
+/* Create and Register Foreign Table Collection and Node. */
+define(
+ ['jquery', 'underscore', 'underscore.string', 'pgadmin', 'pgadmin.browser', 'alertify', 'pgadmin.browser.collection'],
+function($, _, S, pgAdmin, pgBrowser, alertify) {
+
+ if (!pgBrowser.Nodes['coll-foreign-table']) {
+ var foreigntable = pgAdmin.Browser.Nodes['coll-foreign-table'] =
+ pgAdmin.Browser.Collection.extend({
+ node: 'foreign-table',
+ label: '{{ _('Foreign Tables') }}',
+ type: 'coll-foreign-table',
+ columns: ['name', 'owner', 'description']
+ });
+ };
+
+ // Security Model
+ var SecurityModel = Backform.SecurityModel = pgAdmin.Browser.Node.Model.extend({
+ defaults: {
+ provider: null,
+ label: null
+ },
+ schema: [{
+ id: 'provider', label: '{{ _('Provider') }}',
+ type: 'text'
+ },{
+ id: 'label', label: '{{ _('Security Label') }}',
+ type: 'text'
+ }],
+ validate: function() {
+ var err = {},
+ errmsg = null,
+ data = this.toJSON();
+
+ if (_.isUndefined(data.label) ||
+ _.isNull(data.label) ||
+ String(data.label).replace(/^\s+|\s+$/g, '') == '') {
+ return _("Please specify the value for all the security providers.");
+ }
+ return null;
+ }
+ });
+
+
+ // Columns Model
+ var ColumnsModel = pgAdmin.Browser.Node.Model.extend({
+ idAttribute: 'attname',
+ defaults: {
+ attname: undefined,
+ datatype: undefined,
+ typlen: undefined,
+ precision: undefined,
+ typdefault: undefined,
+ attnotnull: undefined,
+ collname: undefined,
+ attnum: undefined,
+ inheritedfrom: undefined,
+ inheritedid: undefined
+ },
+ schema: [
+ {id: 'attname', label:'{{ _('Name') }}', cell: 'string', type: 'text' },
+ {id: 'datatype', label:'{{ _('Data Type') }}', cell: 'string', control: 'node-ajax-options', type: 'text', url: 'get_types'},
+ {id: 'typlen', label:'{{ _('Length') }}', cell: 'string',type: 'text'},
+ {id: 'precision', label:'{{ _('Precision') }}', cell: 'string',type: 'text'},
+ {id: 'typdefault', label:'{{ _('Default') }}', type: 'text', cell: 'string', min_version: 90300},
+ {id: 'attnotnull', label:'{{ _('Not Null') }}', cell: 'boolean',type: 'switch'},
+ {id: 'collname', label:'{{ _('Collation') }}', cell: 'string', control: 'node-ajax-options', type: 'text', url: 'get_collations', min_version: 90300},
+ {id: 'attnum', cell: 'string',type: 'text', visible: false},
+ {id: 'inheritedfrom', label:'{{ _('Inherited From') }}', cell: 'string',type: 'text', visible: false, mode: ['properties']}
+ ],
+ validate: function() {
+ // TODO: Add validation here
+ },
+ toJSON: Backbone.Model.prototype.toJSON
+ });
+
+ var formatNode = function(opt) {
+ if (!opt.id) {
+ return opt.text;
+ }
+
+ var optimage = $(opt.element).data('image');
+
+ if(!optimage){
+ return opt.text;
+ } else {
+ return $(
+ '<span><span class="wcTabIcon ' + optimage + '"/>' + opt.text + '</span>'
+ );
+ }
+ };
+
+
+ /* NodeAjaxOptionsMultipleControl is for multiple selection of Combobox.
+ * This control is used to select Multiple Parent Tables to be inherited.
+ * It also populates/vacates Columns on selection/deselection of the option (i.e. table name).
+ * To populates the column, it calls the server and fetch the columns data
+ * for the selected table.
+ */
+
+ var NodeAjaxOptionsMultipleControl = Backform.NodeAjaxOptionsMultipleControl = Backform.NodeAjaxOptionsControl.extend({
+ template: _.template([
+ '<label class="<%=Backform.controlLabelClassName%>"><%=label%></label>',
+ '<div class="<%=Backform.controlsClassName%> <%=extraClasses.join(\' \')%>">',
+ ' <select class="pgadmin-node-select form-control" name="<%=name%>" style="width:100%;" value=<%-value%> <%=disabled ? "disabled" : ""%> <%=required ? "required" : ""%> >',
+ ' </select>',
+ '</div>'].join("\n")),
+ defaults: _.extend(
+ {}, Backform.NodeAjaxOptionsControl.prototype.defaults,
+ {
+ select2: {
+ allowClear: true,
+ placeholder: 'Select from the list',
+ width: 'style',
+ templateResult: formatNode,
+ templateSelection: formatNode
+ }
+ }),
+ render: function() {
+ var field = _.defaults(this.field.toJSON(), this.defaults),
+ attributes = this.model.toJSON(),
+ attrArr = field.name.split('.'),
+ name = attrArr.shift(),
+ path = attrArr.join('.'),
+ rawValue = this.keyPathAccessor(attributes[name], path),
+ data = _.extend(field, {
+ rawValue: rawValue,
+ value: this.formatter.fromRaw(rawValue, this.model),
+ attributes: attributes,
+ formatter: this.formatter
+ }),
+ evalF = function(f, d, m) {
+ return (_.isFunction(f) ? !!f.apply(d, [m]) : !!f);
+ };
+
+ // Evaluate the disabled, visible, and required option
+ _.extend(data, {
+ disabled: evalF(data.disabled, data, this.model),
+ visible: evalF(data.visible, data, this.model),
+ required: evalF(data.required, data, this.model)
+ });
+
+ // Evaluation the options
+ if (_.isFunction(data.options)) {
+ try {
+ data.options = data.options.apply(this)
+ } catch(e) {
+ // Do nothing
+ data.options = []
+ this.model.trigger('pgadmin-view:transform:error', m, self.field, e);
+ }
+ }
+
+ // Clean up first
+ this.$el.removeClass(Backform.hiddenClassname);
+ this.$el.html(this.template(data)).addClass(field.name);
+
+ if (!data.visible) {
+ this.$el.addClass(Backform.hiddenClassname);
+ } else {
+ var opts = _.extend(
+ {}, this.defaults.select2, data.select2,
+ {
+ 'data': data.options
+ });
+ this.$el.find("select").select2(opts).val(data.rawValue).trigger("change");
+ this.updateInvalid();
+ }
+
+ return this;
+ },
+ onChange: function(e) {
+ var model = this.model,
+ $el = $(e.target),
+ attrArr = this.field.get("name").split('.'),
+ name = attrArr.shift(),
+ path = attrArr.join('.'),
+ value = this.getValueFromDOM(),
+ changes = {},
+ col_changes = {},
+ col_model = _.clone(model.get('columns'));
+ col_changes['columns'] = col_model.toJSON();
+
+ if (this.model.errorModel instanceof Backbone.Model) {
+ if (_.isEmpty(path)) {
+ this.model.errorModel.unset(name);
+ } else {
+ var nestedError = this.model.errorModel.get(name);
+ if (nestedError) {
+ this.keyPathSetter(nestedError, path, null);
+ this.model.errorModel.set(name, nestedError);
+ }
+ }
+ }
+
+ var m = model.get(name),
+ self = this;
+
+ if (typeof(m) == "string"){ m = JSON.parse(m); }
+
+ // Remove Columns if option is deselected from the combobox
+ if(_.size(JSON.parse(value)) < _.size(m)) {
+ var dif = _.difference(m, JSON.parse(value));
+ cols = _.filter(col_model.toJSON(), function(o) {
+ return o["inheritedid"] != dif[0]
+ });
+ col_changes['columns'] = cols;
+ }
+ // Populate the Columns on selection of the combobox
+ else {
+ _.each(JSON.parse(value), function(i) {
+ if (! _.contains(m, parseInt(i))){
+ // Fetch Columns from server
+ columns = self.fetchColumns(i);
+ col_changes['columns'] = _.union(col_changes['columns'],columns);
+ }
+ });
+ }
+
+ // Set model for this control and also for columns
+ col_model.set(col_changes['columns']);
+ changes[name] = _.isEmpty(path) ? value : _.clone(model.get(name)) || {};
+
+ if (!_.isEmpty(path)) this.keyPathSetter(changes[name], path, value);
+
+ this.stopListening(this.model, "change:" + name, this.render);
+ model.set(changes);
+
+ this.listenTo(this.model, "change:" + name, this.render);
+ },
+ fetchColumns: function(table_id){
+ var self = this,
+ url = 'get_columns',
+ m = self.model.top || self.model;
+
+ if (url) {
+ var node = this.field.get('schema_node'),
+ node_info = this.field.get('node_info'),
+ full_url = node.generate_url.apply(
+ node, [
+ null, url, this.field.get('node_data'),
+ this.field.get('url_with_id') || false, node_info
+ ]),
+ cache_level = this.field.get('cache_level') || node.type,
+ cache_node = this.field.get('cache_node');
+
+ cache_node = (cache_node && pgAdmin.Browser.Nodes['cache_node']) || node;
+
+ m.trigger('pgadmin:view:fetching', m, self.field);
+ data = {attrelid: table_id}
+
+ // Fetching Columns data for the selected table.
+ $.ajax({
+ async: false,
+ url: full_url,
+ data: data,
+ success: function(res) {
+ /*
+ * We will cache this data for short period of time for avoiding
+ * same calls.
+ */
+ data = cache_node.cache(url, node_info, cache_level, res.data);
+ },
+ error: function() {
+ m.trigger('pgadmin:view:fetch:error', m, self.field);
+ }
+ });
+ m.trigger('pgadmin:view:fetched', m, self.field);
+
+ // To fetch only options from cache, we do not need time from 'at'
+ // attribute but only options.
+ //
+ // It is feasible that the data may not have been fetched.
+ data = (data && data.data) || [];
+ return data;
+ }
+ },
+ });
+
+
+ // Constraints Model
+ var ConstraintModel = pgAdmin.Browser.Node.Model.extend({
+ idAttribute: 'conname',
+ defaults: {
+ conname: undefined,
+ consrc: undefined,
+ connoinherit: undefined
+ },
+ schema: [
+ {id: 'conname', label:'{{ _('Name') }}', type: 'text', cell: 'string', group: 'Definition'},
+ {id: 'consrc', label:'{{ _('Check') }}', type: 'multiline', cell: 'string', group: 'Definition'},
+ {id: 'connoinherit', label:'{{ _('No Inherit') }}', type: 'switch', cell: 'boolean', group: 'Definition'}
+
+ ],
+ validate: function() {
+ // TODO: Add validation here
+ },
+ toJSON: Backbone.Model.prototype.toJSON
+ });
+
+
+ // Options Model
+ var OptionsModel = pgAdmin.Browser.Node.Model.extend({
+ defaults: {
+ option: undefined,
+ value: undefined
+ },
+ schema: [
+ {id: 'option', label:'{{ _('Option') }}', cell: 'string', type: 'text'},
+ {id: 'value', label:'{{ _('Value') }}', cell: 'string',type: 'text'}
+ ],
+ validate: function() {
+ // TODO: Add validation here
+ },
+ toJSON: Backbone.Model.prototype.toJSON
+ });
+
+
+ if (!pgBrowser.Nodes['foreign-table']) {
+ pgAdmin.Browser.Nodes['foreign-table'] = pgBrowser.Node.extend({
+ type: 'foreign-table',
+ label: '{{ _('Foreign Table') }}',
+ collection_type: 'coll-foreign-table',
+ hasSQL: true,
+ hasDepends: true,
+ parent_type: ['schema'],
+ Init: function() {
+ /* Avoid mulitple registration of menus */
+ if (this.initialized)
+ return;
+
+ this.initialized = true;
+
+ pgBrowser.add_menus([{
+ name: 'create_foreign-table_on_coll', node: 'coll-foreign-table', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _('Foreign Table...') }}',
+ icon: 'wcTabIcon icon-foreign-table', data: {action: 'create', check: true},
+ enable: 'canCreate'
+ },{
+ name: 'create_foreign-table', node: 'foreign-table', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _('Foreign Table...') }}',
+ icon: 'wcTabIcon icon-foreign-table', data: {action: 'create', check: true},
+ enable: 'canCreate'
+ },{
+ name: 'create_foreign-table', node: 'schema', module: this,
+ applies: ['object', 'context'], callback: 'show_obj_properties',
+ category: 'create', priority: 4, label: '{{ _('Foreign Table...') }}',
+ icon: 'wcTabIcon icon-foreign-table', data: {action: 'create', check: false},
+ enable: 'canCreate'
+ }
+ ]);
+
+ },
+ canDrop: pgBrowser.Nodes['schema'].canChildDrop,
+ canDropCascade: pgBrowser.Nodes['schema'].canChildDrop,
+ model: pgAdmin.Browser.Node.Model.extend({
+ defaults: {
+ name: undefined,
+ oid: undefined,
+ owner: undefined,
+ basensp: undefined,
+ description: undefined,
+ ftsrvname: undefined,
+ inherits: [],
+ columns: [],
+ constraints: [],
+ ftoptions: [],
+ seclabels: []
+ },
+ schema: [{
+ id: 'name', label: '{{ _('Name') }}', cell: 'string',
+ type: 'text', mode: ['properties', 'create', 'edit']
+ },{
+ id: 'oid', label:'{{ _('Oid') }}', cell: 'string',
+ type: 'text' , mode: ['properties']
+ },{
+ id: 'owner', label:'{{ _('Owner') }}', cell: 'string', control: Backform.NodeListByNameControl,
+ node: 'role', type: 'text', mode: ['edit', 'properties']
+ },{
+ id: 'basensp', label:'{{ _('Schema') }}', cell: 'string', control: 'node-ajax-options',
+ type: 'text', url: 'get_schemas', mode: ['edit', 'create', 'properties'],
+ },{
+ id: 'description', label:'{{ _('Comment') }}', cell: 'string',
+ type: 'multiline'
+ },{
+ 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',
+ url: 'get_tables', select2: {multiple: true},
+ 'cache_level': 'database'
+ },{
+ id: 'columns', label:'{{ _('Columns') }}', cell: 'string',
+ type: 'collection', group: 'Columns', visible: false, mode: ['edit', 'create', 'properties'],
+ model: ColumnsModel, canAdd: true, canDelete: true, canEdit: true,
+ columns: ['attname', 'datatype', 'typlen', 'precision', 'typdefault', 'attnotnull', 'collname', 'inheritedfrom']
+ },
+ {
+ id: 'constraints', label:'{{ _('Constraints') }}', cell: 'string',
+ type: 'collection', group: 'Constraints', visible: false, mode: ['edit', 'create', 'properties'],
+ model: ConstraintModel, canAdd: true, canDelete: true, canEdit: function(o) {
+ if (o instanceof Backbone.Model) {
+ if (o instanceof ConstraintModel) {
+ return o.isNew();
+ }
+ }
+ return true;
+ }, min_version: 90500
+ },{
+ id: 'ftoptions', label:'{{ _('Options') }}', cell: 'string',
+ type: 'collection', group: 'Options', visible: false, mode: ['edit', 'create', 'properties'],
+ model: OptionsModel, canAdd: true, canDelete: true, canEdit: true
+ },{
+ id: 'seclabels', label: '{{ _('Security Labels') }}',
+ model: SecurityModel, type: 'collection',
+ group: '{{ _('Security') }}', mode: ['edit', 'create'],
+ min_version: 90100, canAdd: true,
+ canEdit: true, canDelete: true
+ }
+ ],
+ validate: function()
+ {
+ var err = {},
+ errmsg,
+ seclabels = this.get('seclabels');
+
+ if (_.isUndefined(this.get('name')) || String(this.get('name')).replace(/^\s+|\s+$/g, '') == '') {
+ err['name'] = '{{ _('Name can not be empty!') }}';
+ errmsg = errmsg || err['name'];
+ }
+
+ if (_.isUndefined(this.get('ftsrvname')) || String(this.get('ftsrvname')).replace(/^\s+|\s+$/g, '') == '') {
+ err['ftsrvname'] = '{{ _('Foreign Server can not be empty!') }}';
+ errmsg = errmsg || err['ftsrvname'];
+ }
+
+ if (seclabels) {
+ var secLabelsErr;
+ for (var i = 0; i < seclabels.models.length && !secLabelsErr; i++) {
+ secLabelsErr = (seclabels.models[i]).validate.apply(seclabels.models[i]);
+ if (secLabelsErr) {
+ err['seclabels'] = secLabelsErr;
+ errmsg = errmsg || secLabelsErr;
+ }
+ }
+ }
+
+ this.errorModel.clear().set(err);
+
+ if (_.size(err)) {
+ this.trigger('on-status', {msg: errmsg});
+ return errmsg;
+ }
+
+ return null;
+ }
+ }),
+ canCreate: function(itemData, item, data) {
+ //If check is false then , we will allow create menu
+ if (data && data.check == false)
+ return true;
+
+ var t = pgBrowser.tree, i = item, d = itemData;
+ // To iterate over tree to check parent node
+ while (i) {
+ // If it is schema then allow user to create foreign table
+ if (_.indexOf(['schema'], d._type) > -1)
+ return true;
+
+ if ('coll-foreign-table' == d._type) {
+ //Check if we are not child of catalog
+ prev_i = t.hasParent(i) ? t.parent(i) : null;
+ prev_d = prev_i ? t.itemData(prev_i) : null;
+ if( prev_d._type == 'catalog') {
+ return false;
+ } else {
+ return true;
+ }
+ }
+ i = t.hasParent(i) ? t.parent(i) : null;
+ d = i ? t.itemData(i) : null;
+ }
+ // by default we do not want to allow create menu
+ return true;
+ }
+ });
+
+ }
+
+ return pgBrowser.Nodes['foreign-table'];
+});
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/create.sql
new file mode 100644
index 0000000..00df296
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/create.sql
@@ -0,0 +1,37 @@
+{% import 'macros/security.macros' as SECLABLE %}
+{% if data %}
+CREATE FOREIGN TABLE {{ conn|qtIdent(data.basensp, data.name) }}
+ ({% if data.columns %}
+{% for c in data.columns %}
+{% if not loop.first%} {% endif %}{{conn|qtIdent(c.attname)}} {{ conn|qtTypeIdent(c.datatype) }}{% if c.typlen %}({{c.typlen}} {% if c.precision %}, {{c.precision}}{% endif %}){% endif %}
+{% if c.attnotnull %} NOT NULL{% else %} NULL{% endif %}
+{% if not loop.last %},
+{% endif %}
+{% endfor %}
+{% endif %}
+)
+ SERVER {{ conn|qtIdent(data.ftsrvname) }};
+
+{% if data.ftoptions %}
+{% for o in data.ftoptions %}
+{% 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 data.description %}
+COMMENT ON FOREIGN TABLE {{ conn|qtIdent(data.basensp, data.name) }}
+ IS '{{ data.description }}';
+{% endif -%}
+
+{% if data.seclabels %}
+{% for r in data.seclabels %}
+{% if r.label and r.provider %}
+{{ SECLABLE.APPLY(conn, 'FOREIGN TABLE', data.name, r.provider, r.label) }}
+{% endif %}
+{% endfor %}
+{% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/delete.sql
new file mode 100644
index 0000000..b1bc53d
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/delete.sql
@@ -0,0 +1,15 @@
+{% if scid and foid %}
+SELECT
+ c.relname AS name, nspname as basensp
+FROM
+ pg_class c
+LEFT OUTER JOIN
+ pg_namespace nsp ON (nsp.oid=c.relnamespace)
+WHERE
+ c.relnamespace = {{scid}}::oid
+ AND c.oid = {{foid}}::oid;
+{% endif %}
+
+{% if name %}
+DROP FOREIGN TABLE {{ conn|qtIdent(basensp, name) }}{% if cascade%} CASCADE{% endif %};
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_collations.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_collations.sql
new file mode 100644
index 0000000..e59c17d
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_collations.sql
@@ -0,0 +1,10 @@
+SELECT --nspname, collname,
+ CASE WHEN length(nspname) > 0 AND length(collname) > 0 THEN
+ concat(nspname, '."', collname,'"')
+ ELSE '' END AS copy_collation
+FROM
+ pg_collation c, pg_namespace n
+WHERE
+ c.collnamespace=n.oid
+ORDER BY
+ nspname, collname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_columns.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_columns.sql
new file mode 100644
index 0000000..0733a3c
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_columns.sql
@@ -0,0 +1,16 @@
+SELECT
+ attname, attndims, atttypmod, format_type(t.oid,NULL) AS datatype,
+ format_type(t.oid, att.atttypmod) AS fulltype, attnotnull, attnum,
+ (SELECT COUNT(1) from pg_type t2 WHERE t2.typname=t.typname) > 1 AS isdup
+FROM
+ pg_attribute att
+JOIN
+ pg_type t ON t.oid=atttypid
+JOIN
+ pg_namespace nsp ON t.typnamespace=nsp.oid
+LEFT OUTER JOIN
+ pg_type b ON t.typelem=b.oid
+WHERE
+ att.attrelid={{foid}}::oid
+ AND attnum>0
+ORDER by attnum;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_constraints.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_constraints.sql
new file mode 100644
index 0000000..fc93549
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_constraints.sql
@@ -0,0 +1,7 @@
+SELECT
+ conname, contype, consrc
+FROM
+ pg_constraint
+WHERE
+ conrelid={{foid}}::oid
+ORDER by conname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_foreign_servers.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_foreign_servers.sql
new file mode 100644
index 0000000..1980f02
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_foreign_servers.sql
@@ -0,0 +1,5 @@
+SELECT
+ srvname
+FROM
+ pg_foreign_server
+ORDER BY srvname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_oid.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_oid.sql
new file mode 100644
index 0000000..561434b
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_oid.sql
@@ -0,0 +1,9 @@
+SELECT
+ c.oid, c.relname as name
+FROM
+ pg_class c
+JOIN
+ pg_namespace bn ON bn.oid=c.relnamespace
+WHERE
+ bn.nspname = {{ basensp|qtLiteral }}
+ AND c.relname={{ name|qtLiteral }}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_schemas.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_schemas.sql
new file mode 100644
index 0000000..bf87a37
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_schemas.sql
@@ -0,0 +1,23 @@
+SELECT
+ nsp.nspname
+FROM
+ pg_namespace nsp
+LEFT OUTER JOIN
+ pg_description des ON (des.objoid=nsp.oid AND des.classoid='pg_namespace'::regclass)
+LEFT OUTER JOIN
+ pg_catalog.pg_default_acl dacl ON (dacl.defaclnamespace = nsp.oid)
+WHERE
+ NOT ((nspname = 'pg_catalog' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pg_class' AND relnamespace = nsp.oid LIMIT 1)) OR
+ (nspname = 'pgagent' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pga_job' AND relnamespace = nsp.oid LIMIT 1)) OR
+ (nspname = 'information_schema' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'tables' AND relnamespace = nsp.oid LIMIT 1)) OR
+ (nspname LIKE '_%' AND EXISTS
+ (SELECT 1 FROM pg_proc WHERE proname='slonyversion' AND pronamespace = nsp.oid LIMIT 1))
+ ) AND
+ nspname NOT LIKE E'pg\\temp\\%' AND
+ nspname NOT LIKE E'pg\\toast_temp\\%' AND
+ -- ADDED: Because We need to omit system schema except the one on which we are trying to create collation
+ ( nsp.oid = {{ scid }} OR nspname NOT LIKE E'pg\\_%' )
+ORDER BY nspname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_types.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_types.sql
new file mode 100644
index 0000000..72cfa26
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/get_types.sql
@@ -0,0 +1,17 @@
+SELECT
+ *
+FROM
+ (SELECT format_type(t.oid,NULL) AS typname, CASE WHEN typelem > 0 THEN typelem ELSE t.oid END as elemoid, typlen, typtype, t.oid, nspname,
+ (SELECT COUNT(1) FROM pg_type t2 WHERE t2.typname = t.typname) > 1 AS isdup
+ FROM
+ pg_type t
+ JOIN
+ pg_namespace nsp ON typnamespace=nsp.oid
+ WHERE
+ (NOT (typname = 'unknown' AND nspname = 'pg_catalog'))
+ AND typisdefined AND typtype IN ('b', 'c', 'd', 'e', 'r')
+ AND NOT EXISTS (select 1 from pg_class where relnamespace=typnamespace and relname = typname and relkind != 'c')
+ AND (typname not like '_%' OR NOT EXISTS (select 1 from pg_class where relnamespace=typnamespace and relname = substring(typname from 2)::name and relkind != 'c'))
+ ) AS dummy
+ORDER BY
+ nspname <> 'pg_catalog', nspname <> 'public', nspname, 1;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/node.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/node.sql
new file mode 100644
index 0000000..2d3a38b
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/node.sql
@@ -0,0 +1,12 @@
+SELECT
+ c.oid, c.relname AS name, pg_get_userbyid(relowner) AS owner,
+ ftoptions, nspname as basensp
+FROM
+ pg_class c
+JOIN
+ pg_foreign_table ft ON c.oid=ft.ftrelid
+LEFT OUTER JOIN
+ pg_namespace nsp ON (nsp.oid=c.relnamespace)
+WHERE
+ c.relnamespace = {{scid}}::oid
+ORDER BY c.relname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/properties.sql
new file mode 100644
index 0000000..3561380
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/properties.sql
@@ -0,0 +1,21 @@
+SELECT
+ c.oid, c.relname AS name, pg_get_userbyid(relowner) AS owner,
+ ftoptions, srvname AS ftsrvname, description, nspname as basensp,
+ (SELECT array_agg(label) FROM pg_seclabels sl1 WHERE sl1.objoid=c.oid) AS labels,
+ (SELECT array_agg(provider) FROM pg_seclabels sl2 WHERE sl2.objoid=c.oid) AS providers
+FROM
+ pg_class c
+JOIN
+ pg_foreign_table ft ON c.oid=ft.ftrelid
+LEFT OUTER JOIN
+ pg_foreign_server fs ON ft.ftserver=fs.oid
+LEFT OUTER JOIN
+ pg_description des ON (des.objoid=c.oid AND des.classoid='pg_class'::regclass)
+LEFT OUTER JOIN
+ pg_namespace nsp ON (nsp.oid=c.relnamespace)
+WHERE
+ c.relnamespace = {{scid}}::oid
+ {% if foid %}
+ AND c.oid = {{foid}}::oid
+ {% endif %}
+ORDER BY c.relname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/update.sql
new file mode 100644
index 0000000..cf9056b
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.1_plus/update.sql
@@ -0,0 +1,103 @@
+{% import 'macros/security.macros' as SECLABLE %}
+{% if data %}
+{% set name = o_data.name %}
+{% if data.name %}
+{% if data.name != o_data.name %}
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, o_data.name) }}
+RENAME TO {{ conn|qtIdent(data.name) }};
+{% set name = data.name %}
+{% endif %}
+{% endif -%}
+
+{% if data.owner %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+OWNER TO {{ data.owner }};
+{% endif -%}
+
+{% if data.columns %}
+{% for c in data.columns.deleted %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+DROP COLUMN {{conn|qtIdent(c.attname)}};
+{% endfor -%}
+
+{% for c in data.columns.added %}
+
+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.attnotnull %} NOT NULL {% else %} NULL {% endif %}
+{% if c.typdefault %} DEFAULT {{c.typdefault|qtLiteral}}{% endif %}
+{% if c.collname %} COLLATE {{c.collname}}{% endif %};
+{% endfor -%}
+
+{% for c in data.columns.changed %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+DROP COLUMN {{conn|qtIdent(o_data['columns'][c.attnum]['attname'])}};
+
+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.attnotnull %} NOT NULL {% else %} NULL {% endif %}
+{% if c.typdefault %} DEFAULT {{c.typdefault|qtLiteral}}{% endif %}
+{% if c.collname %} COLLATE {{c.collname}}{% endif %};
+{% endfor %}
+{% endif -%}
+
+
+{% if data.ftoptions %}
+{% for o in data.ftoptions.deleted %}
+{% if o.option and o.value %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+OPTIONS ( DROP {{o.option}} {{o.value|qtLiteral}});
+{% endif %}
+{% endfor %}
+
+{% for o in data.ftoptions.added %}
+{% if o.option and o.value %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+OPTIONS (ADD {{o.option}} {{o.value|qtLiteral}});
+{% endif %}
+{% endfor %}
+
+{% for o in data.ftoptions.changed %}
+{% if o.option and o.value %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+OPTIONS (SET {{o.option}} {{o.value|qtLiteral}});
+{% endif %}
+{% endfor %}
+{% endif -%}
+
+
+{% set seclabels = data.seclabels %}
+{% if 'deleted' in seclabels and seclabels.deleted|length > 0 %}
+{% for r in seclabels.deleted %}
+{{ SECLABLE.DROP(conn, 'FOREIGN TABLE', conn|qtIdent(o_data.basensp, name), r.provider) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in seclabels and seclabels.added|length > 0 %}
+{% for r in seclabels.added %}
+{{ SECLABLE.APPLY(conn, 'FOREIGN TABLE', conn|qtIdent(o_data.basensp, name), r.provider, r.label) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in seclabels and seclabels.changed|length > 0 %}
+{% for r in seclabels.changed %}
+{{ SECLABLE.APPLY(conn, 'FOREIGN TABLE', conn|qtIdent(o_data.basensp, name), r.provider, r.label) }}
+{% endfor %}
+{% endif -%}
+
+{% if data.description %}
+
+COMMENT ON FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ IS {{ data.description|qtLiteral }};
+{% endif -%}
+
+{% if data.basensp %}
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+SET SCHEMA {{ conn|qtIdent(data.basensp) }};
+{% endif %}
+
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/create.sql
new file mode 100644
index 0000000..67c44c1
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/create.sql
@@ -0,0 +1,38 @@
+{% import 'macros/security.macros' as SECLABLE %}
+{% if data %}
+CREATE FOREIGN TABLE {{ conn|qtIdent(data.basensp, data.name) }}
+ ({% if data.columns %}
+{% for c in data.columns %}
+{% if not loop.first%} {% endif %}{{conn|qtIdent(c.attname)}} {{
+conn|qtTypeIdent(c.datatype) }}{% if c.typlen %}({{c.typlen}} {% if c.precision %}, {{c.precision}}{% endif %}){% endif %}{% if c.attnotnull %}
+NOT NULL {% else %} NULL{% endif %}{% if c.typdefault %}
+DEFAULT {{c.typdefault|qtLiteral}}{% endif %}{% if c.collname %}
+COLLATE{{c.collname}}{% endif %}
+{% if not loop.last %},
+{% endif %}
+{% endfor -%}{% endif -%})
+ SERVER {{ conn|qtIdent(data.ftsrvname) }};
+
+{% if data.ftoptions %}
+{% for o in data.ftoptions %}
+{% 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 data.description %}
+COMMENT ON FOREIGN TABLE {{ conn|qtIdent(data.basensp, data.name) }}
+ IS '{{ data.description }}';
+{% endif -%}
+
+{% if data.seclabels %}
+{% for r in data.seclabels %}
+{% if r.label and r.provider %}
+{{ SECLABLE.APPLY(conn, 'FOREIGN TABLE', data.name, r.provider, r.label) }}
+{% endif %}
+{% endfor %}
+{% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/delete.sql
new file mode 100644
index 0000000..5bb0969
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/delete.sql
@@ -0,0 +1,17 @@
+{% if scid and foid %}
+SELECT
+ c.relname AS name, nspname as basensp
+FROM
+ pg_class c
+LEFT OUTER JOIN
+ pg_namespace nsp ON (nsp.oid=c.relnamespace)
+WHERE
+ c.relnamespace = {{scid}}::oid
+AND
+ c.oid = {{foid}}::oid;
+{% endif %}
+
+
+{% if name %}
+DROP FOREIGN TABLE {{ conn|qtIdent(basensp, name) }}{% if cascade%} CASCADE{% endif %};
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_collations.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_collations.sql
new file mode 100644
index 0000000..14c3b2e
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_collations.sql
@@ -0,0 +1,9 @@
+SELECT --nspname, collname,
+ CASE WHEN length(nspname) > 0 AND length(collname) > 0 THEN
+ concat(nspname, '."', collname,'"')
+ ELSE '' END AS copy_collation
+FROM
+ pg_collation c, pg_namespace n
+WHERE
+ c.collnamespace=n.oid
+ORDER BY nspname, collname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_columns.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_columns.sql
new file mode 100644
index 0000000..c6cfa97
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_columns.sql
@@ -0,0 +1,22 @@
+SELECT
+ attname, attndims, atttypmod, format_type(t.oid,NULL) AS datatype,
+ attnotnull, 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,
+ (SELECT COUNT(1) from pg_type t2 WHERE t2.typname=t.typname) > 1 AS isdup
+FROM
+ pg_attribute att
+JOIN
+ pg_type t ON t.oid=atttypid
+JOIN
+ pg_namespace nsp ON t.typnamespace=nsp.oid
+LEFT OUTER JOIN
+ pg_type b ON t.typelem=b.oid
+LEFT OUTER JOIN
+ pg_collation cl ON t.typcollation=cl.oid
+LEFT OUTER JOIN
+ pg_namespace cn ON cl.collnamespace=cn.oid
+WHERE
+ att.attrelid={{foid}}::oid
+ AND attnum>0
+ORDER by attnum;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_constraints.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_constraints.sql
new file mode 100644
index 0000000..fc93549
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_constraints.sql
@@ -0,0 +1,7 @@
+SELECT
+ conname, contype, consrc
+FROM
+ pg_constraint
+WHERE
+ conrelid={{foid}}::oid
+ORDER by conname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_foreign_servers.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_foreign_servers.sql
new file mode 100644
index 0000000..1c95ce5
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_foreign_servers.sql
@@ -0,0 +1,6 @@
+SELECT
+ srvname
+FROM
+ pg_foreign_server
+ORDER BY
+ srvname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_oid.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_oid.sql
new file mode 100644
index 0000000..561434b
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_oid.sql
@@ -0,0 +1,9 @@
+SELECT
+ c.oid, c.relname as name
+FROM
+ pg_class c
+JOIN
+ pg_namespace bn ON bn.oid=c.relnamespace
+WHERE
+ bn.nspname = {{ basensp|qtLiteral }}
+ AND c.relname={{ name|qtLiteral }}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_schemas.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_schemas.sql
new file mode 100644
index 0000000..6b92d37
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_schemas.sql
@@ -0,0 +1,23 @@
+SELECT
+ nsp.nspname
+FROM
+ pg_namespace nsp
+ LEFT OUTER JOIN
+ pg_description des ON (des.objoid=nsp.oid AND des.classoid='pg_namespace'::regclass)
+ LEFT OUTER JOIN
+ pg_catalog.pg_default_acl dacl ON (dacl.defaclnamespace = nsp.oid)
+WHERE
+ NOT ((nspname = 'pg_catalog' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pg_class' AND relnamespace = nsp.oid LIMIT 1)) OR
+ (nspname = 'pgagent' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pga_job' AND relnamespace = nsp.oid LIMIT 1)) OR
+ (nspname = 'information_schema' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'tables' AND relnamespace = nsp.oid LIMIT 1)) OR
+ (nspname LIKE '_%' AND EXISTS
+ (SELECT 1 FROM pg_proc WHERE proname='slonyversion' AND pronamespace = nsp.oid LIMIT 1))
+ )AND
+ nspname NOT LIKE E'pg\\temp\\%' AND
+ nspname NOT LIKE E'pg\\toast_temp\\%' AND
+ -- ADDED: Because We need to omit system schema except the one on which we are trying to create collation
+ ( nsp.oid = {{ scid }} OR nspname NOT LIKE E'pg\\_%' )
+ORDER BY nspname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_types.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_types.sql
new file mode 100644
index 0000000..1f57a85
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/get_types.sql
@@ -0,0 +1,15 @@
+SELECT
+ *
+FROM
+ (
+ SELECT format_type(t.oid,NULL) AS typname, CASE WHEN typelem > 0 THEN typelem ELSE t.oid END as elemoid, typlen, typtype, t.oid, nspname,
+ (SELECT COUNT(1) FROM pg_type t2 WHERE t2.typname = t.typname) > 1 AS isdup
+ FROM
+ pg_type t
+ JOIN
+ pg_namespace nsp ON typnamespace=nsp.oid
+ WHERE
+ (NOT (typname = 'unknown' AND nspname = 'pg_catalog')) AND typisdefined AND typtype IN ('b', 'c', 'd', 'e', 'r')AND NOT EXISTS (select 1 from pg_class where relnamespace=typnamespace and relname = typname and relkind != 'c') AND (typname not like '_%' OR NOT EXISTS (select 1 from pg_class where relnamespace=typnamespace and relname = substring(typname from 2)::name and relkind != 'c'))
+ ) AS dummy
+ORDER BY
+ nspname <> 'pg_catalog', nspname <> 'public', nspname, 1;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/node.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/node.sql
new file mode 100644
index 0000000..2d3a38b
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/node.sql
@@ -0,0 +1,12 @@
+SELECT
+ c.oid, c.relname AS name, pg_get_userbyid(relowner) AS owner,
+ ftoptions, nspname as basensp
+FROM
+ pg_class c
+JOIN
+ pg_foreign_table ft ON c.oid=ft.ftrelid
+LEFT OUTER JOIN
+ pg_namespace nsp ON (nsp.oid=c.relnamespace)
+WHERE
+ c.relnamespace = {{scid}}::oid
+ORDER BY c.relname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/properties.sql
new file mode 100644
index 0000000..8f6cedc
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/properties.sql
@@ -0,0 +1,23 @@
+SELECT
+ c.oid, c.relname AS name, pg_get_userbyid(relowner) AS owner,
+ ftoptions, srvname AS ftsrvname, description, nspname as basensp, consrc,
+ (SELECT array_agg(label) FROM pg_seclabels sl1 WHERE sl1.objoid=c.oid) AS labels,
+ (SELECT array_agg(provider) FROM pg_seclabels sl2 WHERE sl2.objoid=c.oid) AS providers
+FROM
+ pg_class c
+JOIN
+ pg_foreign_table ft ON c.oid=ft.ftrelid
+LEFT OUTER JOIN
+ pg_foreign_server fs ON ft.ftserver=fs.oid
+LEFT OUTER JOIN
+ pg_description des ON (des.objoid=c.oid AND des.classoid='pg_class'::regclass)
+LEFT OUTER JOIN
+ pg_namespace nsp ON (nsp.oid=c.relnamespace)
+LEFT OUTER JOIN
+ pg_constraint cn ON (cn.conrelid=c.oid)
+WHERE
+ c.relnamespace = {{scid}}::oid
+ {% if foid %}
+ AND c.oid = {{foid}}::oid
+ {% endif %}
+ORDER BY c.relname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/update.sql
new file mode 100644
index 0000000..385c4fa
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.3_plus/update.sql
@@ -0,0 +1,116 @@
+{% import 'macros/security.macros' as SECLABLE %}
+{% if data %}
+{% set name = o_data.name %}
+{% if data.name %}
+{% if data.name != o_data.name %}
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, o_data.name) }}
+RENAME TO {{ conn|qtIdent(data.name) }};
+{% set name = data.name %}
+{% endif %}
+{% endif -%}
+
+{% if data.owner %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+OWNER TO {{ data.owner }};
+{% endif -%}
+
+{% if data.columns %}
+{% for c in data.columns.deleted %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+DROP COLUMN {{conn|qtIdent(c.attname)}};
+{% endfor -%}
+
+{% for c in data.columns.added %}
+
+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.attnotnull %} NOT NULL {% else %} NULL {% endif %}
+{% if c.typdefault %} DEFAULT {{c.typdefault|qtLiteral}}{% endif %}
+{% if c.collname %} COLLATE {{c.collname}}{% endif %};
+{% endfor -%}
+
+{% for c in data.columns.changed %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+DROP COLUMN {{conn|qtIdent(o_data['columns'][c.attnum]['attname'])}};
+
+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.attnotnull %} NOT NULL {% else %} NULL {% endif %}
+{% if c.typdefault %} DEFAULT {{c.typdefault|qtLiteral}}{% endif %}
+{% if c.collname %} COLLATE {{c.collname}}{% endif %};
+{% endfor -%}
+
+{% endif -%}
+
+{% if data.constraints %}
+{% for c in data.constraints.deleted %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+DROP CONSTRAINT {{conn|qtIdent(c.conname)}};
+{% endfor %}
+
+{% for c in data.constraints.added %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ADD CONSTRAINT {{conn|qtIdent(c.conname)}} {% if c.consrc %} CHECK ({{c.consrc}}){% endif %} {% if c.connoinherit %} NO INHERIT{% endif %};
+{% endfor %}
+{% endif -%}
+
+{% if data.ftoptions %}
+{% for o in data.ftoptions.deleted %}
+{% if o.option and o.value %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+OPTIONS ( DROP {{o.option}} {{o.value|qtLiteral}});
+{% endif %}
+{% endfor %}
+
+{% for o in data.ftoptions.added %}
+{% if o.option and o.value %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+OPTIONS (ADD {{o.option}} {{o.value|qtLiteral}});
+{% endif %}
+{% endfor %}
+
+{% for o in data.ftoptions.changed %}
+{% if o.option and o.value %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+OPTIONS (SET {{o.option}} {{o.value|qtLiteral}});
+{% endif %}
+{% endfor %}
+{% endif -%}
+
+{% set seclabels = data.seclabels %}
+{% if 'deleted' in seclabels and seclabels.deleted|length > 0 %}
+{% for r in seclabels.deleted %}
+{{ SECLABLE.DROP(conn, 'FOREIGN TABLE', conn|qtIdent(o_data.basensp, name), r.provider) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in seclabels and seclabels.added|length > 0 %}
+{% for r in seclabels.added %}
+{{ SECLABLE.APPLY(conn, 'FOREIGN TABLE', conn|qtIdent(o_data.basensp, name), r.provider, r.label) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in seclabels and seclabels.changed|length > 0 %}
+{% for r in seclabels.changed %}
+{{ SECLABLE.APPLY(conn, 'FOREIGN TABLE', conn|qtIdent(o_data.basensp, name), r.provider, r.label) }}
+{% endfor %}
+{% endif -%}
+
+{% if data.description %}
+
+COMMENT ON FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ IS {{ data.description|qtLiteral }};
+{% endif -%}
+
+{% if data.basensp %}
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+SET SCHEMA {{ conn|qtIdent(data.basensp) }};
+{% endif %}
+
+{% endif %}
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
new file mode 100644
index 0000000..612df40
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/create.sql
@@ -0,0 +1,57 @@
+{% import 'macros/security.macros' as SECLABLE %}
+{% set is_columns = [] %}
+{% if data %}
+CREATE FOREIGN TABLE {{ conn|qtIdent(data.basensp, data.name) }}
+ ({% if data.columns %}
+{% for c in data.columns %}
+{% if (c.inheritedfrom =='' or c.inheritedfrom == None or c.inheritedfrom == 'None' ) %}
+{% if is_columns.append('1') %}{% endif %}
+{% if not loop.first%} {% endif %}{{conn|qtIdent(c.attname)}} {{conn|qtTypeIdent(c.datatype) }}{% if c.typlen %}({{c.typlen}} {% if c.precision %}, {{c.precision}}{% endif %}){% endif %}
+{% if c.attnotnull %} NOT NULL{% else %} NULL{% endif %}
+{% if c.typdefault %} DEFAULT{{c.typdefault|qtLiteral}}{% endif %}
+{% if c.collname %} COLLATE{{c.collname}}{% endif %}
+{% if not loop.last %},
+{% endif %}
+{% endif %}
+{% endfor -%}
+
+{% if data.constraints %}
+{% for c in data.constraints %}
+{% if is_columns|length > 0 and loop.first %},
+{% endif %}
+ CONSTRAINT {{conn|qtIdent(c.conname)}}{% if c.consrc %} CHECK ({{c.consrc}}){% endif %}{% if c.connoinherit %} NO INHERIT{% endif %}
+{% if not loop.last %},
+{% endif %}
+{% endfor %}
+{% endif %}
+{% endif -%}
+)
+
+{% if data.inherits %}
+ INHERITS ({% for i in data.inherits %}{% if i %}{{i}}{% if not loop.last %}, {% endif %}{% endif %}{% endfor %})
+{% endif %}
+ SERVER {{ conn|qtIdent(data.ftsrvname) }};
+
+{% if data.ftoptions %}
+{% for o in data.ftoptions %}
+{% 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 data.description %}
+COMMENT ON FOREIGN TABLE {{ conn|qtIdent(data.basensp, data.name) }}
+ IS '{{ data.description }}';
+{% endif -%}
+
+{% if data.seclabels %}
+{% for r in data.seclabels %}
+{% if r.label and r.provider %}
+{{ SECLABLE.APPLY(conn, 'FOREIGN TABLE', data.name, r.provider, r.label) }}
+{% endif %}
+{% endfor %}
+{% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/delete.sql
new file mode 100644
index 0000000..5bb0969
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/delete.sql
@@ -0,0 +1,17 @@
+{% if scid and foid %}
+SELECT
+ c.relname AS name, nspname as basensp
+FROM
+ pg_class c
+LEFT OUTER JOIN
+ pg_namespace nsp ON (nsp.oid=c.relnamespace)
+WHERE
+ c.relnamespace = {{scid}}::oid
+AND
+ c.oid = {{foid}}::oid;
+{% endif %}
+
+
+{% if name %}
+DROP FOREIGN TABLE {{ conn|qtIdent(basensp, name) }}{% if cascade%} CASCADE{% endif %};
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_collations.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_collations.sql
new file mode 100644
index 0000000..e8d274b
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_collations.sql
@@ -0,0 +1,9 @@
+SELECT --nspname, collname,
+ CASE WHEN length(nspname) > 0 AND length(collname) > 0 THEN
+ concat(nspname, '."', collname,'"')
+ ELSE '' END AS copy_collation
+FROM
+ pg_collation c, pg_namespace n
+WHERE
+ c.collnamespace=n.oid
+ORDER BY nspname, collname;
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
new file mode 100644
index 0000000..dda5a2b
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_columns.sql
@@ -0,0 +1,34 @@
+SELECT
+ att.attname, att.attndims, att.atttypmod, format_type(t.oid,NULL) AS datatype,
+ att.attnotnull, att.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,
+ (SELECT concat(nmsp_parent.nspname, '.',parent.relname ) as inheritedfrom
+FROM
+ pg_attribute at
+JOIN
+ pg_inherits ph ON ph.inhparent = at.attrelid AND ph.inhrelid = {{foid}}::oid
+JOIN
+ pg_class parent ON ph.inhparent = parent.oid
+JOIN
+ pg_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace
+ WHERE at.attname = att.attname
+ ),
+ (SELECT COUNT(1) from pg_type t2 WHERE t2.typname=t.typname) > 1 AS isdup
+FROM
+ pg_attribute att
+JOIN
+ pg_type t ON t.oid=atttypid
+JOIN
+ pg_namespace nsp ON t.typnamespace=nsp.oid
+LEFT OUTER JOIN
+ pg_type b ON t.typelem=b.oid
+LEFT OUTER JOIN
+ pg_collation cl ON t.typcollation=cl.oid
+LEFT OUTER JOIN
+ pg_namespace cn ON cl.collnamespace=cn.oid
+WHERE
+ att.attrelid={{foid}}::oid
+ AND att.attnum>0
+ORDER by att.attnum;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_constraints.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_constraints.sql
new file mode 100644
index 0000000..fc93549
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_constraints.sql
@@ -0,0 +1,7 @@
+SELECT
+ conname, contype, consrc
+FROM
+ pg_constraint
+WHERE
+ conrelid={{foid}}::oid
+ORDER by conname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_foreign_servers.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_foreign_servers.sql
new file mode 100644
index 0000000..9ec3c2f
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_foreign_servers.sql
@@ -0,0 +1,6 @@
+SELECT
+ srvname
+FROM
+ pg_foreign_server
+ORDER
+ BY srvname
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_oid.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_oid.sql
new file mode 100644
index 0000000..e5be5fe
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_oid.sql
@@ -0,0 +1,21 @@
+{% if basensp %}
+SELECT
+ c.oid, bn.oid as scid
+FROM
+ pg_class c
+JOIN
+ pg_namespace bn ON bn.oid=c.relnamespace
+WHERE
+ bn.nspname = {{ basensp|qtLiteral }}
+ AND c.relname={{ name|qtLiteral }}
+{% else %}
+SELECT
+ c.oid, bn.oid as scid
+FROM
+ pg_class c
+JOIN
+ pg_namespace bn ON bn.oid=c.relnamespace
+WHERE
+ c.relname={{ name|qtLiteral }}
+ AND bn.oid = {{scid}}::oid
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_schemas.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_schemas.sql
new file mode 100644
index 0000000..ba3793c
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_schemas.sql
@@ -0,0 +1,26 @@
+SELECT
+ nsp.nspname
+FROM
+ pg_namespace nsp
+LEFT OUTER JOIN
+ pg_description des ON (des.objoid=nsp.oid AND des.classoid='pg_namespace'::regclass)
+LEFT OUTER JOIN
+ pg_catalog.pg_default_acl dacl ON (dacl.defaclnamespace = nsp.oid)
+WHERE
+ NOT (
+ (nspname = 'pg_catalog' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pg_class' AND relnamespace = nsp.oid LIMIT 1)) OR
+ (nspname = 'pgagent' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'pga_job' AND relnamespace = nsp.oid LIMIT 1)) OR
+ (nspname = 'information_schema' AND EXISTS
+ (SELECT 1 FROM pg_class WHERE relname = 'tables' AND relnamespace = nsp.oid LIMIT 1)) OR
+ (nspname LIKE '_%' AND EXISTS
+ (SELECT 1 FROM pg_proc WHERE proname='slonyversion' AND pronamespace = nsp.oid LIMIT 1))
+ )
+ AND
+ nspname NOT LIKE E'pg\\temp\\%' AND
+ nspname NOT LIKE E'pg\\toast_temp\\%' AND
+ -- ADDED: Because We need to omit system schema except the one on which we are trying to create collation
+ ( nsp.oid = {{ scid }} OR nspname NOT LIKE E'pg\\_%' )
+ORDER BY
+ nspname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_table_columns.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_table_columns.sql
new file mode 100644
index 0000000..e1882db
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_table_columns.sql
@@ -0,0 +1,14 @@
+{% if attrelid %}
+SELECT
+ a.attname, format_type(a.atttypid, NULL) AS datatype,
+ quote_ident(n.nspname)||'.'||quote_ident(c.relname) as inheritedfrom,
+ c.oid as inheritedid
+FROM
+ pg_class c
+JOIN
+ pg_namespace n ON c.relnamespace=n.oid
+JOIN
+ pg_attribute a ON a.attrelid = c.oid AND NOT a.attisdropped AND a.attnum>0
+WHERE
+ c.oid = {{attrelid}}::OID
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_tables.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_tables.sql
new file mode 100644
index 0000000..3085a26
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_tables.sql
@@ -0,0 +1,19 @@
+{% if attrelid %}
+SELECT
+ array_agg(quote_ident(n.nspname) || '.' || quote_ident(c.relname)) as inherits
+FROM
+ pg_class c, pg_namespace n
+WHERE
+ c.relnamespace=n.oid AND c.relkind IN ('r', 'f')
+ AND c.oid in {{attrelid}};
+
+{% else %}
+SELECT
+ c.oid AS id, quote_ident(n.nspname) || '.' || quote_ident(c.relname) as text
+FROM
+ pg_class c, pg_namespace n
+WHERE
+ c.relnamespace=n.oid AND c.relkind IN ('r', 'f')
+ORDER BY
+ n.nspname, c.relname;
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_types.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_types.sql
new file mode 100644
index 0000000..5f6c4fc
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/get_types.sql
@@ -0,0 +1,16 @@
+SELECT
+ *
+FROM
+ (
+ SELECT format_type(t.oid,NULL) AS typname, CASE WHEN typelem > 0 THEN typelem ELSE t.oid END as elemoid, typlen, typtype, t.oid, nspname,
+ (SELECT COUNT(1) FROM pg_type t2 WHERE t2.typname = t.typname) > 1 AS isdup
+ FROM
+ pg_type t
+ JOIN
+ pg_namespace nsp ON typnamespace=nsp.oid
+ WHERE
+ (NOT (typname = 'unknown' AND nspname = 'pg_catalog')) AND
+ typisdefined AND typtype IN ('b', 'c', 'd', 'e', 'r')AND NOT EXISTS (select 1 from pg_class where relnamespace=typnamespace and relname = typname and relkind != 'c') AND (typname not like '_%' OR NOT EXISTS (select 1 from pg_class where relnamespace=typnamespace and relname = substring(typname from 2)::name and relkind != 'c'))
+ ) AS dummy
+ORDER BY
+ nspname <> 'pg_catalog', nspname <> 'public', nspname, 1;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/node.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/node.sql
new file mode 100644
index 0000000..2d3a38b
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/node.sql
@@ -0,0 +1,12 @@
+SELECT
+ c.oid, c.relname AS name, pg_get_userbyid(relowner) AS owner,
+ ftoptions, nspname as basensp
+FROM
+ pg_class c
+JOIN
+ pg_foreign_table ft ON c.oid=ft.ftrelid
+LEFT OUTER JOIN
+ pg_namespace nsp ON (nsp.oid=c.relnamespace)
+WHERE
+ c.relnamespace = {{scid}}::oid
+ORDER BY c.relname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/properties.sql
new file mode 100644
index 0000000..22a69d6
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/properties.sql
@@ -0,0 +1,24 @@
+SELECT
+ c.oid, c.relname AS name, pg_get_userbyid(relowner) AS owner,
+ ftoptions, srvname AS ftsrvname, description, nspname as basensp,
+ (SELECT array_agg(label) FROM pg_seclabels sl1 WHERE sl1.objoid=c.oid) AS labels,
+ (SELECT array_agg(provider) FROM pg_seclabels sl2 WHERE sl2.objoid=c.oid) AS providers {% if foid %},
+ (SELECT array_agg(i.inhparent) FROM pg_inherits i
+WHERE
+ i.inhrelid = {{foid}}::oid GROUP BY i.inhrelid) as inherits {% endif %}
+FROM
+ pg_class c
+JOIN
+ pg_foreign_table ft ON c.oid=ft.ftrelid
+LEFT OUTER JOIN
+ pg_foreign_server fs ON ft.ftserver=fs.oid
+LEFT OUTER JOIN
+ pg_description des ON (des.objoid=c.oid AND des.classoid='pg_class'::regclass)
+LEFT OUTER JOIN
+ pg_namespace nsp ON (nsp.oid=c.relnamespace)
+WHERE
+ c.relnamespace = {{scid}}::oid
+ {% if foid %}
+ AND c.oid = {{foid}}::oid
+ {% endif %}
+ORDER BY c.relname;
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
new file mode 100644
index 0000000..51453b0
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/update.sql
@@ -0,0 +1,136 @@
+{% import 'macros/security.macros' as SECLABLE %}
+{% if data %}
+{% set name = o_data.name %}
+{% if data.name %}
+{% if data.name != o_data.name %}
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, o_data.name) }}
+RENAME TO {{ conn|qtIdent(data.name) }};
+{% set name = data.name %}
+{% endif %}
+{% endif -%}
+
+{% if data.owner %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+OWNER TO {{ data.owner }};
+{% endif -%}
+
+{% if data.columns %}
+{% for c in data.columns.deleted %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+DROP COLUMN {{conn|qtIdent(c.attname)}};
+{% endfor -%}
+
+{% for c in data.columns.added %}
+{% if (c.inheritedfrom =='' or c.inheritedfrom == None or c.inheritedfrom == 'None' ) %}
+
+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.attnotnull %} NOT NULL {% else %} NULL {% endif %}
+{% if c.typdefault %} DEFAULT {{c.typdefault|qtLiteral}}{% endif %}
+{% if c.collname %} COLLATE {{c.collname}}{% endif %};
+{% endif %}
+{% endfor -%}
+
+{% for c in data.columns.changed %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+DROP COLUMN {{conn|qtIdent(o_data['columns'][c.attnum]['attname'])}};
+
+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.attnotnull %} NOT NULL {% else %} NULL {% endif %}
+{% if c.typdefault %} DEFAULT {{c.typdefault|qtLiteral}}{% endif %}
+{% if c.collname %} COLLATE {{c.collname}}{% endif %};
+{% endfor %}
+
+{% endif -%}
+
+{% if data.inherits and data.inherits|length > 0%}
+{% for i in data.inherits %}{% if i not in o_data.inherits %}{% if i %}
+
+ALTER FOREIGN TABLE{{ conn|qtIdent(o_data.basensp, name) }} INHERIT {{i}};{% endif %}{% endif %}{% endfor %}
+{% endif -%}
+
+{% if o_data.inherits and 'inherits' in data %}
+{% if data.inherits == None or data.inherits == 'None' %}
+{% set inherits = '' %}
+{% else %}
+{% set inherits = data.inherits %}
+{% endif %}
+{% for i in o_data.inherits %}{% if i not in inherits %}{% if i %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }} NO INHERIT {{i}};{% endif %}{% endif %}{% endfor %}
+{% endif -%}
+
+{% if data.constraints %}
+{% for c in data.constraints.deleted %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+DROP CONSTRAINT {{conn|qtIdent(c.conname)}};
+{% endfor -%}
+
+{% for c in data.constraints.added %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+ADD CONSTRAINT {{conn|qtIdent(c.conname)}} {% if c.consrc %} CHECK ({{c.consrc}}){% endif %} {% if c.connoinherit %} NO INHERIT{% endif %};
+{% endfor %}
+
+{% endif -%}
+
+
+{% if data.ftoptions %}
+{% for o in data.ftoptions.deleted %}
+{% if o.option and o.value %}
+
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+OPTIONS ( DROP {{o.option}} {{o.value|qtLiteral}});
+{% endif %}
+{% endfor -%}
+
+{% for o in data.ftoptions.added %}
+{% if o.option and o.value %}
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+OPTIONS (ADD {{o.option}} {{o.value|qtLiteral}});
+{% endif %}
+{% endfor %}
+
+{% for o in data.ftoptions.changed %}
+{% if o.option and o.value %}
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+OPTIONS (SET {{o.option}} {{o.value|qtLiteral}});
+{% endif %}
+{% endfor %}
+
+{% endif -%}
+
+{% set seclabels = data.seclabels %}
+{% if 'deleted' in seclabels and seclabels.deleted|length > 0 %}
+{% for r in seclabels.deleted %}
+{{ SECLABLE.DROP(conn, 'FOREIGN TABLE', conn|qtIdent(o_data.basensp, name), r.provider) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in seclabels and seclabels.added|length > 0 %}
+{% for r in seclabels.added %}
+{{ SECLABLE.APPLY(conn, 'FOREIGN TABLE', conn|qtIdent(o_data.basensp, name), r.provider, r.label) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in seclabels and seclabels.changed|length > 0 %}
+{% for r in seclabels.changed %}
+{{ SECLABLE.APPLY(conn, 'FOREIGN TABLE', conn|qtIdent(o_data.basensp, name), r.provider, r.label) }}
+{% endfor %}
+{% endif -%}
+
+{% if data.description %}
+
+COMMENT ON FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+IS {{ data.description|qtLiteral }};
+{% endif -%}
+
+{% if data.basensp %}
+ALTER FOREIGN TABLE {{ conn|qtIdent(o_data.basensp, name) }}
+SET SCHEMA {{ conn|qtIdent(data.basensp) }};
+{% endif %}
+
+{% endif %}
--
Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers
