public inbox for [email protected]
help / color / mirror / Atom feedFrom: Khushboo Vashi <[email protected]>
To: Dave Page <[email protected]>
Cc: pgadmin-hackers <[email protected]>
Subject: Re: [pgAdmin4][Patch]: Fixed RM 2324 - PostGIS datatypes not showing up properly on SQL tab.
Date: Fri, 9 Jun 2017 14:48:12 +0530
Message-ID: <CAFOhELdv7VF4W3qFMcHaeVZ+PbVAkKyo6dOtdjmF+sEwFYp1CQ@mail.gmail.com> (raw)
In-Reply-To: <CA+OCxoxVcz4+t34maYgy+h9ZkC-gE-dnMMQ_oSLcivxTF9k3mg@mail.gmail.com>
References: <CAFOhELd8B=ALRSiZwEb0dVZwN8iGyCYu1=Tzk46A=MA3XTfu3w@mail.gmail.com>
<CAKKotZTUdfgBftsznMamYuLZB8SbK24FviwfQJ-7Hoqk_fnyhQ@mail.gmail.com>
<CAFOhELeXLioibqoyEGeB4EwGk=FC5D0DQqnX2ZnHiWYFrt4YuA@mail.gmail.com>
<CAKKotZSeqiwB8Jo8Hx_wBtp92q0kDF4Z_SN7oCt8abU76zjL9A@mail.gmail.com>
<CAFOhELe-Bmq9Ah=KWBP_2pPfqiYrpAUaP_hKp3-i-8CODEtoqQ@mail.gmail.com>
<CA+OCxoy6mynKBRDGf4LHXXj9mQHpcq0tP-QRqwzxSrEQWUhM2A@mail.gmail.com>
<CAFOhELdfX3u4PzOxmv5-e1E3kjqWZNTCh=7faGzxB-1=J1JRqA@mail.gmail.com>
<CA+OCxow4oGzbgVuXZVFfq56GNyVwR4F63YGpg=71Mamv+n96fQ@mail.gmail.com>
<CAFOhELfZmHJC=FoB5GXwrTaepxD1wQSrX9SyfKkK-Qt4Fs93hQ@mail.gmail.com>
<CA+OCxoxVcz4+t34maYgy+h9ZkC-gE-dnMMQ_oSLcivxTF9k3mg@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgadmin-hackers>
Hi,
Please find the attached updated patch.
Fixed the PostGIS Datatypes in SQL tab, Create / Update dialogues for
Table, Column, Foreign Table and Type node.
Regarding the review comment given by Dave (Also, I there should be a
space between the , and value in the_geom geometry(MultiPolygon,3310).),
won't be fixed as this format is returned by format_type() function itself.
As per the conversation with Dave, I have logged RM #2473
<https://redmine.postgresql.org/issues/2473; to add support for PostGIS
Datatypes.
Thanks,
Khushboo
On Tue, Jun 6, 2017 at 6:49 PM, Dave Page <[email protected]> wrote:
>
>
> On Tue, Jun 6, 2017 at 2:15 PM, Khushboo Vashi <
> [email protected]> wrote:
>
>>
>>
>> On 6 Jun 2017 18:36, "Dave Page" <[email protected]> wrote:
>>
>>
>>
>> On Tue, Jun 6, 2017 at 1:23 PM, Khushboo Vashi <
>> [email protected]> wrote:
>>
>>>
>>>
>>> On 6 Jun 2017 17:42, "Dave Page" <[email protected]> wrote:
>>>
>>> Hi
>>>
>>> The table and column dialogues seem to have related problems. For
>>> example, the table dialogue shows the length of the geometry field in the
>>> test case as 847,380, whilst the column dialogue shows no length at all.
>>>
>>> What should be there in length and precision fields in this case?
>>>
>>
>> I have no idea :-). What did pgAdmin 3 do here? I assume there's some way
>> to decode the typmod into the desired values, and map them back to
>> appropriate SQL.
>>
>> I will try to figure out the way.
>> pgAdmin 3 has the same issue, so I have asked for the suggestion.
>>
>
> Oh, OK - thanks. Maybe we need to reuse the length and precision fields
> for these types. That is, I believe, essentially how PostGIS passes extra
> info to the column definitions.
>
>
>>
>>
>>> Also, I there should be a space between the , and value in the_geom
>>> geometry(MultiPolygon,3310).
>>>
>>> Thanks.
>>>
>>> On Mon, Jun 5, 2017 at 8:32 AM, Khushboo Vashi <
>>> [email protected]> wrote:
>>>
>>>> Hi,
>>>>
>>>> Fixed PostGIS datatype on SQL tab for Table, column, Foreign Table and
>>>> Type node.
>>>>
>>>> Please find the attached updated patch.
>>>>
>>>>
>>>> Thanks,
>>>> Khushboo
>>>>
>>>> On Thu, Jun 1, 2017 at 6:36 PM, Murtuza Zabuawala <
>>>> [email protected]> wrote:
>>>>
>>>>>
>>>>> On Thu, Jun 1, 2017 at 5:27 PM, Khushboo Vashi <
>>>>> [email protected]> wrote:
>>>>>
>>>>>> Hi,
>>>>>>
>>>>>>
>>>>>> On Thu, Jun 1, 2017 at 4:57 PM, Murtuza Zabuawala <
>>>>>> [email protected]> wrote:
>>>>>>
>>>>>>> Hi Khushboo,
>>>>>>>
>>>>>>> Please include Type node and Foreign Table node in your fix :-)
>>>>>>>
>>>>>>> I don't think Type node requires this change.
>>>>>>
>>>>>> Yes, It do require because user can create composite type from
>>>>> existing types.
>>>>> [image: Inline image 1]
>>>>>
>>>>>> Foreign table changes added in the attached updated patch.
>>>>>>
>>>>>>> --
>>>>>>>
>>>>>> Thanks Murtuza for pointing this out.
>>>>
>>>>
>>>>> Regards,
>>>>>>> Murtuza Zabuawala
>>>>>>> EnterpriseDB: http://www.enterprisedb.com
>>>>>>> The Enterprise PostgreSQL Company
>>>>>>>
>>>>>>> On Thu, Jun 1, 2017 at 3:23 PM, Khushboo Vashi <
>>>>>>> [email protected]> wrote:
>>>>>>>
>>>>>>>> Hi,
>>>>>>>>
>>>>>>>> Please find the attached patch to fix RM #2324 : PostGIS datatypes
>>>>>>>> not showing up properly on SQL tab.
>>>>>>>>
>>>>>>>> Thanks,
>>>>>>>> Khushboo
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> --
>>>>>>>> Sent via pgadmin-hackers mailing list (
>>>>>>>> [email protected])
>>>>>>>> To make changes to your subscription:
>>>>>>>> http://www.postgresql.org/mailpref/pgadmin-hackers
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> Sent via pgadmin-hackers mailing list ([email protected])
>>>> To make changes to your subscription:
>>>> http://www.postgresql.org/mailpref/pgadmin-hackers
>>>>
>>>>
>>>
>>>
>>> --
>>> Dave Page
>>> Blog: http://pgsnake.blogspot.com
>>> Twitter: @pgsnake
>>>
>>> EnterpriseDB UK: http://www.enterprisedb.com
>>> The Enterprise PostgreSQL Company
>>>
>>>
>>>
>>
>>
>> --
>> Dave Page
>> Blog: http://pgsnake.blogspot.com
>> Twitter: @pgsnake
>>
>> EnterpriseDB UK: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>>
>>
>>
>
>
> --
> Dave Page
> Blog: http://pgsnake.blogspot.com
> Twitter: @pgsnake
>
> EnterpriseDB UK: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
--
Sent via pgadmin-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers
Attachments:
[image/png] image.png (71.5K, 3-image.png)
download | view image
[text/x-patch] RM_2324_ver3.patch (25.1K, 4-RM_2324_ver3.patch)
download | inline diff:
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/__init__.py
index 15455e8..2ee6a43 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/__init__.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/__init__.py
@@ -830,7 +830,7 @@ class ForeignTableView(PGChildNodeView, DataTypeReader):
data['columns'] = col_data
SQL = render_template("/".join([self.template_path,
- 'create.sql']), data=data)
+ 'create.sql']), data=data, is_sql=True)
sql_header = u"""-- FOREIGN TABLE: {0}
@@ -1093,10 +1093,10 @@ class ForeignTableView(PGChildNodeView, DataTypeReader):
substr = c['fulltype'][c['fulltype'].find("(") + 1:c['fulltype'].find(")")]
typlen = substr.split(",")
if len(typlen) > 1:
- c['typlen'] = int(typlen[0])
- c['precision'] = int(typlen[1])
+ c['typlen'] = int(typlen[0]) if typlen[0].isdigit() else typlen[0]
+ c['precision'] = int(typlen[1]) if typlen[1].isdigit() else typlen[1]
else:
- c['typlen'] = int(typlen[0])
+ c['typlen'] = int(typlen[0]) if typlen[0].isdigit() else typlen[0]
c['precision'] = None
# Get formatted Column Options
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/create.sql
index cbffa89..d055de0 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/create.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.2_plus/create.sql
@@ -4,7 +4,7 @@
CREATE FOREIGN TABLE {{ conn|qtIdent(data.basensp, data.name) }}(
{% if data.columns %}
{% for c in data.columns %}
- {{conn|qtIdent(c.attname)}} {{conn|qtTypeIdent(c.datatype) }}{% if c.typlen %}({{c.typlen}}{% if c.precision %}, {{c.precision}}{% endif %}){% endif %}{% if c.isArrayType %}[]{% endif %}{% if c.coloptions %}
+ {{conn|qtIdent(c.attname)}} {% if is_sql %}{{ c.fulltype }}{% else %}{{conn|qtTypeIdent(c.datatype) }}{% if c.typlen %}({{c.typlen}}{% if c.precision %}, {{c.precision}}{% endif %}){% endif %}{% if c.isArrayType %}[]{% endif %}{% endif %}{% if c.coloptions %}
{% for o in c.coloptions %}{% if o.option and o.value %}
{% if loop.first %} OPTIONS ({% endif %}{% if not loop.first %}, {% endif %}{{o.option}} {{o.value|qtLiteral}}{% if loop.last %}){% endif %}{% endif %}
{% endfor %}{% endif %}{% if c.attnotnull %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/create.sql
index 093eec0..bccebd4 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/create.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/9.5_plus/create.sql
@@ -7,7 +7,7 @@ CREATE FOREIGN TABLE {{ conn|qtIdent(data.basensp, data.name) }}(
{% for c in data.columns %}
{% if (not c.inheritedfrom or c.inheritedfrom =='' or c.inheritedfrom == None or c.inheritedfrom == 'None' ) %}
{% if is_columns.append('1') %}{% endif %}
- {{conn|qtIdent(c.attname)}} {{conn|qtTypeIdent(c.datatype) }}{% if c.typlen %}({{c.typlen}}{% if c.precision %}, {{c.precision}}{% endif %}){% endif %}{% if c.isArrayType %}[]{% endif %}{% if c.coloptions %}
+ {{conn|qtIdent(c.attname)}} {% if is_sql %}{{ c.fulltype }}{% else %}{{conn|qtTypeIdent(c.datatype) }}{% if c.typlen %}({{c.typlen}}{% if c.precision %}, {{c.precision}}{% endif %}){% endif %}{% if c.isArrayType %}[]{% endif %}{% endif %}{% if c.coloptions %}
{% for o in c.coloptions %}{% if o.option and o.value %}
{% if loop.first %} OPTIONS ({% endif %}{% if not loop.first %}, {% endif %}{{o.option}} {{o.value|qtLiteral}}{% if loop.last %}){% endif %}{% endif %}
{% endfor %}{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/default/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/default/create.sql
index a1a8743..eb1b753 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/default/create.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/default/create.sql
@@ -3,7 +3,7 @@
CREATE FOREIGN TABLE {{ conn|qtIdent(data.basensp, data.name) }}(
{% if data.columns %}
{% for c in data.columns %}
- {{conn|qtIdent(c.attname)}} {{ conn|qtTypeIdent(c.datatype) }}{% if c.typlen %}({{c.typlen}} {% if c.precision %}, {{c.precision}}{% endif %}){% endif %}{% if c.isArrayType %}[]{% endif %}{% if c.attnotnull %}
+ {{conn|qtIdent(c.attname)}} {% if is_sql %}{{ c.fulltype }}{% else %}{{ conn|qtTypeIdent(c.datatype) }}{% if c.typlen %}({{c.typlen}} {% if c.precision %}, {{c.precision}}{% endif %}){% endif %}{% if c.isArrayType %}[]{% endif %}{% endif %}{% if c.attnotnull %}
NOT NULL{% else %} NULL{% endif %}
{% if not loop.last %},
{% endif %}{% endfor -%}{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py
index 99f0ddc..4f6572d 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py
@@ -652,20 +652,24 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings):
column['isdup'], column['attndims'], column['atttypmod']
)
+ length = False
+ precision = False
+ if 'elemoid' in column:
+ length, precision, typeval = self.get_length_precision(column['elemoid'])
+
# If we have length & precision both
- matchObj = re.search(r'(\d+),(\d+)', fulltype)
- if matchObj:
+ if length and precision:
+ matchObj = re.search(r'(\d+),(\d+)', fulltype)
column['attlen'] = matchObj.group(1)
column['attprecision'] = matchObj.group(2)
- else:
+ elif length:
# If we have length only
matchObj = re.search(r'(\d+)', fulltype)
- if matchObj:
- column['attlen'] = matchObj.group(1)
- column['attprecision'] = None
- else:
- column['attlen'] = None
- column['attprecision'] = None
+ column['attlen'] = matchObj.group(1)
+ column['attprecision'] = None
+ else:
+ column['attlen'] = None
+ column['attprecision'] = None
SQL = render_template("/".join([self.column_template_path,
'is_referenced.sql']),
@@ -2540,7 +2544,7 @@ class TableView(PGChildNodeView, DataTypeReader, VacuumSettings):
# If the request for new object which do not have did
table_sql = render_template("/".join([self.template_path,
'create.sql']),
- data=data, conn=self.conn)
+ data=data, conn=self.conn, is_sql=True)
# Add into main sql
table_sql = re.sub('\n{2,}', '\n\n', table_sql)
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/column/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/column/__init__.py
index d9e688e..7a28cda 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/column/__init__.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/column/__init__.py
@@ -343,21 +343,26 @@ class ColumnsView(PGChildNodeView, DataTypeReader):
data['isdup'], data['attndims'], data['atttypmod']
)
+ length = False
+ precision = False
+ if 'elemoid' in data:
+ length, precision, typeval = self.get_length_precision(data['elemoid'])
+
import re
# If we have length & precision both
- matchObj = re.search(r'(\d+),(\d+)', fulltype)
- if matchObj:
+
+ if length and precision:
+ matchObj = re.search(r'(\d+),(\d+)', fulltype)
data['attlen'] = matchObj.group(1)
data['attprecision'] = matchObj.group(2)
- else:
+ elif length:
# If we have length only
matchObj = re.search(r'(\d+)', fulltype)
- if matchObj:
- data['attlen'] = matchObj.group(1)
- data['attprecision'] = None
- else:
- data['attlen'] = None
- data['attprecision'] = None
+ data['attlen'] = matchObj.group(1)
+ data['attprecision'] = None
+ else:
+ data['attlen'] = None
+ data['attprecision'] = None
# We need to fetch inherited tables for each table
SQL = render_template("/".join([self.template_path,
@@ -750,7 +755,7 @@ class ColumnsView(PGChildNodeView, DataTypeReader):
except Exception as e:
return internal_server_error(errormsg=str(e))
- def get_sql(self, scid, tid, clid, data):
+ def get_sql(self, scid, tid, clid, data, is_sql=False):
"""
This function will genrate sql from model data
"""
@@ -819,7 +824,7 @@ class ColumnsView(PGChildNodeView, DataTypeReader):
self.acl)
# If the request for new object which do not have did
SQL = render_template("/".join([self.template_path, 'create.sql']),
- data=data, conn=self.conn)
+ data=data, conn=self.conn, is_sql=is_sql)
return SQL, data['name'] if 'name' in data else old_data['name']
@check_precondition
@@ -863,7 +868,7 @@ class ColumnsView(PGChildNodeView, DataTypeReader):
# We will add table & schema as well
data = self._formatter(scid, tid, clid, data)
- SQL, name = self.get_sql(scid, tid, None, data)
+ SQL, name = self.get_sql(scid, tid, None, data, is_sql=True)
if not isinstance(SQL, (str, unicode)):
return SQL
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/9.1_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/9.1_plus/properties.sql
index 22cef71..32d5cde 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/9.1_plus/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/9.1_plus/properties.sql
@@ -2,6 +2,7 @@ SELECT att.attname as name, att.*, def.*, pg_catalog.pg_get_expr(def.adbin, def.
CASE WHEN att.attndims > 0 THEN 1 ELSE 0 END AS isarray,
format_type(ty.oid,NULL) AS typname,
format_type(ty.oid,att.atttypmod) AS displaytypname,
+ CASE WHEN ty.typelem > 0 THEN ty.typelem ELSE ty.oid END as elemoid,
tn.nspname as typnspname, et.typname as elemtypname,
ty.typstorage AS defaultstorage, cl.relname, na.nspname,
concat(quote_ident(na.nspname) ,'.', quote_ident(cl.relname)) AS parent_tbl,
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/default/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/default/create.sql
index 0fce446..5bc521c 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/default/create.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/default/create.sql
@@ -5,7 +5,7 @@
{### Add column ###}
{% if data.name and data.cltype %}
ALTER TABLE {{conn|qtIdent(data.schema, data.table)}}
- ADD COLUMN {{conn|qtIdent(data.name)}} {{ GET_TYPE.CREATE_TYPE_SQL(conn, data.cltype, data.attlen, data.attprecision, data.hasSqrBracket) }}{% if data.collspcname %}
+ ADD COLUMN {{conn|qtIdent(data.name)}} {% if is_sql %}{{data.displaytypname}}{% else %}{{ GET_TYPE.CREATE_TYPE_SQL(conn, data.cltype, data.attlen, data.attprecision, data.hasSqrBracket) }}{% endif %}{% if data.collspcname %}
COLLATE {{data.collspcname}}{% endif %}{% if data.attnotnull %}
NOT NULL{% endif %}{% if data.defval %}
DEFAULT {{data.defval}}{% endif %};
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/default/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/default/properties.sql
index 1325f4a..36d1e1a 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/default/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/default/properties.sql
@@ -2,6 +2,7 @@ SELECT att.attname as name, att.*, def.*, pg_catalog.pg_get_expr(def.adbin, def.
CASE WHEN att.attndims > 0 THEN 1 ELSE 0 END AS isarray,
format_type(ty.oid,NULL) AS typname,
format_type(ty.oid,att.atttypmod) AS displaytypname,
+ CASE WHEN ty.typelem > 0 THEN ty.typelem ELSE ty.oid END as elemoid,
tn.nspname as typnspname, et.typname as elemtypname,
ty.typstorage AS defaultstorage, cl.relname, na.nspname,
quote_ident(na.nspname) || '.' || quote_ident(cl.relname) AS parent_tbl,
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/default/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/default/create.sql
index c21a661..f8e858f 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/default/create.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/default/create.sql
@@ -44,7 +44,7 @@ CREATE {% if data.relpersistence %}UNLOGGED {% endif %}TABLE {{conn|qtIdent(data
{% if c.name and c.cltype %}
{% if loop.index != 1 %},
{% endif %}
- {{conn|qtIdent(c.name)}} {{ GET_TYPE.CREATE_TYPE_SQL(conn, c.cltype, c.attlen, c.attprecision, c.hasSqrBracket) }}{% if c.collspcname %} COLLATE {{c.collspcname}}{% endif %}{% if c.attnotnull %} NOT NULL{% endif %}{% if c.defval %} DEFAULT {{c.defval}}{% endif %}
+ {{conn|qtIdent(c.name)}} {% if is_sql %}{{c.displaytypname}}{% else %}{{ GET_TYPE.CREATE_TYPE_SQL(conn, c.cltype, c.attlen, c.attprecision, c.hasSqrBracket) }}{% endif %}{% if c.collspcname %} COLLATE {{c.collspcname}}{% endif %}{% if c.attnotnull %} NOT NULL{% endif %}{% if c.defval %} DEFAULT {{c.defval}}{% endif %}
{% endif %}
{% endfor %}
{% endif %}
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 d3070c0..b68ac5a 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
@@ -414,12 +414,12 @@ class TypeView(PGChildNodeView, DataTypeReader):
for row in rset['rows']:
# We will fetch Full type name
- fulltype = self.get_full_type(
- row['collnspname'], row['typname'],
- row['isdup'], row['attndims'], row['atttypmod']
- )
+ # fulltype = self.get_full_type(
+ # row['collnspname'], row['typname'],
+ # row['isdup'], row['attndims'], row['atttypmod']
+ # )
- typelist = ' '.join([row['attname'], fulltype])
+ typelist = ' '.join([row['attname'], row['fulltype']])
if not row['collname'] or (row['collname'] == 'default'
and row['collnspname'] == 'pg_catalog'):
full_collate = ''
@@ -431,25 +431,26 @@ class TypeView(PGChildNodeView, DataTypeReader):
typelist += collate
properties_list.append(typelist)
+ is_tlength = False
+ is_precision = False
+ if 'elemoid' in row:
+ is_tlength, is_precision, typeval = self.get_length_precision(row['elemoid'])
+
# Below logic will allow us to split length, precision from type name for grid
import re
# If we have length & precision both
- matchObj = re.search(r'(\d+),(\d+)', fulltype)
- if matchObj:
+ if is_tlength and is_precision:
+ matchObj = re.search(r'(\d+),(\d+)', row['fulltype'])
t_len = matchObj.group(1)
t_prec = matchObj.group(2)
- else:
+ elif is_tlength:
# If we have length only
- matchObj = re.search(r'(\d+)', fulltype)
- if matchObj:
- t_len = matchObj.group(1)
- t_prec = None
- else:
- t_len = None
- t_prec = None
-
- is_tlength = True if t_len else False
- is_precision = True if t_prec else False
+ matchObj = re.search(r'(\d+)', row['fulltype'])
+ t_len = matchObj.group(1)
+ t_prec = None
+ else:
+ t_len = None
+ t_prec = None
type_name = DataTypeReader.parse_type_name(row['typname'])
@@ -461,7 +462,7 @@ class TypeView(PGChildNodeView, DataTypeReader):
'collation': full_collate, 'cltype': row['type'],
'tlength': t_len, 'precision': t_prec,
'is_tlength': is_tlength, 'is_precision': is_precision,
- 'hasSqrBracket': row['hasSqrBracket']})
+ 'hasSqrBracket': row['hasSqrBracket'], 'fulltype': row['fulltype']})
# Adding both results
res['member_list'] = ', '.join(properties_list)
@@ -1152,7 +1153,7 @@ class TypeView(PGChildNodeView, DataTypeReader):
return data
- def get_sql(self, gid, sid, data, scid, tid=None):
+ def get_sql(self, gid, sid, data, scid, tid=None, is_sql=False):
"""
This function will genrate sql from model data
"""
@@ -1262,7 +1263,7 @@ class TypeView(PGChildNodeView, DataTypeReader):
SQL = render_template("/".join([self.template_path,
'create.sql']),
- data=data, conn=self.conn)
+ data=data, conn=self.conn, is_sql=is_sql)
return SQL, data['name'] if 'name' in data else old_data['name']
@@ -1324,7 +1325,7 @@ class TypeView(PGChildNodeView, DataTypeReader):
if data[k] == '-':
data[k] = None
- SQL, name = self.get_sql(gid, sid, data, scid, tid=None)
+ SQL, name = self.get_sql(gid, sid, data, scid, tid=None, is_sql=True)
# Most probably this is due to error
if not isinstance(SQL, (str, unicode)):
return SQL
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/default/additional_properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/default/additional_properties.sql
index 172f6bb..0c31d46 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/default/additional_properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/default/additional_properties.sql
@@ -2,7 +2,9 @@
{% if type == 'c' %}
SELECT attnum, attname, format_type(t.oid,NULL) AS typname, attndims, atttypmod, nsp.nspname,
(SELECT COUNT(1) from pg_type t2 WHERE t2.typname=t.typname) > 1 AS isdup,
- collname, nspc.nspname as collnspname, att.attrelid
+ collname, nspc.nspname as collnspname, att.attrelid,
+ format_type(t.oid, att.atttypmod) AS fulltype,
+ CASE WHEN t.typelem > 0 THEN t.typelem ELSE t.oid END as elemoid
FROM pg_attribute att
JOIN pg_type t ON t.oid=atttypid
JOIN pg_namespace nsp ON t.typnamespace=nsp.oid
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/default/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/default/create.sql
index 7598294..f2d6560 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/default/create.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/templates/type/sql/default/create.sql
@@ -8,7 +8,7 @@ CREATE TYPE {{ conn|qtIdent(data.schema, data.name) }};
{### 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) }} {{ GET_TYPE.CREATE_TYPE_SQL(conn, d.cltype, d.tlength, d.precision, d.hasSqrBracket) }}{% if d.collation %} COLLATE {{d.collation}}{% endif %}{% endfor %}{% endif %}{{"\n"}});
+({{"\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' %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/utils.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/utils.py
index a09cfff..3cc31d2 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/utils.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/utils.py
@@ -103,25 +103,11 @@ class DataTypeReader:
min_val = 0
max_val = 0
- # Check against PGOID for specific type
+ # Check if the type will have length and precision or not
if row['elemoid']:
- if row['elemoid'] in (1560, 1561, 1562, 1563, 1042, 1043,
- 1014, 1015):
- typeval = 'L'
- elif row['elemoid'] in (1083, 1114, 1115, 1183, 1184, 1185,
- 1186, 1187, 1266, 1270):
- typeval = 'D'
- elif row['elemoid'] in (1231, 1700):
- typeval = 'P'
- else:
- typeval = ' '
-
- # Set precision & length/min/max values
- if typeval == 'P':
- precision = True
+ length, precision, typeval = self.get_length_precision(row['elemoid'])
- if precision or typeval in ('L', 'D'):
- length = True
+ if length:
min_val = 0 if typeval == 'D' else 1
if precision:
max_val = 1000
@@ -143,6 +129,34 @@ class DataTypeReader:
return True, res
+ @staticmethod
+ def get_length_precision(elemoid):
+ precision = False
+ length = False
+ typeval = ''
+
+ # Check against PGOID for specific type
+ if elemoid:
+ if elemoid in (1560, 1561, 1562, 1563, 1042, 1043,
+ 1014, 1015):
+ typeval = 'L'
+ elif elemoid in (1083, 1114, 1115, 1183, 1184, 1185,
+ 1186, 1187, 1266, 1270):
+ typeval = 'D'
+ elif elemoid in (1231, 1700):
+ typeval = 'P'
+ else:
+ typeval = ' '
+
+ # Set precision & length/min/max values
+ if typeval == 'P':
+ precision = True
+
+ if precision or typeval in ('L', 'D'):
+ length = True
+
+ return length, precision, typeval
+
def get_full_type(self, nsp, typname, isDup, numdims, typmod):
"""
Returns full type name with Length and Precision.
view thread (15+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: [pgAdmin4][Patch]: Fixed RM 2324 - PostGIS datatypes not showing up properly on SQL tab.
In-Reply-To: <CAFOhELdv7VF4W3qFMcHaeVZ+PbVAkKyo6dOtdjmF+sEwFYp1CQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox