Hello!
First of all I'd like to thank all the pgAdmin developers for a great
job!
As I like to have nice formatted SQL, I've written simple script in
python to format SQL procedures. It would be nice, to integrate it with
pgAdmin.
This script doesn't cover all PostgreSQL structures, but is enough for
me. If there will be some interest, I can improve it.
I think that it should be enough to add call to this script from SQL
query dialog menu. I've no experience with wxWidgets, so it may be
better if someone more skilled will integrate it.
best regards
Jarek Tabor
#!/usr/bin/env python
# -*- coding: utf-8 -*-
#
# Copyright (c) 2014-2015 Jarosław Tabor <[email protected]>
#
# Permission is hereby granted, free of charge, to any person obtaining a copy
# of this software and associated documentation files (the "Software"), to deal
# in the Software without restriction, including without limitation the rights
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the Software is
# furnished to do so, subject to the following conditions:
#
# The above copyright notice and this permission notice shall be included in all
# copies or substantial portions of the Software.
#
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
# SOFTWARE.
import sys
import re
indent_keywords = ['BEGIN', 'IF', 'FOR']
unindent_keywords = ['END']
uindent_once_keywords = ['ELSE']
indent_once = ['DECLARE']
indentsize = 4
function_re = re.compile('^CREATE[\ ]*(OR[\ ]*REPLACE[\ ]*)*FUNCTION')
function_body_re = re.compile('\$.*\$')
function_declare_re = re.compile('DECLARE')
function_begin_re = re.compile('BEGIN')
function_end_re = re.compile('END;')
function_endsc_re = re.compile(';')
if_re = re.compile('^IF')
endif_re = re.compile('^END IF')
else_re = re.compile('^(ELSE|ELSIF)')
for_re = re.compile('^(FOR.*LOOP|LOOP)')
endfor_re = re.compile('^END[\ ]*LOOP')
exception_re = re.compile('^EXCEPTION')
query_re = re.compile('^(SELECT|PERFORM|UPDATE|DELETE)')
def indent_print( _indent, _line ):
out = ''
if _line != '':
if( _indent > 0 ):
for i in range(_indent * indentsize):
out = out + ' '
print out + _line
else:
print
def main(_file):
indent_level = 0
function_body = 0
query = 0
with open(_file) as f:
n = 0
for line in f:
n = n + 1
out = ''
line=line.strip()
if function_re.match( line ): # function start
function_body = 1
indent_level = 1
print line
continue
if function_body_re.match( line ): #function body
if function_body==1:
function_body = 2
elif function_body==5:
function_body = 6
else:
sys.stderr.write('Unexpected $$ keyword in %d\n' % n )
print line
continue
if function_declare_re.match( line ):
if function_body == 2:
function_body = 3
else:
sys.stderr.write('Unexpected DECLARE keyword in %d\n' % n )
print line
continue
if function_begin_re.match( line ):
if function_body == 2 or function_body == 3:
function_body = 4
print line
else:
indent_print(indent_level,line)
indent_level = indent_level + 1
continue
if function_end_re.match( line ):
if function_body == 4:
if indent_level > 1:
indent_level = indent_level -1
indent_print(indent_level,line)
continue
else:
function_body = 5
else:
sys.stderr.write('Unexpected END; keyword in %d\n' % n )
print line
continue
if function_body == 6 and (';' in line):
function_body = 0
indent_print(indent_level,line)
indent_level = 0
continue
if if_re.match( line ):
indent_print(indent_level,line)
indent_level = indent_level + 1
continue
if else_re.match( line ):
indent_print(indent_level-1,line)
continue
if exception_re.match( line ):
indent_print(indent_level-1,line)
continue
if endif_re.match(line):
if indent_level > 0:
indent_level = indent_level-1
if for_re.match(line):
indent_print(indent_level,line)
indent_level = indent_level + 1
continue
if endfor_re.match(line):
if indent_level > 0:
indent_level = indent_level-1
if query_re.match(line):
#print '*********query 1'
if not ';' in line:
query = 1
indent_print(indent_level,line)
indent_level = indent_level + 1
continue
if (';' in line) and (query>0):
#print '*********query 0'
if indent_level > 0:
query = query -1
indent_print(indent_level,line)
indent_level = indent_level-1
continue
indent_print(indent_level,line)
if __name__ == "__main__":
if len(sys.argv) > 1:
main(sys.argv[1])
else:
sys.stderr.write('No input file provided.\n' )
--
Sent via pgadmin-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers
