[SA 9.0.166 Pro] - PostgreSql - funct vs proc inconsistence

[SA 9.0.166 Pro] - PostgreSql - funct vs proc inconsistence

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

	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.

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.

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

	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.

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.

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.

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

I submitted new change request to make that separation optional and configurable.

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

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.

It's great news. I'm happy to hear you are keep tracking new versions of databases