1.. -*- coding: utf-8 -*- 2.. :Project: pglast -- Usage 3.. :Created: gio 10 ago 2017 10:06:38 CEST 4.. :Author: Lele Gaifax <lele@metapensiero.it> 5.. :License: GNU General Public License version 3 or later 6.. :Copyright: © 2017, 2018, 2019 Lele Gaifax 7.. 8 9=================== 10 Examples of usage 11=================== 12 13Here are some example of how the module can be used. 14 15Parse an ``SQL`` statement and get its *AST* root node 16====================================================== 17 18.. doctest:: 19 20 >>> from pglast import Node, parse_sql 21 >>> root = Node(parse_sql('SELECT foo FROM bar')) 22 >>> print(root) 23 None=[1*{RawStmt}] 24 25Recursively :meth:`traverse <pglast.node.Node.traverse>` the parse tree 26========================================================================= 27 28.. doctest:: 29 30 >>> for node in root.traverse(): 31 ... print(node) 32 ... 33 None[0]={RawStmt} 34 stmt={SelectStmt} 35 fromClause[0]={RangeVar} 36 inh=<True> 37 location=<16> 38 relname=<'bar'> 39 relpersistence=<'p'> 40 op=<0> 41 targetList[0]={ResTarget} 42 location=<7> 43 val={ColumnRef} 44 fields[0]={String} 45 str=<'foo'> 46 location=<7> 47 48As you can see, the ``repr``\ esentation of each value is mnemonic: ``{some_tag}`` means a 49``Node`` with tag ``some_tag``, ``[X*{some_tag}]`` is a ``List`` containing `X` nodes of that 50particular kind\ [*]_ and ``<value>`` is a ``Scalar``. 51 52Get a particular node 53===================== 54 55.. doctest:: 56 57 >>> from_clause = root[0].stmt.fromClause 58 >>> print(from_clause) 59 fromClause=[1*{RangeVar}] 60 61Obtain some information about a node 62==================================== 63 64.. doctest:: 65 66 >>> range_var = from_clause[0] 67 >>> print(range_var.node_tag) 68 RangeVar 69 >>> print(range_var.attribute_names) 70 dict_keys(['relname', 'inh', 'relpersistence', 'location']) 71 >>> print(range_var.parent_node) 72 stmt={SelectStmt} 73 74Iterate over nodes 75================== 76 77.. doctest:: 78 79 >>> for a in from_clause: 80 ... print(a) 81 ... for b in a: 82 ... print(b) 83 ... 84 fromClause[0]={RangeVar} 85 inh=<True> 86 location=<16> 87 relname=<'bar'> 88 relpersistence=<'p'> 89 90Programmatically :func:`reformat <pglast.prettify>` a ``SQL`` statement\ [*]_ 91=============================================================================== 92 93.. doctest:: 94 95 >>> from pglast import prettify 96 >>> print(prettify('delete from sometable where value is null')) 97 DELETE FROM sometable 98 WHERE value IS NULL 99 100.. doctest:: 101 102 >>> print(prettify('select a,b,c from sometable where value is null')) 103 SELECT a 104 , b 105 , c 106 FROM sometable 107 WHERE value IS NULL 108 109.. doctest:: 110 111 >>> print(prettify('select a,b,c from sometable' 112 ... ' where value is null or value = 1', 113 ... comma_at_eoln=True)) 114 SELECT a, 115 b, 116 c 117 FROM sometable 118 WHERE (value IS NULL 119 OR (value = 1)) 120 121Customize a :func:`node printer <pglast.printer.node_printer>` 122================================================================ 123 124.. doctest:: 125 126 >>> sql = 'update translations set italian=$2 where word=$1' 127 >>> print(prettify(sql)) 128 UPDATE translations 129 SET italian = $2 130 WHERE word = $1 131 >>> from pglast.printer import node_printer 132 >>> @node_printer('ParamRef', override=True) 133 ... def replace_param_ref(node, output): 134 ... output.write(repr(args[node.number.value - 1])) 135 ... 136 >>> args = ['Hello', 'Ciao'] 137 >>> print(prettify(sql, safety_belt=False)) 138 UPDATE translations 139 SET italian = 'Ciao' 140 WHERE word = 'Hello' 141 142:func:`Iterate <pglast.split>` over each statement 143================================================== 144 145.. doctest:: 146 147 >>> from pglast import split 148 >>> for statement in split('select 1; select 2'): 149 ... print(statement) 150 ... 151 SELECT 1 152 SELECT 2 153 154Reformat a ``SQL`` statement from the command line 155================================================== 156 157.. code-block:: shell 158 159 $ echo "select a,b,c from sometable" | pgpp 160 SELECT a 161 , b 162 , c 163 FROM sometable 164 165 $ echo 'update "table" set value=123 where value is null' | pgpp 166 UPDATE "table" 167 SET value = 123 168 WHERE value IS NULL 169 170 $ echo " 171 insert into t (id, description) 172 values (1, 'this is short enough'), 173 (2, 'this is too long, and will be splitted')" | pgpp -s 20 174 INSERT INTO t (id, description) 175 VALUES (1, 'this is short enough') 176 , (2, 'this is too long, an' 177 'd will be splitted') 178 179Get a more compact representation 180================================= 181 182.. code-block:: shell 183 184 $ echo "select a,b,c from st where a='longvalue1' and b='longvalue2'" \ 185 | pgpp --compact 30 186 SELECT a, b, c 187 FROM st 188 WHERE (a = 'longvalue1') 189 AND (b = 'longvalue2') 190 191.. code-block:: shell 192 193 $ echo "select a,b,c from st where a='longvalue1' and b='longvalue2'" \ 194 | pgpp --compact 60 195 SELECT a, b, c 196 FROM st 197 WHERE (a = 'longvalue1') AND (b = 'longvalue2') 198 199Obtain the *parse tree* of a ``SQL`` statement from the command line 200==================================================================== 201 202.. code-block:: shell 203 204 $ echo "select 1" | pgpp --parse-tree 205 [ 206 { 207 "RawStmt": { 208 "stmt": { 209 "SelectStmt": { 210 "op": 0, 211 "targetList": [ 212 { 213 "ResTarget": { 214 "location": 7, 215 "val": { 216 "A_Const": { 217 "location": 7, 218 "val": { 219 "Integer": { 220 "ival": 1 221 } 222 } 223 } 224 } 225 } 226 } 227 ] 228 } 229 } 230 } 231 } 232 ] 233 234 235--- 236 237.. [*] This is an approximation, because in principle a list could contain different kinds of 238 nodes, or even sub-lists in some cases: the ``List`` representation arbitrarily shows 239 the tag of the first object. 240 241.. [*] Currently this covers most `DML` statements such as ``SELECT``\ s, ``INSERT``\ s, 242 ``DELETE``\ s and ``UPDATE``\ s, fulfilling my needs, but I'd like to extend it to 243 handle also `DDL` statements and, why not, `PLpgSQL` instructions too. 244