SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[SA 9.0.166 Pro] - PostgreSql - funct vs proc inconsistence

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[SA 9.0.166 Pro] - PostgreSql - funct vs proc inconsistence
Author Message
michalk



Joined: 29 Aug 2014
Posts: 211

Post [SA 9.0.166 Pro] - PostgreSql - funct vs proc inconsistence Reply with quote
I believe I reported it already for SA v7.
But I'm ready to do this again.

First of all, postgresql doesn't distinguish function and procedure. All of them are functions.
But splitting functions onto categories is not bad idea at all*), as long as it's consistent.

I assume functions are term for methods which return single values while procedures return sets of values.
SA does some little mess in this.

1. functions returning void are in Procedure category. It should be in Functions one
2. functions returning TABLE are in Functions category. It should be in Procedure.

*) I would like to have possibility to join functions and procedures all together into single subtree of objects. It's because all of them are functions in postgresql. However I would rather stay with trigger functions separated. In short: pgAdmin3 style. If there are users who prefer more categories, then best way is to make selectable option.

With regards.


SA 9.0.166 Pro
Postgresql 9.2
Native Connection
Editor 64bit
Thu Nov 03, 2016 12:15 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Reply with quote
Quote:
I assume functions are term for methods which return single values while procedures return sets of values.
SA does some little mess in this.


I believe if the result is void the it goes to procedures, else goes to functions. This is done for internal reasons to allow common SQL Assistant functions to work with PG the same way they are used with all other databases.
Thu Nov 03, 2016 2:00 pm View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 211

Post Reply with quote
Then it's more than unjustified.
First of all, GUI representation must not be in pair with how application handles those objects.
Second, IMO your approach doesn't match meaning of 'function' and 'procedure' RDBMS wise.

However, I looked into SA Queries, and found following code in Objects queries
Code:

CASE format_type (t.oid, NULL)
          WHEN 'trigger'    THEN 'PC'
          WHEN 'void'    THEN 'PR'
          WHEN 'record'    THEN 'FT'
       ELSE              'FU'
END


I turned it into

Code:
CASE format_type (t.oid, NULL)
          WHEN 'trigger'    THEN 'PC'
        ELSE           'FU'
END


making all functions (except trigger ones) be located in Functions branch. It meets perfectly my requirements. Should I expect some negative impacts?

BTW the case contain RECORD returned functions as separate category. Is it done this way for internal reasons? Just FYI, RECORD is just composite type, like array of varchar or json. Nonetheless. Just saying.
Fri Nov 04, 2016 7:46 am View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 211

Post Reply with quote
I answer myself a question about impact. Yes, the CASE options cannot be modified, because it causes SA stop to suggest function arguments.

Please revise whole part of SA related to splitting functions into categories
If I were you, I would create 2 CASE queries in Objetcs sql. One for internal usage, second for UI.

with regards


Last edited by michalk on Fri Nov 04, 2016 8:21 am; edited 3 times in total
Fri Nov 04, 2016 8:10 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Reply with quote
Quote:
Please revise whole part of SA related to splitting functions into categories
If I were you, I would create 2 CASE queries in Objetcs sql. One for internal usage, second for UI.


Thanks for your suggestion. I will pass it along.

I'm afraid it's not as simple as that. SQL Assistant is a cross-database development system. It attempts to present things in different databases in a consistent manner. Many of SQL Assistant users work with multiple databases. So there are multiple dependencies inside than what meets an eye in the Database Explorer view. For example, one can use SQL Assistant to compare schemas across different database types, to transfer data across database types, and so on... the SQL Intellisense is a dependency too, it uses the same internal structures and caches for all database types and it needs uniform internal data presentation.

You can certainly tweak the queries in the configuration to return the results you like, just please be careful with them, the changes may improve the display but break something else, which may or may not be a concern if you work with a subset of functions only.
Fri Nov 04, 2016 9:49 am View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 211

Post Reply with quote
Thanks for explanation. I can imagine complexity of internals as I'm programmer (not db only).
But right now we are considering UI presentation which is (should be) independent from internal one.

Thank you for your involvement. I'm looking forward to some changes.
with regards.
Fri Nov 04, 2016 10:12 am View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 211

Post Reply with quote
After some while I would like to rise up this issue again.
Splitting functions into 2 categories by void/non-void return type makes a work more difficult while the difference is meaningless (in case of postgresql at least)

The best option would be filtering functions into folders using optional, user-defined filters, for example:
- is return type record/record set - allowing to split functions and stored procedures (important for databases which differentiate those types, like mysql)
- is return type trigger - allowing to separate trigger functions from other ones (useful for postgresql)
- others, based on argument types, argument appearance, name pattern etc, as for customizing for personal needs

I can imagine it might be most demanding, but the result will be more flexible (across various dbms) giving significant return value for users as well as for future development.
Thu Jan 19, 2017 6:51 am View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 211

Post Reply with quote
Hello
Guys, please give some love to this issue.
Splitting postgresql functions into separate folders by void non-void return type really hurts.
Would be OK if all functions will be in the single folder
Tue May 23, 2017 9:20 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Reply with quote
I submitted new change request to make that separation optional and configurable.
Tue May 23, 2017 10:52 am View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 211

Post Reply with quote
Hello,
Is this feature available already (in SA10)?

As you probably know, postgresql v11 supports real procedures. Since this Procedures folder should contain procedures only, while all functions (regardless returned datatype) should be put into Functions folder.

with regards
Wed Nov 07, 2018 12:59 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Reply with quote
We have this behavioral change scheduled for 10.1 maintenance release in which we will provide management interface for PG 11. As part of the change there will be proper separation of functions and procedures, they won't be mixed together.

PS. In my personal opinion PG development community took the right turn in finally after so many years embraced that procedures and functions play have different purposes. Or maybe take a step further, and accept that functions not returning anything are indeed procedures used to execute operations, not calculate results, so they should be called by their name "procedures" and shouldn't need a SELECT statement to execute them. Hope one day they accept that there is also a place for triggers instead of trigger functions.
Thu Nov 08, 2018 2:08 am View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 211

Post Reply with quote
It's great news. I'm happy to hear you are keep tracking new versions of databases
Thu Nov 08, 2018 4:45 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.