SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Postgresql - function formating

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
Postgresql - function formating
Author Message
michalk



Joined: 29 Aug 2014
Posts: 209

Post Postgresql - function formating Reply with quote
in Postgresql, function can return recordset where function uses following syntax:

Code:

CREATE OR REPLACE FUNCTION some_func
(
   param1    CHARACTER VARYING DEFAULT NULL : : CHARACTER VARYING,
   param2    INTEGER[] DEFAULT NULL : : INTEGER[],
   param3    INTEGER[] DEFAULT NULL : : INTEGER[],   
)
RETURNS TABLE
(
   col_name1       VARCHAR,
   col_something2  INTEGER,
   col3            SMALLINT
)
AS


Point is, I cannot get table syntax to be formatted like in example above. I mean formatting datatypes to be aligned as in argument block
I've added formatting rule:
Code:

TABLE
(
   ...,
   ...
)


but it doesn't work. Any clue what can I do to enable formatting for this block?

BTW as you can see in example above, spaces de-formatted by some spaces. Double colon is common method to cast/define datatype in postgresql. There should be no spaces around or between colons.

I can see another glitch. for example DECLARE is always indented. IMO it should be as aligned as BEGIN statement
In create function and function format rule, I can see IS keyword but actually shouldn't it be: AS?

with regards
Wed Jun 24, 2015 10:22 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6738

Post Reply with quote
I played a bit with this kind of code and here is what I found out. In PG formatting rules in the options there are two kind of contradicting rules named "create function" and "function". Note that rules names are just names they have no significance, but their order does because they are matched and applied sequentially.

1. Disable the "function" rule, you don't really need it.
2. Modify the "create function" rule as the following (note I changed CREATE FUNCTION to CREATE ... FUNCTION in order to support all cases of CREATE and CREATE OR REPLACE, and also changed IS to AS)

Code:
CREATE ... FUNCTION ...
(
   ...,
   ...
)
RETURNS ...
AS
   <stmtList>
BEGIN
   <stmtList>
EXCEPTION...
   WHEN ... THEN
      <stmtList>
END



3. Make sure your new "table" rule goes after the "create function" rule.

Hope that works for you.


I did a few simple tests and I think code like below is properly formatted. I haven't tried complex cases with a bunch of composite statements.

Code:
CREATE OR REPLACE FUNCTION some_func
(
   param1     CHARACTER VARYING DEFAULT NULL : : CHARACTER VARYING,
   param2     INTEGER[] DEFAULT NULL : : INTEGER[],
   param3     INTEGER[] DEFAULT NULL : : INTEGER[],
)
RETURNS TABLE
        (
           col_name1 VARCHAR,
           col_something2 INTEGER,
           col3 SMALLINT
        )
AS
   'SELECT * FROM mytable' LANGUAGE SQL;

Thu Jun 25, 2015 9:43 am View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 209

Post Reply with quote
Sorry to say but you missed the point of my request.
Please look at how TABLE fields are formatted. Compare my (requested) example with your result. In your result fields and their datatypes are not formatted into 2 columns (like function arguments are)

I have very similar problem with variables in DECLARE block. Those are indented but datatyes are not aligned into vertical column.

And what about double-colons?
Fri Jun 26, 2015 5:55 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6738

Post Reply with quote
If you mean the alignment of the datatypes in the TABLE part, I'm afraid it cannot be done in the formatting rule using a formatting patterns. The alignment of the data types is a separate function which currently recognizes only predefined syntax elements such as columns in CREATE TABLE statements and parameters between brackets in CREATE FUNCTION and CREATE PROCEDURE statements. I believe it also recognizes DECLARE blocks in PG, Oracle, DB2, and DECLARE statements in SQL Server and Sybase.
Fri Jun 26, 2015 2:49 pm View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 209

Post Reply with quote
It doesn't align datatypes in DECLARE block (at least not for postgresql
Also by default it doesn't take care about $$ delimiter.
I modified CREATE FUNCTION template a bit to avoid printing $BODY$ and DECLARE in the same line. Problem with this delimiter is, that it might be any string between dollar signs. i.e. $BODY$ is returned by pgAdmin, while native postgresql functions to get function definition make use of $FUNCTION$ delimiter. It might be also simply $$

Code:

CREATE ... FUNCTION ...
(
   ...,
   ...
)
RETURNS ...
AS
...
DECLARE
   <stmtList>
BEGIN
   <stmtList>
EXCEPTION...
   WHEN ... THEN
      <stmtList>
END


However it still doesn't take care about aligment of datatypes in declare part. I would suggest to add some new template format for formatting with aligned datatypes (for example ... ...) . Or just add (hardcode) aligning datatypes for TABLE and DECLARE
Also there is minor issue with function argument formatting.
If argument(s) is/are proceeded by IN/OUT keyword, datatypes are not aligned properly. I know it might be pain-in-ass to recognize it, but... you know ;)
See example how it behaves:

Code:

CREATE OR REPLACE FUNCTION statistiky.motivacni_soutez
(
   IN      d_od DATE,
   IN      d_do DATE,
   IN      min_pocet INTEGER,
   IN      min_vsa INTEGER,
   OUT     _jmeno CHARACTER VARYING,
   OUT     _osc INTEGER,
   OUT     _mid INTEGER,
)


BTW how formatter manage alternatives? For example, In postgresql you can have DECLARE block or have no one. The same for EXCEPTION block. Also functions written in sql language has a bit different content than plpgsql ones.

with regards
Mon Jun 29, 2015 10:50 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6738

Post Reply with quote
Thank you so much for your valuable feedback. Let me share this with the development team and hear their input. I will get back on this topic in a couple of days.
Mon Jun 29, 2015 2:23 pm View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 209

Post Reply with quote
Hello again.
Can I bump this subject yet again?
It seems, DECLARE block still remains not formatted into 2 aligned columns. Checked with SA 9.5.452

With regards


Fri Mar 02, 2018 6:26 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6738

Post Reply with quote
I opened a new ticket #SA0033352 for formatting of variables in the DECLARE section, and will follow up on this as soon as I hear back from the team.
Fri Mar 02, 2018 1:15 pm 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.