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/databases/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..07f6a2cb
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/gpdb/sql/default/get_definition.sql
@@ -0,0 +1,12 @@
+SELECT
+ pg_get_functiondef({{fnid}}::oid) AS func_def,
+ nspname || '.' || pr.proname || '(' ||
COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') || ')' as
name,
+ nspname || '.' || pr.proname || '(' ||
COALESCE(pg_catalog.pg_get_function_arguments(pr.oid), '') || ')' as
name_with_default_args
+FROM
+ pg_proc pr
+JOIN
+ pg_namespace nsp ON nsp.oid=pr.pronamespace
+WHERE
+ proisagg = FALSE
+ AND pronamespace = {{scid}}::oid
+ AND pr.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
