Обсуждение: Stored procedure large parameter list

On 25/10/2014 02:46, inspector morse wrote:
> Hello All,
> 
> We have a postgresql database that is using stored procedures
> exclusively to communicate with the web application. The stored
> procedures often have upto 50 input/output parameters! We're using the
> latest version of pgadmin3 in Debian Stable (1.14.2-2)
> 
> Anytime we try to edit the stored procedure parameters, the parameter
> list appears horizontally in pgadmin3.
> It doesn't matter if we vertically align them, the next time we edit
> it...the parameters are all horizontally aligned. We cannot see all of
> the parameters without scrolling horizontally...which really decreases
> our productivity.
> 
> Is it possible to keep the formatting for the stored procedure OR at the
> very least make it vertically aligned?

This is something I've often wished for also.... I sometimes have to
deal with large lists of parameters to functions.

What would be nice would be to have an extra node, listing parameters,
appear in the tree view below the function.

My C++ is limited, and I don't know WxWidgets at all, so I'm not in a
position to offer to help... but I'd be happy to test if such a feature
were ever implemented.

Ray.

-- 
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

Hello,

I've also faced with long parameters lists inconvenience.
It's an interesting idea to introduce function parameter nodes. But
their only functionality would be... just their existence in the tree?
Nothing to show in SQL Pane, no context menu actions, no stats,
dependencies, etc.
So I'm not sure if it's worth implementing that (?)

Regarding parameters formatting:
We should remember that PostgreSQL doesn't store whole function's
definition text but only it's body. Parameters, return values and
other attributes are saved into Postgre's catalog structure
(pg_catalog.pg_proc). So,
>> Is it possible to keep the formatting for the stored procedure
No.
But it's possible to make PgAdmin recreate function's definition query
in a most suitable way. FYI, it's not so hard to implement:
https://github.com/postgres/pgadmin3/blob/master/pgadmin/schema/pgFunction.cpp#L286
https://github.com/postgres/pgadmin3/blob/master/pgadmin/schema/pgFunction.cpp#L501 

The question is formatting considerations, which are a matter of taste:

CREATE OR REPLACE FUNCTION f1(   i_param1 integer,   i_param_with_very_very_long_name integer) RETURNS ...

CREATE OR REPLACE FUNCTION f2(   i_param1 integer  ,i_param_with_very_very_long_name integer) RETURNS ...

CREATE OR REPLACE FUNCTION f3(i_param1 integer                            ,i_param_with_very_very_long_name integer)
RETURNS...
 

Which one will suit everybody?

Also I think that vertical formatting shouldn't be applied where
number of parameters is less than two.


Saturday, October 25, 2014, 4:10:14 PM, Raymond O'Donnell wrote:

ROD> On 25/10/2014 02:46, inspector morse wrote:
>> Hello All,
>> 
>> We have a postgresql database that is using stored procedures
>> exclusively to communicate with the web application. The stored
>> procedures often have upto 50 input/output parameters! We're using the
>> latest version of pgadmin3 in Debian Stable (1.14.2-2)
>> 
>> Anytime we try to edit the stored procedure parameters, the parameter
>> list appears horizontally in pgadmin3.
>> It doesn't matter if we vertically align them, the next time we edit
>> it...the parameters are all horizontally aligned. We cannot see all of
>> the parameters without scrolling horizontally...which really decreases
>> our productivity.
>> 
>> Is it possible to keep the formatting for the stored procedure OR at the
>> very least make it vertically aligned?

ROD> This is something I've often wished for also.... I sometimes have to
ROD> deal with large lists of parameters to functions.

ROD> What would be nice would be to have an extra node, listing parameters,
ROD> appear in the tree view below the function.

ROD> My C++ is limited, and I don't know WxWidgets at all, so I'm not in a
ROD> position to offer to help... but I'd be happy to test if such a feature
ROD> were ever implemented.

ROD> Ray.

-- 
Best regards,J.F.

On Sat, Oct 25, 2014 at 6:37 PM, J.F. Oster <jinfroster@mail.ru> wrote:
> Hello,
>
> I've also faced with long parameters lists inconvenience.
> It's an interesting idea to introduce function parameter nodes. But
> their only functionality would be... just their existence in the tree?
> Nothing to show in SQL Pane, no context menu actions, no stats,
> dependencies, etc.
> So I'm not sure if it's worth implementing that (?)
>
> Regarding parameters formatting:
> We should remember that PostgreSQL doesn't store whole function's
> definition text but only it's body. Parameters, return values and
> other attributes are saved into Postgre's catalog structure
> (pg_catalog.pg_proc). So,
>>> Is it possible to keep the formatting for the stored procedure
> No.
> But it's possible to make PgAdmin recreate function's definition query
> in a most suitable way. FYI, it's not so hard to implement:
> https://github.com/postgres/pgadmin3/blob/master/pgadmin/schema/pgFunction.cpp#L286
> https://github.com/postgres/pgadmin3/blob/master/pgadmin/schema/pgFunction.cpp#L501
>
> The question is formatting considerations, which are a matter of taste:
>
> CREATE OR REPLACE FUNCTION f1(
>     i_param1 integer,
>     i_param_with_very_very_long_name integer)
>   RETURNS ...
>
> CREATE OR REPLACE FUNCTION f2(
>     i_param1 integer
>    ,i_param_with_very_very_long_name integer)
>   RETURNS ...
>
> CREATE OR REPLACE FUNCTION f3(i_param1 integer
>                              ,i_param_with_very_very_long_name integer)
>   RETURNS ...
>
> Which one will suit everybody?

I wouldn't want to see anything except the first.

> Also I think that vertical formatting shouldn't be applied where
> number of parameters is less than two.

Right.

>
> Saturday, October 25, 2014, 4:10:14 PM, Raymond O'Donnell wrote:
>
> ROD> On 25/10/2014 02:46, inspector morse wrote:
>>> Hello All,
>>>
>>> We have a postgresql database that is using stored procedures
>>> exclusively to communicate with the web application. The stored
>>> procedures often have upto 50 input/output parameters! We're using the
>>> latest version of pgadmin3 in Debian Stable (1.14.2-2)
>>>
>>> Anytime we try to edit the stored procedure parameters, the parameter
>>> list appears horizontally in pgadmin3.
>>> It doesn't matter if we vertically align them, the next time we edit
>>> it...the parameters are all horizontally aligned. We cannot see all of
>>> the parameters without scrolling horizontally...which really decreases
>>> our productivity.
>>>
>>> Is it possible to keep the formatting for the stored procedure OR at the
>>> very least make it vertically aligned?
>
> ROD> This is something I've often wished for also.... I sometimes have to
> ROD> deal with large lists of parameters to functions.
>
> ROD> What would be nice would be to have an extra node, listing parameters,
> ROD> appear in the tree view below the function.
>
> ROD> My C++ is limited, and I don't know WxWidgets at all, so I'm not in a
> ROD> position to offer to help... but I'd be happy to test if such a feature
> ROD> were ever implemented.
>
> ROD> Ray.
>
> --
> Best regards,
>  J.F.
>
>
>
> --
> Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgadmin-support



-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company