Hello Hackers,
I am updating this patch as we found out that the SQL tab was not showing
the functions properly, and we corrected that in the new update patch in
this email
Thanks
Joao
On Wed, Jan 17, 2018 at 12:25 PM, Joao De Almeida Pereira <
jdealmeidapere...@pivotal.io> wrote:
>
> @Dave I sent the new patch with correction in the previous email. Can you
> review it and let us know if it is ok?
>
>
> On Fri, Jan 12, 2018 at 12:48 PM, Joao De Almeida Pereira <
> jdealmeidapere...@pivotal.io> wrote:
>
>> Thanks for the help Murtuza
>>
>> Attached you can find the revisited patch that now displayed all the
>> information about functions in GreenPlum
>>
>> Thanks
>> Joao
>>
>> On Fri, Jan 12, 2018 at 9:51 AM, Murtuza Zabuawala <
>> murtuza.zabuaw...@enterprisedb.com> wrote:
>>
>>> Hi,
>>>
>>> We create collection of existing parameters using function
>>> 'def _format_arguments_from_db(...)' in a file
>>> '../pgadmin4/web/pgadmin/browser/server_groups/servers/datab
>>> ases/schemas/functions/__init__.py' @ line 454, and on client side we
>>> have backbone collection mapped with same id on line 304 (function.js)
>>>
>>>
>>>
>>> --
>>> Regards,
>>> Murtuza Zabuawala
>>> EnterpriseDB: http://www.enterprisedb.com
>>> The Enterprise PostgreSQL Company
>>>
>>>
>>> On Fri, Jan 12, 2018 at 8:10 PM, Joao De Almeida Pereira <
>>> jdealmeidapere...@pivotal.io> wrote:
>>>
>>>> Hello,
>>>> I changed that function to make the retrieval quicker because the
>>>> return type of all the types in the database for GreenPlum can be quite
>>>> big.
>>>>
>>>> Nevertheless the "Data Types" in the front end do not get filled up
>>>> with the values.
>>>>
>>>> Where is the Javascript code that does the mapping between the call of
>>>> get_types and that table in the screenshot?
>>>>
>>>> Thanks
>>>> Joao
>>>>
>>>>
>>>> On Fri, Jan 12, 2018 at 12:37 AM, Murtuza Zabuawala <
>>>> murtuza.zabuaw...@enterprisedb.com> wrote:
>>>>
>>>>> Hi Joao,
>>>>>
>>>>> We have written common function 'def get_types(...)' in a class called
>>>>> 'DataTypeReader' to fetch datatypes in a file
>>>>> '../pgadmin4/web/pgadmin/brows
>>>>> er/server_groups/servers/databases/schemas/utils.py' and we have
>>>>> inherited from DataTypeReader in Function module's 'FunctionView' class to
>>>>> use it check line no. 120
>>>>>
>>>>> --
>>>>> Regards,
>>>>> Murtuza Zabuawala
>>>>> EnterpriseDB: http://www.enterprisedb.com
>>>>> The Enterprise PostgreSQL Company
>>>>>
>>>>>
>>>>> On Thu, Jan 11, 2018 at 9:31 PM, Joao De Almeida Pereira <
>>>>> jdealmeidapere...@pivotal.io> wrote:
>>>>>
>>>>>> Hello Hackers,
>>>>>> We had some requests from GreenPlum users to display the functions
>>>>>> node again.
>>>>>> This patch reintroduces functions as a node underneath the Schemas.
>>>>>>
>>>>>>
>>>>>> Unfortunately this patch is not final because I could not find the
>>>>>> place where the Data Type is populated from. (See next image)[image:
>>>>>> Inline image 1]
>>>>>>
>>>>>> Can anyone explain me how these types get populated?
>>>>>>
>>>>>>
>>>>>>
>>>>>> Thanks
>>>>>> Joao
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>>
>
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/__init__.py
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/__init__.py
index 92edc8b7..a59f6671 100644
---
a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/__init__.py
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/__init__.py
@@ -71,10 +71,10 @@ class FunctionModule(SchemaChildModule):
"""
super(FunctionModule, self).__init__(*args, **kwargs)
- self.min_ver = 90100
+ self.min_ver = None
self.max_ver = None
self.server_type = None
- self.min_gpdbver = 1000000000
+ self.min_gpdbver = None
def get_nodes(self, gid, sid, did, scid):
"""
@@ -659,7 +659,7 @@ class FunctionView(PGChildNodeView, DataTypeReader):
condition += " AND nspname NOT LIKE E'pg\\\\_toast%' AND nspname
NOT LIKE E'pg\\\\_temp%'"
# Get Types
- status, types = self.get_types(self.conn, condition)
+ status, types = self.get_types(self.conn, condition, False, scid)
if not status:
return internal_server_error(errormsg=types)
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/acl.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/acl.sql
new file mode 100644
index 00000000..1162ee67
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/acl.sql
@@ -0,0 +1,38 @@
+SELECT
+ COALESCE(gt.rolname, 'PUBLIC') AS grantee,
+ g.rolname AS grantor, array_agg(privilege_type) AS privileges,
+ array_agg(is_grantable) AS grantable
+FROM
+ (SELECT
+ (d).grantee AS grantee,
+ (d).grantor AS grantor,
+ (d).is_grantable AS is_grantable,
+ CASE (d).privilege_type
+ WHEN 'EXECUTE' THEN 'X'
+ ELSE 'UNKNOWN' END AS privilege_type
+ FROM
+ (SELECT
+ u_grantor.oid AS grantor,
+ grantee.oid AS grantee,
+ pr.type AS privilege_type,
+ aclcontains(c.proacl, makeaclitem(grantee.oid, u_grantor.oid,
pr.type, true)) AS is_grantable
+ FROM pg_proc c, pg_namespace nc, pg_authid u_grantor, (
+ SELECT pg_authid.oid, pg_authid.rolname
+ FROM pg_authid
+ UNION ALL
+ SELECT 0::oid AS oid, 'PUBLIC') grantee(oid, rolname),
+ (SELECT 'EXECUTE') pr(type)
+ WHERE c.pronamespace = nc.oid
+ AND (
+ c.proacl is NULL
+ OR aclcontains(c.proacl, makeaclitem(grantee.oid, u_grantor.oid,
pr.type, false))
+ )
+ AND (pg_has_role(u_grantor.oid, 'USAGE'::text)
+ OR pg_has_role(grantee.oid, 'USAGE'::text)
+ OR grantee.rolname = 'PUBLIC'::name)
+ AND c.oid = {{ fnid }}::OID
+ ) 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;
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/coll_stats.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/coll_stats.sql
new file mode 100644
index 00000000..86a0d7be
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/coll_stats.sql
@@ -0,0 +1,20 @@
+SELECT
+ funcname AS {{ conn|qtIdent(_('Name')) }},
+ calls AS {{ conn|qtIdent(_('Number of calls')) }},
+ total_time AS {{ conn|qtIdent(_('Total time')) }},
+ self_time AS {{ conn|qtIdent(_('Self time')) }}
+FROM
+ pg_stat_user_functions
+WHERE
+ schemaname = {{schema_name|qtLiteral}}
+ AND funcid IN (
+ SELECT p.oid
+ FROM
+ pg_proc p
+ JOIN
+ pg_type typ ON typ.oid=p.prorettype
+ WHERE
+ p.proisagg = FALSE
+ AND typname NOT IN ('trigger', 'event_trigger')
+ )
+ORDER BY funcname;
\ No newline at end of file
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/create.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/create.sql
new file mode 100644
index 00000000..6716826f
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/create.sql
@@ -0,0 +1,63 @@
+{% import 'macros/functions/security.macros' as SECLABEL %}
+{% import 'macros/functions/privilege.macros' as PRIVILEGE %}
+{% import 'macros/functions/variable.macros' as VARIABLE %}
+{% set is_columns = [] %}
+{% if data %}
+{% if query_for == 'sql_panel' and func_def is defined %}
+CREATE{% if query_type is defined %}{{' OR REPLACE'}}{% endif %} FUNCTION
{{func_def}}
+{% else %}
+CREATE{% if query_type is defined %}{{' OR REPLACE'}}{% endif %} FUNCTION {{
conn|qtIdent(data.pronamespace, data.name) }}({% if data.arguments %}
+{% for p in data.arguments %}{% if p.argmode %}{{p.argmode}} {% endif %}{% if
p.argname %}{{ conn|qtIdent(p.argname)}}{% endif %}{% if p.argtype %}{{
conn|qtTypeIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT
{{p.argdefval}}{% endif %}
+{% if not loop.last %},{% endif %}
+{% endfor %}
+{% endif -%}
+)
+{% endif -%}
+ RETURNS{% if data.proretset and (data.prorettypename.startswith('SETOF ')
or data.prorettypename.startswith('TABLE')) %} {{ data.prorettypename }} {%
elif data.proretset %} SETOF {{ conn|qtTypeIdent(data.prorettypename) }}{% else
%} {{ conn|qtTypeIdent(data.prorettypename) }}{% endif %}
+
+ LANGUAGE {{ data.lanname|qtLiteral }}
+{% if data.procost %}
+ COST {{data.procost}}
+{% endif %}
+ {% if data.provolatile %}{% if data.provolatile == 'i' %}IMMUTABLE{% elif
data.provolatile == 's' %}STABLE{% else %}VOLATILE{% endif %} {% endif %}{% if
data.proisstrict %}STRICT {% endif %}{% if data.prosecdef %}SECURITY DEFINER {%
endif %}{% if data.proiswindow %}WINDOW{% endif -%}
+{% if data.prorows and (data.prorows | int) > 0 %}
+ ROWS {{data.prorows}}{% endif -%}{% if data.variables %}{% for v in
data.variables %}
+
+ SET {{ conn|qtIdent(v.name) }}={{ v.value|qtLiteral }}{% endfor %}
+{% endif %}
+
+AS {% if data.lanname == 'c' %}
+{{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }}
+{% else %}
+$BODY$
+{{ data.prosrc }}
+$BODY${% endif %};
+{% if data.funcowner %}
+
+ALTER FUNCTION {{ conn|qtIdent(data.pronamespace, data.name)
}}({{data.func_args_without}})
+ OWNER TO {{ conn|qtIdent(data.funcowner) }};
+{% endif %}
+{% if data.acl %}
+{% for p in data.acl %}
+
+{{ PRIVILEGE.SET(conn, "FUNCTION", p.grantee, data.name, p.without_grant,
p.with_grant, data.pronamespace, data.func_args_without)}}
+{% endfor %}{% endif %}
+{% if data.revoke_all %}
+
+{{ PRIVILEGE.UNSETALL(conn, "FUNCTION", "PUBLIC", data.name,
data.pronamespace, data.func_args_without)}}
+{% endif %}
+{% if data.description %}
+
+COMMENT ON FUNCTION {{ conn|qtIdent(data.pronamespace, data.name)
}}({{data.func_args_without}})
+ IS {{ data.description|qtLiteral }};
+{% endif -%}
+{% if data.seclabels %}
+{% for r in data.seclabels %}
+{% if r.label and r.provider %}
+
+{{ SECLABEL.SET(conn, 'FUNCTION', data.name, r.provider, r.label,
data.pronamespace, data.func_args_without) }}
+{% endif %}
+{% endfor %}
+{% endif -%}
+
+{% endif %}
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/delete.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/delete.sql
new file mode 100644
index 00000000..246bec12
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/delete.sql
@@ -0,0 +1,21 @@
+{% if scid and fnid %}
+SELECT
+ pr.proname as name, '(' || COALESCE(pg_catalog
+ .pg_get_function_identity_arguments(pr.oid), '') || ')' as func_args,
+ nspname
+FROM
+ pg_proc pr
+JOIN
+ pg_type typ ON typ.oid=prorettype
+JOIN
+ pg_namespace nsp ON nsp.oid=pr.pronamespace
+WHERE
+ proisagg = FALSE
+ AND pronamespace = {{scid}}::oid
+ AND typname NOT IN ('trigger', 'event_trigger')
+ AND pr.oid = {{fnid}};
+{% endif %}
+
+{% if name %}
+DROP FUNCTION {{ conn|qtIdent(nspname, name) }}{{func_args}}{% if cascade %}
CASCADE{% endif %};
+{% endif %}
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/get_definition.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/get_definition.sql
new file mode 100644
index 00000000..cae98f84
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/get_definition.sql
@@ -0,0 +1,15 @@
+SELECT proretset, prosrc, probin,
+ pg_catalog.pg_get_function_arguments(pg_proc.oid) AS funcargs,
+ pg_catalog.pg_get_function_identity_arguments(pg_proc.oid) AS funciargs,
+ pg_catalog.pg_get_function_result(pg_proc.oid) AS funcresult,
+ proiswin, provolatile, proisstrict, prosecdef,
+ proconfig, procost, prorows, prodataaccess,
+ 'a' as proexeclocation,
+ (SELECT lanname FROM pg_catalog.pg_language WHERE pg_proc.oid = prolang) as
lanname,
+ nspname || '.' || pg_proc.proname || '(' ||
COALESCE(pg_catalog.pg_get_function_identity_arguments(pg_proc.oid), '') || ')'
as name,
+ nspname || '.' || pg_proc.proname || '(' ||
COALESCE(pg_catalog.pg_get_function_arguments(pg_proc.oid), '') || ')' as
name_with_default_args
+FROM pg_catalog.pg_proc
+ JOIN pg_namespace nsp ON nsp.oid=pg_proc.pronamespace
+WHERE proisagg = FALSE
+ AND pronamespace = {{scid}}::oid
+ AND pg_proc.oid = {{fnid}}::oid;
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/get_languages.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/get_languages.sql
new file mode 100644
index 00000000..f81ddfbd
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/get_languages.sql
@@ -0,0 +1,4 @@
+SELECT
+ lanname as label, lanname as value
+FROM
+ pg_language;
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/get_oid.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/get_oid.sql
new file mode 100644
index 00000000..3bfc20a6
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/get_oid.sql
@@ -0,0 +1,17 @@
+SELECT
+ pr.oid, pr.proname || '(' || COALESCE(pg_catalog
+ .pg_get_function_identity_arguments(pr.oid), '') || ')' as name,
+ lanname, pg_get_userbyid(proowner) as funcowner, pr.pronamespace as nsp
+FROM
+ pg_proc pr
+JOIN
+ pg_type typ ON typ.oid=prorettype
+JOIN
+ pg_language lng ON lng.oid=prolang
+JOIN
+ pg_namespace nsp ON nsp.oid=pr.pronamespace
+ AND nsp.nspname={{ nspname|qtLiteral }}
+WHERE
+ proisagg = FALSE
+ AND typname NOT IN ('trigger', 'event_trigger')
+ AND pr.proname = {{ name|qtLiteral }};
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/get_out_types.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/get_out_types.sql
new file mode 100644
index 00000000..64a11875
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/get_out_types.sql
@@ -0,0 +1,6 @@
+SELECT
+ format_type(oid, NULL) AS out_arg_type
+FROM
+ pg_type
+WHERE
+ oid = {{ out_arg_oid }}::oid;
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/get_schema.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/get_schema.sql
new file mode 100644
index 00000000..127d4b9d
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/get_schema.sql
@@ -0,0 +1,6 @@
+SELECT
+ nspname
+FROM
+ pg_namespace
+WHERE
+ oid = {{ scid }}::oid;
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/get_types.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/get_types.sql
new file mode 100644
index 00000000..2a5582ee
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/get_types.sql
@@ -0,0 +1,20 @@
+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
+ (
+ typtype IN ('b', 'c', 'd', 'e', 'p', 'r')
+ AND typname NOT IN ('any', 'trigger', 'language_handler',
'event_trigger')
+ )
+ ) AS dummy
+ORDER BY nspname <> 'pg_catalog', nspname <> 'public', nspname, 1;
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/node.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/node.sql
new file mode 100644
index 00000000..238e6a06
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/node.sql
@@ -0,0 +1,22 @@
+SELECT
+ pr.oid, pr.proname || '(' ||
COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') || ')' as
name,
+ lanname, pg_get_userbyid(proowner) as funcowner, description
+FROM
+ pg_proc pr
+JOIN
+ pg_type typ ON typ.oid=prorettype
+JOIN
+ pg_language lng ON lng.oid=prolang
+LEFT OUTER JOIN
+ pg_description des ON (des.objoid=pr.oid AND
des.classoid='pg_proc'::regclass)
+WHERE
+ proisagg = FALSE
+{% if fnid %}
+ AND pr.oid = {{ fnid|qtLiteral }}
+{% endif %}
+{% if scid %}
+ AND pronamespace = {{scid}}::oid
+{% endif %}
+ AND typname NOT IN ('trigger', 'event_trigger')
+ORDER BY
+ proname;
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/properties.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/properties.sql
new file mode 100644
index 00000000..fdc2f149
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/properties.sql
@@ -0,0 +1,27 @@
+SELECT
+ pr.oid, pr.xmin, pr.*, pr.prosrc AS prosrc_c,
+ pr.proname AS name, pg_get_function_result(pr.oid) AS prorettypename,
+ typns.nspname AS typnsp, lanname, proargnames, oidvectortypes(proargtypes)
AS proargtypenames,
+ pg_get_expr(proargdefaults, 'pg_catalog.pg_class'::regclass) AS
proargdefaultvals,
+ pronargdefaults, proconfig, pg_get_userbyid(proowner) AS funcowner,
description,
+ NULL AS seclabels
+FROM
+ pg_proc pr
+JOIN
+ pg_type typ ON typ.oid=prorettype
+JOIN
+ pg_namespace typns ON typns.oid=typ.typnamespace
+JOIN
+ pg_language lng ON lng.oid=prolang
+LEFT OUTER JOIN
+ pg_description des ON (des.objoid=pr.oid AND
des.classoid='pg_proc'::regclass)
+WHERE
+ proisagg = FALSE
+{% if fnid %}
+ AND pr.oid = {{fnid}}::oid
+{% else %}
+ AND pronamespace = {{scid}}::oid
+{% endif %}
+ AND typname NOT IN ('trigger', 'event_trigger')
+ORDER BY
+ proname;
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/stats.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/stats.sql
new file mode 100644
index 00000000..2e276b65
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/stats.sql
@@ -0,0 +1,8 @@
+SELECT
+ calls AS {{ conn|qtIdent(_('Number of calls')) }},
+ total_time AS {{ conn|qtIdent(_('Total time')) }},
+ self_time AS {{ conn|qtIdent(_('Self time')) }}
+FROM
+ pg_stat_user_functions
+WHERE
+ funcid = {{fnid}}::OID
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/update.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/update.sql
new file mode 100644
index 00000000..2114aeea
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/update.sql
@@ -0,0 +1,105 @@
+{% import 'macros/functions/security.macros' as SECLABEL %}
+{% import 'macros/functions/privilege.macros' as PRIVILEGE %}
+{% import 'macros/functions/variable.macros' as VARIABLE %}{% if data %}
+{% set name = o_data.name %}
+{% if data.name %}
+{% if data.name != o_data.name %}
+ALTER FUNCTION {{ conn|qtIdent(o_data.pronamespace, o_data.name) }}({{
o_data.proargtypenames }})
+ RENAME TO {{ conn|qtIdent(data.name) }};
+{% set name = data.name %}
+{% endif %}
+{% endif -%}
+{% if data.change_func %}
+
+CREATE OR REPLACE FUNCTION {{ conn|qtIdent(o_data.pronamespace, name) }}({% if
data.arguments %}
+{% for p in data.arguments %}{% if p.argmode %}{{p.argmode}} {% endif %}{% if
p.argname %}{{ conn|qtIdent(p.argname) }} {% endif %}{% if p.argtype %}{{
conn|qtTypeIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT
{{p.argdefval}}{% endif %}
+{% if not loop.last %}, {% endif %}
+{% endfor %}
+{% endif -%}
+)
+ RETURNS {{ o_data.prorettypename }}
+{% if 'lanname' in data %}
+ LANGUAGE {{ data.lanname|qtLiteral }} {% else %}
+ LANGUAGE {{ o_data.lanname|qtLiteral }}
+ {% endif %}{% if 'provolatile' in data and data.provolatile %}{{
data.provolatile }} {% elif 'provolatile' not in data and o_data.provolatile
%}{{ o_data.provolatile }}{% endif %}
+{% if ('proisstrict' in data and data.proisstrict) or ('proisstrict' not in
data and o_data.proisstrict) %} STRICT{% endif %}
+{% if ('prosecdef' in data and data.prosecdef) or ('prosecdef' not in data and
o_data.prosecdef) %} SECURITY DEFINER{% endif %}
+{% if ('proiswindow' in data and data.proiswindow) or ('proiswindow' not in
data and o_data.proiswindow) %} WINDOW{% endif %}
+
+ {% if data.procost %}COST {{data.procost}}{% elif o_data.procost %}COST
{{o_data.procost}}{% endif %}{% if data.prorows %}
+
+ ROWS {{data.prorows}}{% elif o_data.prorows and o_data.prorows != '0' %}
ROWS {{o_data.prorows}}{%endif -%}{% if data.merged_variables %}{% for v in
data.merged_variables %}
+
+ SET {{ conn|qtIdent(v.name) }}={{ v.value|qtLiteral }}{% endfor -%}
+ {% endif %}
+
+AS {% if 'probin' in data or 'prosrc_c' in data %}
+{% if 'probin' in data %}{{ data.probin|qtLiteral }}{% else %}{{
o_data.probin|qtLiteral }}{% endif %}, {% if 'prosrc_c' in data %}{{
data.prosrc_c|qtLiteral }}{% else %}{{ o_data.prosrc_c|qtLiteral }}{% endif
%}{% elif 'prosrc' in data %}
+$BODY${{ data.prosrc }}$BODY${% elif o_data.lanname == 'c' %}
+{{ o_data.probin|qtLiteral }}, {{ o_data.prosrc_c|qtLiteral }}{% else %}
+$BODY${{ o_data.prosrc }}$BODY${% endif -%};
+{% endif -%}
+{% if data.funcowner %}
+
+ALTER FUNCTION {{ conn|qtIdent(o_data.pronamespace, name) }}({{
o_data.proargtypenames }})
+ OWNER TO {{ conn|qtIdent(data.funcowner) }};
+{% endif -%}
+{# The SQL generated below will change priviledges #}
+{% if data.acl %}
+{% if 'deleted' in data.acl %}
+{% for priv in data.acl.deleted %}
+{{ PRIVILEGE.UNSETALL(conn, 'FUNCTION', priv.grantee, name,
o_data.pronamespace, o_data.proargtypenames) }}
+
+{% endfor %}{% endif %}
+{% if 'changed' in data.acl %}
+{% for priv in data.acl.changed %}
+{{ PRIVILEGE.UNSETALL(conn, 'FUNCTION', priv.grantee, name,
o_data.pronamespace, o_data.proargtypenames) }}
+
+{{ PRIVILEGE.SET(conn, 'FUNCTION', priv.grantee, name, priv.without_grant,
priv.with_grant, o_data.pronamespace, o_data.proargtypenames) }}
+
+{% endfor %}{% endif %}
+{% if 'added' in data.acl %}
+{% for priv in data.acl.added %}
+
+{{ PRIVILEGE.SET(conn, 'FUNCTION', priv.grantee, name, priv.without_grant,
priv.with_grant, o_data.pronamespace, o_data.proargtypenames) }}
+{% endfor %}{% endif %}{% endif %}
+{% if data.change_func == False %}
+{% if data.variables %}
+{% if 'deleted' in data.variables and data.variables.deleted|length > 0 %}
+
+{{ VARIABLE.UNSET(conn, 'FUNCTION', name, data.variables.deleted,
o_data.pronamespace, o_data.proargtypenames ) }}
+{% endif -%}
+{% if 'merged_variables' in data and data.merged_variables|length > 0 %}
+
+{{ VARIABLE.SET(conn, 'FUNCTION', name, data.merged_variables,
o_data.pronamespace, o_data.proargtypenames ) }}
+{% endif %}
+{% endif %}{% endif %}
+{% set seclabels = data.seclabels %}
+{% if 'deleted' in seclabels and seclabels.deleted|length > 0 %}
+{% for r in seclabels.deleted %}
+{{ SECLABEL.UNSET(conn, 'FUNCTION', name, r.provider, o_data.pronamespace,
o_data.proargtypenames) }}
+
+{% endfor %}
+{% endif -%}
+{% if 'added' in seclabels and seclabels.added|length > 0 %}
+{% for r in seclabels.added %}
+
+{{ SECLABEL.SET(conn, 'FUNCTION', name, r.provider, r.label,
o_data.pronamespace, o_data.proargtypenames) }}
+{% endfor %}
+{% endif -%}
+{% if 'changed' in seclabels and seclabels.changed|length > 0 %}
+{% for r in seclabels.changed %}
+{{ SECLABEL.SET(conn, 'FUNCTION', name, r.provider, r.label,
o_data.pronamespace, o_data.proargtypenames) }}
+{% endfor %}{% endif -%}
+{% if data.description is defined and data.description != o_data.description%}
+
+COMMENT ON FUNCTION {{ conn|qtIdent(o_data.pronamespace, name)
}}({{o_data.proargtypenames }})
+ IS {{ data.description|qtLiteral }};
+{% endif -%}
+{% if data.pronamespace %}
+
+ALTER FUNCTION {{ conn|qtIdent(o_data.pronamespace, name)
}}({{o_data.proargtypenames }})
+ SET SCHEMA {{ conn|qtIdent(data.pronamespace) }};
+{% endif -%}
+
+{% endif %}
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/variables.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/variables.sql
new file mode 100644
index 00000000..5233c71e
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/variables.sql
@@ -0,0 +1,6 @@
+SELECT
+ name, vartype, min_val, max_val, enumvals
+FROM
+ pg_settings
+WHERE
+ context in ('user', 'superuser');
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/datatype/sql/gpdb_5.0_plus/get_types.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/datatype/sql/gpdb_5.0_plus/get_types.sql
index 76343214..4c57b83a 100644
---
a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/datatype/sql/gpdb_5.0_plus/get_types.sql
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/datatype/sql/gpdb_5.0_plus/get_types.sql
@@ -21,5 +21,8 @@ FROM
UNION SELECT 'bigserial', 0, 8, 'b', 0, 'pg_catalog', false, false
UNION SELECT 'serial', 0, 4, 'b', 0, 'pg_catalog', false, false
{% endif %}
- ) AS dummy
+ AND (
+ typnamespace = {{schema_oid}}:: OID
+ OR nsp.nspname = 'pg_catalog'
+ )) AS dummy
ORDER BY nspname <> 'pg_catalog', nspname <> 'public', nspname, 1
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/test_utils.py
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/test_utils.py
new file mode 100644
index 00000000..d8f80dc3
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tests/test_utils.py
@@ -0,0 +1,113 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2018, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+
+import sys
+
+from pgadmin.browser.server_groups.servers.databases.schemas.utils import
DataTypeReader
+from pgadmin.utils.route import BaseTestGenerator
+
+if sys.version_info < (3, 3):
+ from mock import patch, Mock
+else:
+ from unittest.mock import patch, Mock
+
+_default_database_response = [
+ {
+ 'typname': 'type name',
+ 'elemoid': 1560,
+ 'is_collatable': True
+ }
+]
+_default_expected_function_output = [
+ {
+ 'label': 'type name',
+ 'value': 'type name',
+ 'typval': 'L',
+ 'precision': False,
+ 'length': True,
+ 'min_val': 1,
+ 'max_val': 2147483647,
+ 'is_collatable': True
+ }
+]
+_default_manager = dict(
+ server_type='ppas',
+ version='456'
+)
+
+
+class DataTypeReaderTest(BaseTestGenerator):
+ scenarios = [
+ ('Schema Oid is passed to the SQL Renderer',
+ dict(
+ manager=_default_manager,
+ execute_return_values=_default_database_response,
+ data_type_template_path='someplate/where/templates/are',
+ sql_condition='new condition',
+ schema_oid='123',
+ add_serials=False,
+ expected_sql_template_path='someplate/where/templates/are',
+ expected_function_output=_default_expected_function_output
+ )),
+ ('When no data_type_template_path is present in class, should create
template path with version number',
+ dict(
+ manager=_default_manager,
+ execute_return_values=_default_database_response,
+ sql_condition='new condition',
+ schema_oid='123',
+ add_serials=False,
+ expected_sql_template_path='datatype/sql/#456#',
+ expected_function_output=_default_expected_function_output
+ )),
+ ('When no data_type_template_path is present in class for GreenPlum, '
+ 'should create template path with gpdb and the version number',
+ dict(
+ manager=dict(
+ server_type='gpdb',
+ version='456'
+ ),
+ execute_return_values=_default_database_response,
+ sql_condition='new condition',
+ schema_oid='123',
+ add_serials=False,
+ expected_sql_template_path='datatype/sql/#gpdb#456#',
+ expected_function_output=_default_expected_function_output
+ ))
+ ]
+
+
@patch('pgadmin.browser.server_groups.servers.databases.schemas.utils.render_template')
+ def runTest(self, template_mock):
+ connection = Mock()
+ connection.execute_2darray.return_value = [
+ True,
+ {
+ 'rows': self.execute_return_values
+
+ }
+ ]
+
+ reader = DataTypeReader()
+ reader.manager = Mock()
+ reader.manager.server_type = self.manager['server_type']
+ reader.manager.version = self.manager['version']
+ try:
+ reader.data_type_template_path = self.data_type_template_path
+ except AttributeError:
+ ''
+ result = reader.get_types(connection, self.sql_condition,
self.add_serials, self.schema_oid)
+ self.assertEqual(result[1], self.expected_function_output)
+ self.assertTrue(result[0])
+
+ connection.execute_2darray.assert_called()
+ template_mock.assert_called_with(
+ self.expected_sql_template_path + '/get_types.sql',
+ condition=self.sql_condition,
+ add_serials=self.add_serials,
+ schema_oid=self.schema_oid
+ )
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 853c04f9..bba36ed1 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/utils.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/utils.py
@@ -82,7 +82,7 @@ class DataTypeReader:
- Returns data-types on the basis of the condition provided.
"""
- def get_types(self, conn, condition, add_serials=False):
+ def get_types(self, conn, condition, add_serials=False, schema_oid = ''):
"""
Returns data-types including calculation for Length and Precision.
@@ -90,6 +90,7 @@ class DataTypeReader:
conn: Connection Object
condition: condition to restrict SQL statement
add_serials: If you want to serials type
+ schema_oid: If needed pass the schema OID to restrict the search
"""
res = []
try:
@@ -103,11 +104,12 @@ class DataTypeReader:
) if self.manager.server_type == 'gpdb' else
'#{0}#'.format(self.manager.version)
)
-
SQL = render_template(
"/".join([self.data_type_template_path,'get_types.sql']),
condition=condition,
- add_serials=add_serials)
+ add_serials=add_serials,
+ schema_oid=schema_oid
+ )
status, rset = conn.execute_2darray(SQL)
if not status:
return status, rset
