 |
SoftTree Technologies
Technical Support Forums
|
|
Author |
Message |
michalk
Joined: 29 Aug 2014 Posts: 211
|
|
Postgresql - function formating |
|
in Postgresql, function can return recordset where function uses following syntax:
 |
 |
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:
 |
 |
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 |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7948
|
|
|
|
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)
 |
 |
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.
 |
 |
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 |
|
 |
michalk
Joined: 29 Aug 2014 Posts: 211
|
|
|
|
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 |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7948
|
|
|
|
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 |
|
 |
michalk
Joined: 29 Aug 2014 Posts: 211
|
|
|
|
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 $$
 |
 |
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:
 |
 |
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 |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7948
|
|
|
|
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 |
|
 |
michalk
Joined: 29 Aug 2014 Posts: 211
|
|
|
|
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 |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7948
|
|
|
|
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 |
|
 |
|
|
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
|
|
|