Hi,
Attached patch adds support for Nested Table & Varay Type creation in
Advance Server.(Supported by version > PPAS 96 )
Thanks,
Yogesh Mahajan
EnterpriseDB
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/__init__.py
index 143dc934f..46efe57f5 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/__init__.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/__init__.py
@@ -246,14 +246,12 @@ class TypeView(PGChildNodeView, DataTypeReader, SchemaDiffObjectCompare):
# Declare allows acl on type
self.acl = ['U']
- ver = self.manager.version
- server_type = self.manager.server_type
- # Set the template path for the SQL scripts
- self.template_path = 'types/sql/' + (
- '#{0}#{1}#'.format(server_type, ver)
- if server_type == 'gpdb' else
- '#{0}#'.format(ver)
- )
+ self.template_path = "/".join([
+ 'types',
+ self.manager.server_type,
+ 'sql',
+ '#{0}#'
+ ]).format(self.manager.version)
return f(*args, **kwargs)
@@ -472,6 +470,31 @@ class TypeView(PGChildNodeView, DataTypeReader, SchemaDiffObjectCompare):
return res
+ def _additional_properties_advanced_server_type(self, data):
+ """
+ Used by additional_properties internally for advanced server types.
+ :param rows: list of data
+ :return: formatted response
+ """
+ is_tlength, is_precision, typeval = \
+ self.get_length_precision(data.get('elemoid', None))
+
+ # Split length, precision from type name for grid
+ t_len, t_prec = DataTypeReader.parse_length_precision(
+ data['fulltype'], is_tlength, is_precision)
+
+ data = self.convert_length_precision_to_string(data)
+ data['type'] = self._cltype_formatter(data['type'])
+ data['cltype'] = self._cltype_formatter(data['type'])
+ data['hasSqrBracket'] = self.hasSqrBracket
+ data['tlength'] = t_len,
+ data['precision'] = t_prec
+ data['is_tlength'] = is_tlength,
+ data['is_precision'] = is_precision,
+ data['maxsize'] = data['typndims']
+
+ return data
+
def additional_properties(self, copy_dict, tid):
"""
We will use this function to add additional properties according to
@@ -485,13 +508,13 @@ class TypeView(PGChildNodeView, DataTypeReader, SchemaDiffObjectCompare):
of_type = copy_dict['typtype']
res = dict()
- render_args = {'type': of_type}
+ render_args = {'typtype': of_type}
if of_type == 'c':
render_args['typrelid'] = copy_dict['typrelid']
else:
render_args['tid'] = tid
- if of_type in ('c', 'e', 'r'):
+ if of_type in ('c', 'e', 'r', 'N', 'V', 'A'):
SQL = render_template("/".join([self.template_path,
'additional_properties.sql']),
**render_args)
@@ -505,6 +528,11 @@ class TypeView(PGChildNodeView, DataTypeReader, SchemaDiffObjectCompare):
# To display in properties
res = self._additional_properties_composite(rset['rows'])
+ if of_type in ('N', 'V'):
+ # To display in properties
+ res = self._additional_properties_advanced_server_type(
+ rset['rows'][0])
+
# If type is of ENUM then we need to add labels in our output
if of_type == 'e':
# To display in properties
@@ -1003,6 +1031,12 @@ class TypeView(PGChildNodeView, DataTypeReader, SchemaDiffObjectCompare):
each_type['type'])
each_type['hasSqrBracket'] = self.hasSqrBracket
+ of_type = data.get('typtype', None)
+ if of_type in ('N', 'V'):
+ data = self.convert_length_precision_to_string(data)
+ data['cltype'] = self._cltype_formatter(data['type'])
+ data['hasSqrBracket'] = self.hasSqrBracket
+
SQL = render_template("/".join([self.template_path,
self._CREATE_SQL]),
data=data, conn=self.conn)
@@ -1281,6 +1315,15 @@ class TypeView(PGChildNodeView, DataTypeReader, SchemaDiffObjectCompare):
each_type['type'])
each_type['hasSqrBracket'] = self.hasSqrBracket
+ of_type = data.get('typtype', None)
+ if of_type in ('N', 'V', 'A'):
+ data = self.convert_length_precision_to_string(data)
+ data['cltype'] = self._cltype_formatter(data['type'])
+ data['hasSqrBracket'] = self.hasSqrBracket
+ if of_type == 'V':
+ data['typndims'] = data['maxsize']
+ # data['maxsize'] = data['typndims']
+
SQL = render_template("/".join([self.template_path,
self._CREATE_SQL]),
data=data, conn=self.conn, is_sql=is_sql)
@@ -1307,6 +1350,16 @@ class TypeView(PGChildNodeView, DataTypeReader, SchemaDiffObjectCompare):
each_type['type'])
each_type['hasSqrBracket'] = self.hasSqrBracket
+ of_type = data.get('typtype', None)
+ if of_type in ('N', 'V', 'A'):
+ data = self.convert_length_precision_to_string(data)
+ data['cltype'] = self._cltype_formatter(data['type'])
+ data['hasSqrBracket'] = self.hasSqrBracket
+
+ if of_type == 'V':
+ data['typndims'] = data['maxsize']
+ # data['maxsize'] = data['typndims']
+
SQL = render_template(
"/".join([self.template_path,
self._PROPERTIES_SQL]),
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/static/js/type.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/static/js/type.js
index 5c4b869fd..f0196a61f 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/static/js/type.js
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/static/js/type.js
@@ -343,13 +343,21 @@ define('pgadmin.node.type', [
group: gettext('Definition'),
select2: { allowClear: false },
options: function() {
- return [
+ var typetype = [
{label: gettext('Composite'), value: 'c'},
{label: gettext('Enumeration'), value: 'e'},
{label: gettext('External'), value: 'b'},
{label: gettext('Range'), value: 'r'},
{label: gettext('Shell'), value: 'p'},
];
+ if (this.node_info.server.server_type === 'ppas' &&
+ this.node_info.server.version >= 100000){
+ typetype.push(
+ {label: gettext('Nested Table'), value: 'N'},
+ {label: gettext('Varying Array'), value: 'V'}
+ );
+ }
+ return typetype;
},
// If create mode then by default open composite type
control: Backform.Select2Control.extend({
@@ -385,6 +393,171 @@ define('pgadmin.node.type', [
visible: function(m) {
return m.get('typtype') === 'e';
},
+ },{
+ type: 'nested',
+ group: gettext('Definition'),
+ label: '',
+ control: 'plain-fieldset',
+ deps: ['typtype'],
+ mode: ['edit', 'create', 'properties'],
+ visible: function (m) {
+ return m.get('typtype') === 'N' || m.get('typtype') === 'V';
+ },
+ schema: [{
+ id: 'type',
+ type: 'text',
+ label: gettext('Data Type'),
+ group: gettext('Definition'),
+ control: 'node-ajax-options',
+ mode: ['edit', 'create','properties'],
+ readonly: 'inEditMode',
+ url: 'get_types',
+ disabled: false,
+ node: 'type',
+ cache_node: 'domain',
+ editable: true,
+ deps: ['typtype'],
+ cell: 'node-ajax-options',
+ select2: { allowClear: false },
+ transform: function (d, control) {
+ var data_types = [];
+ _.each(d, function (o) {
+ if (!(o.value.includes('[]'))) {
+ data_types.push(o);
+ }
+ });
+ control.model.type_options = data_types;
+ return data_types;
+ },
+ },{
+ id: 'maxsize',
+ group: gettext('Definition'),
+ label: gettext('Size'),
+ type: 'int',
+ deps: ['typtype'],
+ cell: IntegerDepCell,
+ mode: ['create', 'edit','properties'],
+ readonly: 'inEditMode',
+ visible: function (m) {
+ return m.get('typtype') === 'V';
+ }
+ },{
+ // Note: There are ambiguities in the PG catalogs and docs between
+ // precision and scale. In the UI, we try to follow the docs as
+ // closely as possible, therefore we use Length/Precision and Scale
+ id: 'tlength',
+ group: gettext('Data Type'),
+ label: gettext('Length/Precision'),
+ mode: ['edit', 'create','properties'],
+ deps: ['type'],
+ type: 'text',
+ readonly: 'inEditMode',
+ cell: IntegerDepCell,
+ visible: function (m) {
+ return m.get('typtype') === 'N';
+ },
+ disabled: function (m) {
+ var of_type = m.get('type'),
+ flag = true;
+ _.each(m.type_options, function (o) {
+ if (of_type == o.value) {
+ if (o.length) {
+ m.set('min_val_attlen', o.min_val, { silent: true });
+ m.set('max_val_attlen', o.max_val, { silent: true });
+ flag = false;
+ }
+ }
+ });
+ flag && setTimeout(function () {
+ if (m.get('attlen')) {
+ m.set('attlen', null);
+ }
+ }, 10);
+ return flag;
+ },
+ editable: function (m) {
+ // We will store type from selected from combobox
+ var of_type = m.get('type');
+ if (m.type_options) {
+ // iterating over all the types
+ _.each(m.type_options, function (o) {
+ // if type from selected from combobox matches in options
+ if (of_type == o.value) {
+ // if length is allowed for selected type
+ if (o.length) {
+ // set the values in model
+ m.set('is_tlength', true, { silent: true });
+ m.set('min_val', o.min_val, { silent: true });
+ m.set('max_val', o.max_val, { silent: true });
+ } else {
+ // set the values in model
+ m.set('is_tlength', false, { silent: true });
+ }
+ }
+ });
+ }
+ return m.get('is_tlength');
+ }
+ },{
+ // Note: There are ambiguities in the PG catalogs and docs between
+ // precision and scale. In the UI, we try to follow the docs as
+ // closely as possible, therefore we use Length/Precision and Scale
+ id: 'precision',
+ group: gettext('Data Type'),
+ label: gettext('Scale'),
+ mode: ['edit', 'create','properties'],
+ deps: ['type'],
+ type: 'text',
+ readonly: 'inEditMode',
+ cell: IntegerDepCell,
+ visible: function (m) {
+ return m.get('typtype') === 'N';
+ },
+ disabled: function(m) {
+ var of_type = m.get('type'),
+ flag = true;
+ _.each(m.type_options, function(o) {
+ if ( of_type == o.value ) {
+ if(o.precision) {
+ m.set('min_val_attprecision', 0, {silent: true});
+ m.set('max_val_attprecision', o.max_val, {silent: true});
+ flag = false;
+ }
+ }
+ });
+
+ flag && setTimeout(function() {
+ if(m.get('attprecision')) {
+ m.set('attprecision', null);
+ }
+ },10);
+ return flag;
+ },
+ editable: function(m) {
+ // We will store type from selected from combobox
+ var of_type = m.get('type');
+ if(m.type_options) {
+ // iterating over all the types
+ _.each(m.type_options, function(o) {
+ // if type from selected from combobox matches in options
+ if ( of_type == o.value ) {
+ // if precession is allowed for selected type
+ if(o.precision)
+ {
+ // set the values in model
+ m.set('is_precision', true, {silent: true});
+ m.set('min_val', o.min_val, {silent: true});
+ m.set('max_val', o.max_val, {silent: true});
+ } else {
+ // set the values in model
+ m.set('is_precision', false, {silent: true});
+ }
+ }
+ });
+ }
+ return m.get('is_precision');
+ },
+ }]
},{
// We will disable range type control in edit mode
type: 'nested', control: 'plain-fieldset', group: gettext('Definition'),
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/default/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/default/acl.sql
similarity index 100%
rename from web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/default/acl.sql
rename to web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/default/acl.sql
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/default/additional_properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/default/additional_properties.sql
similarity index 95%
rename from web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/default/additional_properties.sql
rename to web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/default/additional_properties.sql
index fd97d8c37..1f4d45255 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/default/additional_properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/default/additional_properties.sql
@@ -1,5 +1,5 @@
{# The SQL given below will fetch composite type#}
-{% if type == 'c' %}
+{% if typtype == 'c' %}
SELECT attnum, attname, pg_catalog.format_type(t.oid,NULL) AS typname, attndims, atttypmod, nsp.nspname,
(SELECT COUNT(1) from pg_catalog.pg_type t2 WHERE t2.typname=t.typname) > 1 AS isdup,
collname, nspc.nspname as collnspname, att.attrelid,
@@ -16,7 +16,7 @@ FROM pg_catalog.pg_attribute att
{% endif %}
{# The SQL given below will fetch enum type#}
-{% if type == 'e' %}
+{% if typtype == 'e' %}
SELECT enumlabel
FROM pg_catalog.pg_enum
WHERE enumtypid={{tid}}::oid
@@ -24,7 +24,7 @@ FROM pg_catalog.pg_enum
{% endif %}
{# The SQL given below will fetch range type#}
-{% if type == 'r' %}
+{% if typtype == 'r' %}
SELECT rngsubtype, st.typname,
rngcollation,
CASE WHEN n.nspname IS NOT NULL THEN pg_catalog.concat(pg_catalog.quote_ident(n.nspname), '.', pg_catalog.quote_ident(col.collname)) ELSE col.collname END AS collname,
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/default/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/default/create.sql
similarity index 100%
rename from web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/default/create.sql
rename to web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/default/create.sql
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/default/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/default/delete.sql
similarity index 100%
rename from web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/default/delete.sql
rename to web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/default/delete.sql
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/default/get_collations.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/default/get_collations.sql
similarity index 100%
rename from web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/default/get_collations.sql
rename to web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/default/get_collations.sql
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/default/get_external_functions.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/default/get_external_functions.sql
similarity index 100%
rename from web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/default/get_external_functions.sql
rename to web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/default/get_external_functions.sql
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/default/get_oid.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/default/get_oid.sql
similarity index 100%
rename from web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/default/get_oid.sql
rename to web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/default/get_oid.sql
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/default/get_scid.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/default/get_scid.sql
similarity index 100%
rename from web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/default/get_scid.sql
rename to web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/default/get_scid.sql
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/default/get_subtypes.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/default/get_subtypes.sql
similarity index 100%
rename from web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/default/get_subtypes.sql
rename to web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/default/get_subtypes.sql
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/default/get_types.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/default/get_types.sql
similarity index 100%
rename from web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/default/get_types.sql
rename to web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/default/get_types.sql
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/default/nodes.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/default/nodes.sql
similarity index 100%
rename from web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/default/nodes.sql
rename to web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/default/nodes.sql
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/default/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/default/properties.sql
similarity index 100%
rename from web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/default/properties.sql
rename to web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/default/properties.sql
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/default/type_schema_diff.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/default/type_schema_diff.sql
similarity index 100%
rename from web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/default/type_schema_diff.sql
rename to web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/default/type_schema_diff.sql
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/default/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/default/update.sql
similarity index 100%
rename from web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/default/update.sql
rename to web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/default/update.sql
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/gpdb_5.0_plus/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/gpdb_5.0_plus/acl.sql
similarity index 100%
rename from web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/gpdb_5.0_plus/acl.sql
rename to web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/gpdb_5.0_plus/acl.sql
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/gpdb_5.0_plus/additional_properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/gpdb_5.0_plus/additional_properties.sql
similarity index 100%
rename from web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/gpdb_5.0_plus/additional_properties.sql
rename to web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/gpdb_5.0_plus/additional_properties.sql
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/gpdb_5.0_plus/get_collations.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/gpdb_5.0_plus/get_collations.sql
similarity index 100%
rename from web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/gpdb_5.0_plus/get_collations.sql
rename to web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/gpdb_5.0_plus/get_collations.sql
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/gpdb_5.0_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/gpdb_5.0_plus/properties.sql
similarity index 100%
rename from web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/gpdb_5.0_plus/properties.sql
rename to web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/gpdb_5.0_plus/properties.sql
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/postgres_inbuit_types.txt b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/postgres_inbuit_types.txt
similarity index 100%
rename from web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/sql/postgres_inbuit_types.txt
rename to web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/pg/sql/postgres_inbuit_types.txt
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/acl.sql
new file mode 100644
index 000000000..54dc1eed1
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/acl.sql
@@ -0,0 +1,27 @@
+SELECT 'typacl' as deftype, COALESCE(gt.rolname, 'PUBLIC') grantee, g.rolname grantor, pg_catalog.array_agg(privilege_type) as privileges, pg_catalog.array_agg(is_grantable) as grantable
+FROM
+ (SELECT
+ d.grantee, d.grantor, d.is_grantable,
+ CASE d.privilege_type
+ WHEN 'USAGE' THEN 'U'
+ ELSE 'UNKNOWN'
+ END AS privilege_type
+ FROM
+ (SELECT t.typacl
+ FROM pg_catalog.pg_type t
+ LEFT OUTER JOIN pg_catalog.pg_type e ON e.oid=t.typelem
+ LEFT OUTER JOIN pg_catalog.pg_class ct ON ct.oid=t.typrelid AND ct.relkind <> 'c'
+ LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=t.oid AND des.classoid='pg_type'::regclass)
+ WHERE t.typtype != 'd' AND t.typname NOT LIKE E'\\_%' AND t.typnamespace = {{scid}}::oid
+ {% if tid %}
+ AND t.oid = {{tid}}::oid
+ {% endif %}
+ ) acl,
+ (SELECT (d).grantee AS grantee, (d).grantor AS grantor, (d).is_grantable
+ AS is_grantable, (d).privilege_type AS privilege_type FROM (SELECT
+ pg_catalog.aclexplode(t.typacl) as d FROM pg_catalog.pg_type t WHERE t.oid = {{tid}}::oid) a ORDER BY privilege_type) d
+ ) d
+ LEFT JOIN pg_catalog.pg_roles g ON (d.grantor = g.oid)
+ LEFT JOIN pg_catalog.pg_roles gt ON (d.grantee = gt.oid)
+GROUP BY g.rolname, gt.rolname
+ORDER BY grantee
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/additional_properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/additional_properties.sql
new file mode 100644
index 000000000..8379a23e8
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/additional_properties.sql
@@ -0,0 +1,58 @@
+{# The SQL given below will fetch composite type#}
+{% if typtype == 'c' %}
+SELECT attnum, attname, pg_catalog.format_type(t.oid,NULL) AS typname, attndims, atttypmod, nsp.nspname,
+ (SELECT COUNT(1) from pg_catalog.pg_type t2 WHERE t2.typname=t.typname) > 1 AS isdup,
+ collname, nspc.nspname as collnspname, att.attrelid,
+ pg_catalog.format_type(t.oid, att.atttypmod) AS fulltype,
+ CASE WHEN t.typelem > 0 THEN t.typelem ELSE t.oid END as elemoid
+FROM pg_catalog.pg_attribute att
+ JOIN pg_catalog.pg_type t ON t.oid=atttypid
+ JOIN pg_catalog.pg_namespace nsp ON t.typnamespace=nsp.oid
+ LEFT OUTER JOIN pg_catalog.pg_type b ON t.typelem=b.oid
+ LEFT OUTER JOIN pg_catalog.pg_collation c ON att.attcollation=c.oid
+ LEFT OUTER JOIN pg_catalog.pg_namespace nspc ON c.collnamespace=nspc.oid
+ WHERE att.attrelid = {{typrelid}}::oid
+ ORDER by attnum;
+{% endif %}
+
+{# The SQL given below will fetch enum type#}
+{% if typtype == 'e' %}
+SELECT enumlabel
+FROM pg_catalog.pg_enum
+ WHERE enumtypid={{tid}}::oid
+ ORDER by enumsortorder
+{% endif %}
+
+{# The SQL given below will fetch range type#}
+{% if typtype == 'r' %}
+SELECT rngsubtype, st.typname,
+ rngcollation,
+ CASE WHEN n.nspname IS NOT NULL THEN pg_catalog.concat(pg_catalog.quote_ident(n.nspname), '.', pg_catalog.quote_ident(col.collname)) ELSE col.collname END AS collname,
+ rngsubopc, opc.opcname,
+ rngcanonical, rngsubdiff
+FROM pg_catalog.pg_range
+ LEFT JOIN pg_catalog.pg_type st ON st.oid=rngsubtype
+ LEFT JOIN pg_catalog.pg_collation col ON col.oid=rngcollation
+ LEFT JOIN pg_catalog.pg_namespace n ON col.collnamespace=n.oid
+ LEFT JOIN pg_catalog.pg_opclass opc ON opc.oid=rngsubopc
+ WHERE rngtypid={{tid}}::oid;
+{% endif %}
+
+{# The SQL given below will fetch enum type#}
+{% if typtype == 'N' or typtype == 'V' %}
+SELECT t.typname AS typname,
+ CASE WHEN t.typelem > 0 THEN t.typelem ELSE t.oid END AS elemoid,
+ t.typtypmod,
+ t.typtype,
+ t.typndims,
+ pg_catalog.format_type(e.oid,NULL) AS type,
+ pg_catalog.format_type(e.oid, t.typtypmod) AS fulltype,
+ nsp.nspname as typnspname,
+ e.typname as type,
+ (SELECT COUNT(1) FROM pg_catalog.pg_type t2 WHERE t2.typname = t.typname) > 1 AS isdup,
+ CASE WHEN t.typcollation != 0 THEN TRUE ELSE FALSE END AS is_collatable
+FROM pg_catalog.pg_type t
+ LEFT OUTER JOIN pg_catalog.pg_namespace nsp ON typnamespace=nsp.oid
+ LEFT OUTER JOIN pg_catalog.pg_type e ON e.oid=t.typelem
+WHERE t.oid={{tid}}::oid;
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/create.sql
new file mode 100644
index 000000000..691735732
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/create.sql
@@ -0,0 +1,94 @@
+{% import 'macros/schemas/security.macros' as SECLABEL %}
+{% import 'macros/schemas/privilege.macros' as PRIVILEGE %}
+{% import 'types/macros/get_full_type_sql_format.macros' as GET_TYPE %}
+{## If user selected shell type then just create type template ##}
+{% if data and data.typtype == 'p' %}
+CREATE TYPE {{ conn|qtIdent(data.schema, data.name) }};
+{% endif %}
+{### Composite Type ###}
+{% if data and data.typtype == 'c' %}
+CREATE TYPE {% if data.schema %}{{ conn|qtIdent(data.schema, data.name) }}{% else %}{{ conn|qtIdent(data.name) }}{% endif %} AS
+({{"\n\t"}}{% if data.composite %}{% for d in data.composite %}{% if loop.index != 1 %},{{"\n\t"}}{% endif %}{{ conn|qtIdent(d.member_name) }} {% if is_sql %}{{ d.fulltype }}{% else %}{{ GET_TYPE.CREATE_TYPE_SQL(conn, d.cltype, d.tlength, d.precision, d.hasSqrBracket) }}{% endif %}{% if d.collation %} COLLATE {{d.collation}}{% endif %}{% endfor %}{% endif %}{{"\n"}});
+{% endif %}
+{### Enum Type ###}
+{% if data and data.typtype == 'e' %}
+CREATE TYPE {% if data.schema %}{{ conn|qtIdent(data.schema, data.name) }}{% else %}{{ conn|qtIdent(data.name) }}{% endif %} AS ENUM
+ ({% for e in data.enum %}{% if loop.index != 1 %}, {% endif %}{{ e.label|qtLiteral }}{% endfor %});
+{% endif %}
+{### Range Type ###}
+{% if data and data.typtype == 'r' %}
+CREATE TYPE {% if data.schema %}{{ conn|qtIdent(data.schema, data.name) }}{% else %}{{ conn|qtIdent(data.name) }}{% endif %} AS RANGE
+(
+ {% if data.typname %}SUBTYPE={{ conn|qtTypeIdent(data.typname) }}{% endif %}{% if data.collname %},
+ COLLATION = {{ data.collname }}{% endif %}{% if data.opcname %},
+ SUBTYPE_OPCLASS = {{ data.opcname }}{% endif %}{% if data.rngcanonical %},
+ CANONICAL = {{ data.rngcanonical }}{% endif %}{% if data.rngsubdiff %},
+ SUBTYPE_DIFF = {{ data.rngsubdiff }}{% endif %}
+
+);
+{% endif %}
+{### External Type ###}
+{% if data and data.typtype == 'b' %}
+CREATE TYPE {% if data.schema %}{{ conn|qtIdent(data.schema, data.name) }}{% else %}{{ conn|qtIdent(data.name) }}{% endif %}
+
+(
+ {% if data.typinput %}INPUT = {{data.typinput}}{% endif %}{% if data.typoutput %},
+ OUTPUT = {{ data.typoutput }}{% endif %}{% if data.typreceive %},
+ RECEIVE = {{data.typreceive}}{% endif %}{% if data.typsend %},
+ SEND = {{data.typsend}}{% endif %}{% if data.typmodin %},
+ TYPMOD_IN = {{data.typmodin}}{% endif %}{% if data.typmodout %},
+ TYPMOD_OUT = {{data.typmodout}}{% endif %}{% if data.typanalyze %},
+ ANALYZE = {{data.typanalyze}}{% endif %}{% if data.typlen %},
+ INTERNALLENGTH = {{data.typlen}}{% endif %}{% if data.typbyval %},
+ PASSEDBYVALUE{% endif %}{% if data.typalign %},
+ ALIGNMENT = {{data.typalign}}{% endif %}{% if data.typstorage %},
+ STORAGE = {{data.typstorage}}{% endif %}{% if data.typcategory %},
+ CATEGORY = {{data.typcategory|qtLiteral}}{% endif %}{% if data.typispreferred %},
+ PREFERRED = {{data.typispreferred}}{% endif %}{% if data.typdefault %},
+ DEFAULT = {{data.typdefault|qtLiteral}}{% endif %}{% if data.element %},
+ ELEMENT = {{data.element}}{% endif %}{% if data.typdelim %},
+ DELIMITER = {{data.typdelim|qtLiteral}}{% endif %}{% if data.is_collatable %},
+ COLLATABLE = {{data.is_collatable}}{% endif %}
+
+);
+{% endif %}
+{### Nested-table Type ###}
+{% if data and data.typtype == 'N' %}
+CREATE TYPE {% if data.schema %}{{ conn|qtIdent(data.schema, data.name) }}{% else %}{{ conn|qtIdent(data.name) }}{% endif %} AS TABLE OF
+ {% if is_sql %}{{ data.fulltype }}{% else %}{{ GET_TYPE.CREATE_TYPE_SQL(conn, data.cltype, data.tlength, data.precision, data.hasSqrBracket) }}{% endif %};
+{% endif %}
+{### VARRAY Type ###}
+{% if data and data.typtype == 'V' %}
+CREATE TYPE {% if data.schema %}{{ conn|qtIdent(data.schema, data.name) }}{% else %}{{ conn|qtIdent(data.name) }}{% endif %} AS VARRAY({{data.maxsize}}) OF
+ {% if is_sql %}{{ data.fulltype }}{% else %}{{ GET_TYPE.CREATE_TYPE_SQL(conn, data.cltype, data.tlength, data.precision, data.hasSqrBracket) }}{% endif %};
+{% endif %}
+
+{### Type Owner ###}
+{% if data and data.typeowner %}
+ALTER TYPE {% if data.schema %}{{ conn|qtIdent(data.schema, data.name) }}{% else %}{{ conn|qtIdent(data.name) }}{% endif %}
+
+ OWNER TO {{ conn|qtIdent(data.typeowner) }};
+{% endif %}
+{### Type Comments ###}
+{% if data and data.description %}
+
+COMMENT ON TYPE {% if data.schema %}{{ conn|qtIdent(data.schema, data.name) }}{% else %}{{ conn|qtIdent(data.name) }}{% endif %}
+
+ IS {{data.description|qtLiteral}};
+{% endif %}
+{### ACL ###}
+{% if data.typacl %}
+
+{% for priv in data.typacl %}
+{{ PRIVILEGE.SET(conn, 'TYPE', priv.grantee, data.name, priv.without_grant, priv.with_grant, data.schema) }}
+{% endfor %}
+{% endif %}
+{### Security Lables ###}
+{% if data.seclabels %}
+
+{% for r in data.seclabels %}
+{% if r.provider and r.label %}
+{{ SECLABEL.SET(conn, 'TYPE', data.name, r.provider, r.label, data.schema) }}
+{% endif %}
+{% endfor %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/delete.sql
new file mode 100644
index 000000000..c25882706
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/delete.sql
@@ -0,0 +1 @@
+DROP TYPE {{ conn|qtIdent(data.schema, data.name) }}{% if cascade%} CASCADE{% endif %};
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/get_collations.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/get_collations.sql
new file mode 100644
index 000000000..2b5cb534c
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/get_collations.sql
@@ -0,0 +1,7 @@
+SELECT --nspname, collname,
+ CASE WHEN length(nspname::text) > 0 AND length(collname::text) > 0 THEN
+ pg_catalog.concat(pg_catalog.quote_ident(nspname), '.', pg_catalog.quote_ident(collname))
+ ELSE '' END AS collation
+FROM pg_catalog.pg_collation c, pg_catalog.pg_namespace n
+WHERE c.collnamespace=n.oid
+ORDER BY nspname, collname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/get_external_functions.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/get_external_functions.sql
new file mode 100644
index 000000000..425df8845
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/get_external_functions.sql
@@ -0,0 +1,42 @@
+{### Input/Output/Send/Receive/Analyze function list also append into TypModeIN/TypModOUT ###}
+{% if extfunc %}
+SELECT proname, nspname,
+ CASE WHEN (length(nspname::text) > 0 AND nspname != 'public') and length(proname::text) > 0 THEN
+ pg_catalog.concat(pg_catalog.quote_ident(nspname), '.', pg_catalog.quote_ident(proname))
+ WHEN length(proname::text) > 0 THEN
+ pg_catalog.quote_ident(proname)
+ ELSE '' END AS func
+FROM (
+ SELECT proname, nspname, max(proargtypes[0]) AS arg0, max(proargtypes[1]) AS arg1
+FROM pg_catalog.pg_proc p
+ JOIN pg_catalog.pg_namespace n ON n.oid=pronamespace
+GROUP BY proname, nspname
+HAVING count(proname) = 1) AS uniquefunc
+WHERE arg0 <> 0 AND (arg1 IS NULL OR arg1 <> 0);
+{% endif %}
+{### TypmodIN list ###}
+{% if typemodin %}
+SELECT proname, nspname,
+ CASE WHEN length(nspname::text) > 0 AND length(proname::text) > 0 THEN
+ pg_catalog.concat(pg_catalog.quote_ident(nspname), '.', pg_catalog.quote_ident(proname))
+ ELSE '' END AS func
+FROM pg_catalog.pg_proc p
+ JOIN pg_catalog.pg_namespace n ON n.oid=pronamespace
+WHERE prorettype=(SELECT oid FROM pg_catalog.pg_type WHERE typname='int4')
+ AND proargtypes[0]=(SELECT oid FROM pg_catalog.pg_type WHERE typname='_cstring')
+ AND proargtypes[1] IS NULL
+ORDER BY nspname, proname;
+{% endif %}
+{### TypmodOUT list ###}
+{% if typemodout %}
+SELECT proname, nspname,
+ CASE WHEN length(nspname::text) > 0 AND length(proname::text) > 0 THEN
+ pg_catalog.concat(pg_catalog.quote_ident(nspname), '.', pg_catalog.quote_ident(proname))
+ ELSE '' END AS func
+FROM pg_catalog.pg_proc p
+ JOIN pg_catalog.pg_namespace n ON n.oid=pronamespace
+WHERE prorettype=(SELECT oid FROM pg_catalog.pg_type WHERE typname='cstring')
+ AND proargtypes[0]=(SELECT oid FROM pg_catalog.pg_type WHERE typname='int4')
+ AND proargtypes[1] IS NULL
+ORDER BY nspname, proname;
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/get_oid.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/get_oid.sql
new file mode 100644
index 000000000..d5282bc10
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/get_oid.sql
@@ -0,0 +1,11 @@
+{# Below will provide oid for newly created type #}
+SELECT t.oid
+FROM pg_catalog.pg_type t
+ LEFT OUTER JOIN pg_catalog.pg_type e ON e.oid=t.typelem
+ LEFT OUTER JOIN pg_catalog.pg_class ct ON ct.oid=t.typrelid AND ct.relkind <> 'c'
+ LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=t.oid AND des.classoid='pg_type'::regclass)
+WHERE t.typtype != 'd' AND t.typname NOT LIKE E'\\_%' AND t.typnamespace = {{scid}}::oid
+{% if data %}
+ AND t.typname = {{data.name|qtLiteral}}
+{% endif %}
+ORDER BY t.typname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/get_scid.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/get_scid.sql
new file mode 100644
index 000000000..65d7661d4
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/get_scid.sql
@@ -0,0 +1,15 @@
+{% if tid %}
+SELECT
+ t.typnamespace as scid
+FROM
+ pg_catalog.pg_type t
+WHERE
+ t.oid = {{tid}}::oid;
+{% else %}
+SELECT
+ ns.oid as scid
+FROM
+ pg_catalog.pg_namespace ns
+WHERE
+ ns.nspname = {{schema|qtLiteral}}::text;
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/get_subtypes.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/get_subtypes.sql
new file mode 100644
index 000000000..f8ba524a2
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/get_subtypes.sql
@@ -0,0 +1,56 @@
+{### To fill subtype combobox ###}
+{% if subtype %}
+SELECT DISTINCT typ.typname AS stype,
+ (CASE WHEN typ.typcollation > 0 THEN true ELSE false END) AS is_collate
+FROM pg_catalog.pg_opclass opc
+ JOIN pg_catalog.pg_type typ ON opc.opcintype = typ.oid
+WHERE opc.opcmethod = 403
+ORDER BY 1
+{% endif %}
+{### To fill subtype opclass combobox ###}
+{% if subtype_opclass and data and data.typname %}
+SELECT opc.opcname
+FROM pg_catalog.pg_opclass opc
+ JOIN pg_catalog.pg_type typ ON opc.opcintype=typ.oid
+ AND typ.typname = {{ data.typname|qtLiteral }}
+WHERE opc.opcmethod = 403
+ORDER BY opcname;
+{% endif %}
+{### To fetch opcinttype from subtype opclass ###}
+{% if get_opcintype and data and data.typname and data.opcname %}
+SELECT opc.opcintype
+FROM pg_catalog.pg_opclass opc
+ JOIN pg_catalog.pg_type typ ON opc.opcintype=typ.oid
+ AND typ.typname = {{ data.typname|qtLiteral }}
+WHERE opc.opcmethod = 403
+ AND opc.opcname = {{ data.opcname|qtLiteral }}
+ORDER BY opcname;
+{% endif %}
+{### To fill subtype diff function combobox ###}
+{% if opcintype %}
+SELECT proname, nspname,
+ CASE WHEN length(nspname::text) > 0 AND length(proname::text) > 0 THEN
+ pg_catalog.concat(quote_ident(nspname), '.', pg_catalog.quote_ident(proname))
+ ELSE '' END AS stypdiff
+FROM pg_catalog.pg_proc
+ JOIN pg_catalog.pg_namespace n ON n.oid=pronamespace
+WHERE prorettype = 701
+ AND proargtypes = '{{opcintype}} {{opcintype}}'
+ORDER BY proname;
+{% endif %}
+{### To fill canonical combobox ###}
+{% if getoid %}
+SELECT oid FROM pg_catalog.pg_type
+WHERE typname = {{ data.name|qtLiteral }}
+{% endif %}
+{% if canonical and oid %}
+SELECT proname, nspname,
+ CASE WHEN length(nspname::text) > 0 AND length(proname::text) > 0 THEN
+ pg_catalog.concat(quote_ident(nspname), '.', pg_catalog.quote_ident(proname))
+ ELSE '' END AS canonical
+FROM pg_catalog.pg_proc
+ JOIN pg_catalog.pg_namespace n ON n.oid=pronamespace
+WHERE prorettype= {{ oid }}
+ AND proargtypes = '{{ oid }}'
+ORDER BY proname;
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/get_types.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/get_types.sql
new file mode 100644
index 000000000..4beb57574
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/get_types.sql
@@ -0,0 +1,11 @@
+SELECT * FROM
+ (SELECT pg_catalog.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_catalog.pg_type t2 WHERE t2.typname = t.typname) > 1 AS isdup,
+ CASE WHEN t.typcollation != 0 THEN TRUE ELSE FALSE END AS is_collatable
+FROM pg_catalog.pg_type t
+ JOIN pg_catalog.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_catalog.pg_class where relnamespace=typnamespace and relname = typname and relkind != 'c') AND (typname not like '_%' OR NOT EXISTS (select 1 from pg_catalog.pg_class where relnamespace=typnamespace and relname = substring(typname from 2)::name and relkind != 'c')) AND nsp.nspname != 'information_schema'
+ ) AS dummy
+ORDER BY nspname <> 'pg_catalog', nspname <> 'public', nspname, 1
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/nodes.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/nodes.sql
new file mode 100644
index 000000000..5cab7ef1d
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/nodes.sql
@@ -0,0 +1,13 @@
+SELECT t.oid, t.typname AS name
+FROM pg_catalog.pg_type t
+ LEFT OUTER JOIN pg_catalog.pg_type e ON e.oid=t.typelem
+ LEFT OUTER JOIN pg_catalog.pg_class ct ON ct.oid=t.typrelid AND ct.relkind <> 'c'
+ LEFT OUTER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = t.typnamespace
+WHERE t.typtype != 'd' AND t.typname NOT LIKE E'\\_%' AND t.typnamespace = {{scid}}::oid
+{% if tid %}
+ AND t.oid = {{tid}}::oid
+{% endif %}
+{% if not show_system_objects %}
+ AND ct.oid is NULL
+{% endif %}
+ORDER BY t.typname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/properties.sql
new file mode 100644
index 000000000..09728bf20
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/properties.sql
@@ -0,0 +1,24 @@
+SELECT t.oid, t.typname AS name,
+ (CASE WHEN CAST(coalesce(t.typcollation, '0') AS integer) = 100 THEN true ElSE false END) AS is_collatable,
+ t.typacl AS type_acl,
+ t.*, pg_catalog.format_type(t.oid, null) AS alias,
+ pg_catalog.pg_get_userbyid(t.typowner) as typeowner, e.typname as element,
+ description, ct.oid AS taboid,
+ nsp.nspname AS schema,
+ --MinimumVersion 9.1 START
+ (SELECT pg_catalog.array_agg(provider || '=' || label) FROM pg_catalog.pg_seclabel sl1 WHERE sl1.objoid=t.oid) AS seclabels,
+ -- END
+ (CASE WHEN (t.oid <= {{ datlastsysoid}}::oid OR ct.oid != 0) THEN true ElSE false END) AS is_sys_type
+FROM pg_catalog.pg_type t
+ LEFT OUTER JOIN pg_catalog.pg_type e ON e.oid=t.typelem
+ LEFT OUTER JOIN pg_catalog.pg_class ct ON ct.oid=t.typrelid AND ct.relkind <> 'c'
+ LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=t.oid AND des.classoid='pg_type'::regclass)
+ LEFT OUTER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = t.typnamespace
+WHERE t.typtype != 'd' AND t.typname NOT LIKE E'\\_%' AND t.typnamespace = {{scid}}::oid
+{% if tid %}
+ AND t.oid = {{tid}}::oid
+{% endif %}
+{% if not show_system_objects %}
+ AND ct.oid is NULL
+{% endif %}
+ORDER BY t.typname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/update.sql
new file mode 100644
index 000000000..036ea2a11
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/types/ppas/sql/default/update.sql
@@ -0,0 +1,161 @@
+{% import 'macros/schemas/security.macros' as SECLABEL %}
+{% import 'macros/schemas/privilege.macros' as PRIVILEGE %}
+{% import 'types/macros/get_full_type_sql_format.macros' as GET_TYPE %}
+{#======================================#}
+{# Below will change object owner #}
+{% if data.typeowner and data.typeowner != o_data.typeowner %}
+ALTER TYPE {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ OWNER TO {{ conn|qtIdent(data.typeowner) }};
+
+{% endif %}
+{#======================================#}
+{# Below will change objects comment #}
+{% if data.description is defined and data.description != o_data.description %}
+COMMENT ON TYPE {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ IS {{ data.description|qtLiteral }};
+
+{% endif %}
+{#======================================#}
+{### The sql given below will update composite type ###}
+{% if data.composite and data.composite|length > 0 %}
+{% set composite = data.composite %}
+{% if 'deleted' in composite and composite.deleted|length > 0 %}
+{% for r in composite.deleted %}
+ALTER TYPE {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ DROP ATTRIBUTE {{conn|qtIdent(r.member_name)}};
+{% endfor %}
+{% endif %}
+{% if 'added' in composite and composite.added|length > 0 %}
+{% for r in composite.added %}
+ALTER TYPE {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ ADD ATTRIBUTE {{conn|qtIdent(r.member_name)}} {{ GET_TYPE.CREATE_TYPE_SQL(conn, r.cltype, r.tlength, r.precision, r.hasSqrBracket) }}{% if r.collation %}
+ COLLATE {{r.collation}}{% endif %};
+{% endfor %}
+{% endif %}
+{% if 'changed' in composite and composite.changed|length > 0 %}
+{% for r in composite.changed %}
+{% for o in o_data.composite %}
+{##### Variables for the loop #####}
+{% set member_name = o.member_name %}
+{% set cltype = o.cltype %}
+{% set tlength = o.tlength %}
+{% set precision = o.precision %}
+{% set hasSqrBracket = o.hasSqrBracket %}
+{##### If member name changed #####}
+{% if o.attnum == r.attnum %}
+{% if r.member_name and o.member_name != r.member_name %}
+ALTER TYPE {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ RENAME ATTRIBUTE {{o.member_name}} TO {{r.member_name}};
+{% set member_name = r.member_name %}
+{% endif %}
+{##### If type changed #####}
+{% if r.cltype and cltype != r.cltype %}
+{% set cltype = r.cltype %}
+{% set hasSqrBracket = r.hasSqrBracket %}
+{##### If length is not allowed on type #####}
+{% if not r.is_tlength %}
+{% set tlength = 0 %}
+{% set precision = 0 %}
+{% endif %}
+{% endif %}
+{##### If length changed #####}
+{% if r.tlength and tlength != r.tlength %}
+{% set tlength = r.tlength %}
+{% endif %}
+{##### If precision changed #####}
+{% if tlength and r.precision and precision != r.precision %}
+{% set precision = r.precision %}
+{% endif %}
+ALTER TYPE {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ ALTER ATTRIBUTE {{conn|qtIdent(member_name)}} SET DATA TYPE {{ GET_TYPE.CREATE_TYPE_SQL(conn, cltype, tlength, precision, hasSqrBracket) }}{% if r.collation %}
+ COLLATE {{r.collation}}{% endif %};
+{% endif%}
+{% endfor %}
+{% endfor %}
+{% endif %}
+{% endif %}
+{#======================================#}
+{### The sql given below will update enum type ###}
+{% if data.enum and data.enum|length > 0 %}
+{% set enum = data.enum %}
+{% set o_enum_len = o_data.enum|length %}
+{# We need actual list index from length #}
+{% set o_enum_len = o_enum_len - 1 %}
+{% if 'added' in enum and enum.added|length > 0 %}
+{% for r in enum.added %}
+{% set c_idx = loop.index %}
+{% if c_idx == 1 %}
+{# if first new element then add it after old data enum list#}
+ALTER TYPE {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ ADD VALUE {{r.label|qtLiteral}} {% if o_enum_len > 0 %}AFTER {{o_data.enum[o_enum_len].label|qtLiteral }}{% endif %};
+{% else %}
+{# if first new element then add it after new data enum list#}
+{% set p_idx = loop.index - 2 %}
+ALTER TYPE {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ ADD VALUE {{r.label|qtLiteral}} AFTER {{enum.added[p_idx].label|qtLiteral}};
+{% endif %}
+{% endfor %}
+{% endif %}
+
+{% endif %}
+{#======================================#}
+{# The SQL generated below will change Security Label #}
+{% if data.seclabels and data.seclabels|length > 0 %}
+{% set seclabels = data.seclabels %}
+{% if 'deleted' in seclabels and seclabels.deleted|length > 0 %}
+{% for r in seclabels.deleted %}
+{{ SECLABEL.UNSET(conn, 'TYPE', o_data.name, r.provider, o_data.schema) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in seclabels and seclabels.added|length > 0 %}
+{% for r in seclabels.added %}
+{{ SECLABEL.SET(conn, 'TYPE', o_data.name, r.provider, r.label, o_data.schema) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in seclabels and seclabels.changed|length > 0 %}
+{% for r in seclabels.changed %}
+{{ SECLABEL.SET(conn, 'TYPE', o_data.name, r.provider, r.label, o_data.schema) }}
+{% endfor %}
+{% endif %}
+
+{% endif %}
+{#======================================#}
+{# Change the privileges #}
+{% if data.typacl and data.typacl|length > 0 %}
+{% if 'deleted' in data.typacl %}
+{% for priv in data.typacl.deleted %}
+{{ PRIVILEGE.UNSETALL(conn, 'TYPE', priv.grantee, o_data.name, o_data.schema) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in data.typacl %}
+{% for priv in data.typacl.changed %}
+{% if priv.grantee != priv.old_grantee %}
+{{ PRIVILEGE.UNSETALL(conn, 'TYPE', priv.old_grantee, o_data.name, o_data.schema) }}
+{% else %}
+{{ PRIVILEGE.UNSETALL(conn, 'TYPE', priv.grantee, o_data.name, o_data.schema) }}
+{% endif %}
+{{ PRIVILEGE.SET(conn, 'TYPE', priv.grantee, o_data.name, priv.without_grant, priv.with_grant, o_data.schema) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in data.typacl %}
+{% for priv in data.typacl.added %}
+{{ PRIVILEGE.SET(conn, 'TYPE', priv.grantee, o_data.name, priv.without_grant, priv.with_grant, o_data.schema) }}
+{% endfor %}
+{% endif %}
+{% endif %}
+{#======================================#}
+{# Below will change object name #}
+{% if data.name and data.name != o_data.name %}
+ALTER TYPE {{ conn|qtIdent(o_data.schema, o_data.name) }}
+ RENAME TO {{ conn|qtIdent(data.name) }};
+
+{% endif %}
+{#======================================#}
+{# Below will change the schema for object #}
+{# with extra if condition we will also make sure that object has correct name #}
+{% if data.schema and data.schema != o_data.schema %}
+ALTER TYPE {% if data.name and data.name != o_data.name %}{{ conn|qtIdent(o_data.schema, data.name) }}
+{% else %}{{ conn|qtIdent(o_data.schema, o_data.name) }}
+{% endif %}
+ SET SCHEMA {{ conn|qtIdent(data.schema) }};
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/tests/ppas/default/create_nested_table_type.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/tests/ppas/default/create_nested_table_type.sql
new file mode 100644
index 000000000..82188ce58
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/tests/ppas/default/create_nested_table_type.sql
@@ -0,0 +1,9 @@
+-- Type: nested_table_type_$%{}[]()&*^!@"'`\/#
+
+-- DROP TYPE public."nested_table_type_$%{}[]()&*^!@""'`\/#";
+
+CREATE TYPE public."nested_table_type_$%{}[]()&*^!@""'`\/#" AS TABLE OF
+ numeric(2,1);
+
+ALTER TYPE public."nested_table_type_$%{}[]()&*^!@""'`\/#"
+ OWNER TO enterprisedb;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/tests/ppas/default/create_varray_type.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/tests/ppas/default/create_varray_type.sql
new file mode 100644
index 000000000..b7f01d412
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/tests/ppas/default/create_varray_type.sql
@@ -0,0 +1,9 @@
+-- Type: varray_type_$%{}[]()&*^!@"'`\/#
+
+-- DROP TYPE public."varray_type_$%{}[]()&*^!@""'`\/#";
+
+CREATE TYPE public."varray_type_$%{}[]()&*^!@""'`\/#" AS VARRAY(10) OF
+ integer;
+
+ALTER TYPE public."varray_type_$%{}[]()&*^!@""'`\/#"
+ OWNER TO enterprisedb;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/tests/ppas/default/tests.json b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/tests/ppas/default/tests.json
index cc662721e..94018e027 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/tests/ppas/default/tests.json
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/tests/ppas/default/tests.json
@@ -53,7 +53,45 @@
"data": {}
},
-
+ {
+ "type": "create",
+ "name": "Create Nested Table type",
+ "endpoint": "NODE-type.obj",
+ "sql_endpoint": "NODE-type.sql_id",
+ "msql_endpoint": "NODE-type.msql",
+ "data": {
+ "name": "nested_table_type_$%{}[]()&*^!@\"'`\\/#",
+ "is_sys_type":false,
+ "typtype":"N",
+ "schema":"public",
+ "type": "numeric",
+ "tlength": 2,
+ "precision": 1,
+ "typacl":[],
+ "seclabels":[],
+ "description":""
+ },
+ "expected_sql_file": "create_nested_table_type.sql"
+ },
+ {
+ "type": "create",
+ "name": "Create Varray type",
+ "endpoint": "NODE-type.obj",
+ "sql_endpoint": "NODE-type.sql_id",
+ "msql_endpoint": "NODE-type.msql",
+ "data": {
+ "name": "varray_type_$%{}[]()&*^!@\"'`\\/#",
+ "is_sys_type":false,
+ "typtype":"V",
+ "schema":"public",
+ "type": "integer",
+ "maxsize": 10,
+ "typacl":[],
+ "seclabels":[],
+ "description":""
+ },
+ "expected_sql_file": "create_varray_type.sql"
+ },
{
"type": "create",
"name": "Create Composite type",
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/tests/test_types_add.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/tests/test_types_add.py
index 7a3cbd13a..77b81a430 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/tests/test_types_add.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/tests/test_types_add.py
@@ -19,6 +19,7 @@ from regression import parent_node_dict
from regression.python_test_utils import test_utils as utils
from . import utils as types_utils
from unittest.mock import patch
+from pgadmin.utils import server_utils as server_utils
class TypesAddTestCase(BaseTestGenerator):
@@ -27,10 +28,23 @@ class TypesAddTestCase(BaseTestGenerator):
types_utils.test_cases)
def setUp(self):
+ self.data = self.test_data
self.db_name = parent_node_dict["database"][-1]["db_name"]
schema_info = parent_node_dict["schema"][-1]
self.server_id = schema_info["server_id"]
self.db_id = schema_info["db_id"]
+
+ # Check DB version
+ if "server_min_version" in self.data:
+ server_con = server_utils.connect_server(self, self.server_id)
+ if not server_con["info"] == "Server connected.":
+ raise Exception("Could not connect to server to add "
+ "a table.")
+ if (server_con["data"]["type"] == 'pg' or
+ server_con["data"]["version"] <=
+ self.data["server_min_version"]):
+ self.skipTest(self.data["skip_msg"])
+
db_con = database_utils.connect_database(self, utils.SERVER_GROUP,
self.server_id, self.db_id)
if not db_con['data']["connected"]:
@@ -57,15 +71,17 @@ class TypesAddTestCase(BaseTestGenerator):
def runTest(self):
""" This function will add type under schema node. """
+ if "name" in self.data:
+ self.data["name"] = self.data["name"] + (str(uuid.uuid4())[1:8])
+
db_user = self.server["username"]
- self.type_name = "test_type_add_%s" % (str(uuid.uuid4())[1:8])
- self.data = types_utils.get_types_data(self.type_name,
- self.schema_name, db_user)
+ self.data["typeowner"] = db_user
+ self.data["schema"] = self.schema_name
+
if self.is_positive_test:
response = self.create_types()
else:
if hasattr(self, "missing_parameter"):
- del self.data['name']
response = self.create_types()
if hasattr(self, "internal_server_error"):
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/tests/types_test_data.json b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/tests/types_test_data.json
index 58937e88c..220df84a4 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/tests/types_test_data.json
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/tests/types_test_data.json
@@ -3,8 +3,68 @@
{
"name": "Create types: With valid data.",
"url": "/browser/type/obj/",
+ "server_min_version": "10000",
+ "skip_msg": "Nested Table types are supported for PPAS & version > 9.6",
"is_positive_test": true,
- "test_data": {},
+ "test_data": {
+ "name": "type_name_add",
+ "is_sys_type": false,
+ "typtype": "c",
+ "typeowner": "db_user",
+ "schema": "schema_name",
+ "composite": [{"member_name": "one", "type": "bigint",
+ "is_tlength": false, "is_precision": false},
+ {"member_name": "two", "type": "\"char\"[]",
+ "is_tlength": false, "is_precision": false}],
+ "enum": [], "typacl": [], "seclabels": []
+ },
+ "mocking_required": false,
+ "mock_data": {},
+ "expected_data": {
+ "status_code": 200
+ }
+ },
+ {
+ "name": "Create Nested types: With valid data for Advanced Server.",
+ "url": "/browser/type/obj/",
+ "is_positive_test": true,
+ "test_data": {
+ "name": "type_name_add",
+ "is_sys_type": false,
+ "typeowner": "db_user",
+ "schema": "schema_name",
+ "typtype":"N",
+ "type": "numeric",
+ "tlength": 2,
+ "precision": 1,
+ "typacl":[],
+ "seclabels":[],
+ "server_min_version": 10000,
+ "skip_msg": "Nested Table types are supported for PPAS & version > 9.6"
+ },
+ "mocking_required": false,
+ "mock_data": {},
+ "expected_data": {
+ "status_code": 200
+ }
+ },
+ {
+ "name": "Create Varray types: With valid data for Advanced Server.",
+ "url": "/browser/type/obj/",
+ "is_positive_test": true,
+ "test_data": {
+ "name": "type_name_add",
+ "is_sys_type": false,
+ "typeowner": "db_user",
+ "schema": "schema_name",
+ "typtype":"V",
+ "type": "integer",
+ "maxsize": 10,
+ "typacl":[],
+ "seclabels":[],
+ "server_min_version": 10000,
+ "skip_msg": "Varray types are supported for PPAS & version > 9.6"
+ },
"mocking_required": false,
"mock_data": {},
"expected_data": {
@@ -16,7 +76,17 @@
"url": "/browser/type/obj/",
"is_positive_test": false,
"missing_parameter": true,
- "test_data": {},
+ "test_data": {
+ "is_sys_type": false,
+ "typtype": "c",
+ "typeowner": "db_user",
+ "schema": "schema_name",
+ "composite": [{"member_name": "one", "type": "bigint",
+ "is_tlength": false, "is_precision": false},
+ {"member_name": "two", "type": "\"char\"[]",
+ "is_tlength": false, "is_precision": false}],
+ "enum": [], "typacl": [], "seclabels": []
+ },
"mocking_required": false,
"mock_data": {},
"expected_data": {
@@ -28,7 +98,18 @@
"url": "/browser/type/obj/",
"is_positive_test": false,
"internal_server_error": true,
- "test_data": {},
+ "test_data": {
+ "name": "type_name_add",
+ "is_sys_type": false,
+ "typtype": "c",
+ "typeowner": "db_user",
+ "schema": "schema_name",
+ "composite": [{"member_name": "one", "type": "bigint",
+ "is_tlength": false, "is_precision": false},
+ {"member_name": "two", "type": "\"char\"[]",
+ "is_tlength": false, "is_precision": false}],
+ "enum": [], "typacl": [], "seclabels": []
+ },
"mocking_required": true,
"mock_data": {
"function_name": "pgadmin.utils.driver.psycopg2.connection.Connection.execute_dict",
@@ -43,7 +124,18 @@
"url": "/browser/type/obj/",
"is_positive_test": false,
"error_in_db": true,
- "test_data": {},
+ "test_data": {
+ "name": "type_name_add",
+ "is_sys_type": false,
+ "typtype": "c",
+ "typeowner": "db_user",
+ "schema": "schema_name",
+ "composite": [{"member_name": "one", "type": "bigint",
+ "is_tlength": false, "is_precision": false},
+ {"member_name": "two", "type": "\"char\"[]",
+ "is_tlength": false, "is_precision": false}],
+ "enum": [], "typacl": [], "seclabels": []
+ },
"mocking_required": true,
"mock_data": {
"function_name": "pgadmin.utils.driver.psycopg2.connection.Connection.execute_scalar",
