MIME-Version: 1.0
In-Reply-To: 
 <CAFOhELeZJvNT0VXmq-CQFSruH8fOK9tt45AGSKMzJcQ6HX53eg@mail.gmail.com>
References: 
 <CAFOhELekFyrBJ8__NO8uFLL4OgYxvmxq_LOmKW4+Y1qrLaTVBw@mail.gmail.com>
	<CA+OCxowz38ek4d-km9+_pMPqbfHasvx24J5neLmsno0327ftTA@mail.gmail.com>
	<CAFOhELeZJvNT0VXmq-CQFSruH8fOK9tt45AGSKMzJcQ6HX53eg@mail.gmail.com>
Date: Wed, 23 Mar 2016 12:03:14 +0000
Message-ID: 
 <CA+OCxowyuh83pgosTiWAGnp6KUTsXw8D3V1jqUr7n2ApV63-HQ@mail.gmail.com>
Subject: Re: [pgAdmin4][Patch]: Functions/Procedures Module
From: Dave Page <dpage@pgadmin.org>
To: Khushboo Vashi <khushboo.vashi@enterprisedb.com>
Cc: pgadmin-hackers <pgadmin-hackers@postgresql.org>
Content-Type: text/plain; charset=UTF-8
Precedence: bulk
Sender: pgadmin-hackers-owner@postgresql.org

On Tue, Mar 22, 2016 at 10:51 AM, Khushboo Vashi
<khushboo.vashi@enterprisedb.com> wrote:
> Hi,
>
> Please find updated Patch for the Functions/Procedures Module.

Hi

Unfortunately I found more issues as I tested in-depth. I've attached
an updated patch in which I've done some cleanup - additional comments
below:

- Some argument lists are shown with an extra space, e.g.

character varying , integer

(pem.create_agent(character varying , integer))

- A 404 error is seen when functions are selected under a Catalog node.

- Procedures have a property of "System function?" - should be "System
procedure?"

- The Return Type, Returns a Set and Window fields should be hidden
for Procedures.

- When creating a procedure (below), I get the following error:

-----
-- PROCEDURE: foo

-- DROP PROCEDURE foo;

CREATE OR REPLACE PROCEDURE public.foo(IN a integer DEFAULT 10, INOUT
b integer DEFAULT  -)
    VOLATILE NOT LEAKPROOF SECURITY DEFINER
    COST 100.0
AS

BEGIN
   b:=a+b+1;
END;

COMMENT ON PROCEDURE public.foo
    IS 'Foo procedure';
-----

-----
2016-03-23 11:22:02,186: INFO werkzeug: 127.0.0.1 - - [23/Mar/2016
11:22:02] "GET /browser/procedure/obj/1/2/14844/13627/16387 HTTP/1.1"
500 -
Traceback (most recent call last):
  File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
line 1836, in __call__
    return self.wsgi_app(environ, start_response)
  File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
line 1820, in wsgi_app
    response = self.make_response(self.handle_exception(e))
  File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
line 1403, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
line 1817, in wsgi_app
    response = self.full_dispatch_request()
  File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
line 1477, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
line 1381, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
line 1475, in full_dispatch_request
    rv = self.dispatch_request()
  File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
line 1461, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/views.py",
line 84, in view
    return self.dispatch_request(*args, **kwargs)
  File "/Users/dpage/git/pgadmin4/web/pgadmin/browser/utils.py", line
233, in dispatch_request
    return method(*args, **kwargs)
  File "/Users/dpage/git/pgadmin4/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/__init__.py",
line 349, in wrap
    return f(*args, **kwargs)
  File "/Users/dpage/git/pgadmin4/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/__init__.py",
line 425, in properties
    resp_data = self._fetch_properties(gid, sid, did, scid, fnid)
  File "/Users/dpage/git/pgadmin4/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/__init__.py",
line 1097, in _fetch_properties
    resp_data = res['rows'][0]
IndexError: list index out of range
-----

- After a Python server reset, I get the following error when trying
to view SQL of a Procedure (seems like it thinks it's connected to
PG?):

TemplateNotFound: procedure/pg/sql/9.5_plus/node.sql
2016-03-23 11:28:52,852: INFO werkzeug: 127.0.0.1 - - [23/Mar/2016
11:28:52] "GET /browser/procedure/nodes/1/2/14844/2200/ HTTP/1.1" 500
-
Traceback (most recent call last):
  File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
line 1836, in __call__
    return self.wsgi_app(environ, start_response)
  File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
line 1820, in wsgi_app
    response = self.make_response(self.handle_exception(e))
  File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
line 1403, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
line 1817, in wsgi_app
    response = self.full_dispatch_request()
  File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
line 1477, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
line 1381, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
line 1475, in full_dispatch_request
    rv = self.dispatch_request()
  File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py",
line 1461, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/views.py",
line 84, in view
    return self.dispatch_request(*args, **kwargs)
  File "/Users/dpage/git/pgadmin4/web/pgadmin/browser/utils.py", line
233, in dispatch_request
    return method(*args, **kwargs)
  File "/Users/dpage/git/pgadmin4/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/__init__.py",
line 349, in wrap
    return f(*args, **kwargs)
  File "/Users/dpage/git/pgadmin4/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/__init__.py",
line 390, in nodes
    'node.sql']), scid=scid)
  File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/templating.py",
line 127, in render_template
    return _render(ctx.app.jinja_env.get_or_select_template(template_name_or_list),
  File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/jinja2/environment.py",
line 830, in get_or_select_template
    return self.get_template(template_name_or_list, parent, globals)
  File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/jinja2/environment.py",
line 791, in get_template
    return self._load_template(name, self.make_globals(globals))
  File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/jinja2/environment.py",
line 765, in _load_template
    template = self.loader.load(self, name, globals)
  File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/jinja2/loaders.py",
line 113, in load
    source, filename, uptodate = self.get_source(environment, name)
  File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/templating.py",
line 64, in get_source
    raise TemplateNotFound(template)
TemplateNotFound: procedure/pg/sql/9.5_plus/node.sql
2016-03-23 11:28:57,953: INFO werkzeug: 127.0.0.1 - - [23/Mar/2016
11:28:57] "POST /ping HTTP/1.1" 200 -

- Procedures do not include the schema name in the headers of reverse
engineered SQL, e.g.

- What's the trailing - in the parameter list in this reverse engineered SQL?

-- PROCEDURE: foo

-- DROP PROCEDURE foo;

CREATE OR REPLACE PROCEDURE public.foo(IN a integer DEFAULT 10, INOUT
b integer DEFAULT  -)
    VOLATILE NOT LEAKPROOF SECURITY DEFINER
    COST 100.0
AS

BEGIN
   b:=a+b+1;
END;

COMMENT ON PROCEDURE public.foo
    IS 'Foo procedure';

Thanks!

-- 
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 (pgadmin-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers