4
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
5
<link rel="STYLESHEET" type="text/css" href="pgadmin3.css">
6
<title>pgScript Scripting Language Reference</title>
11
<h3>pgScript Scripting Language Reference</h3>
13
<p><b>Table of Contents</b></p>
15
<li><a href="#overview">Overview</a></li>
16
<li><a href="#examples">Examples</a></li>
18
<li><a href="#example1">Batch table creations</a></li>
19
<li><a href="#example2">Insert random data</a></li>
20
<li><a href="#example3">Batch table deletions</a></li>
21
<li><a href="#example4">Print information on screen</a></li>
23
<li><a href="#commands">SQL Commands</a></li>
24
<li><a href="#variables">Variables</a></li>
26
<li><a href="#variable1">Simple variables</a></li>
27
<li><a href="#variable2">Records</a></li>
28
<li><a href="#variable3">Cast</a></li>
29
<li><a href="#variable4">Operations</a></li>
31
<li><a href="#control">Control-of-flow structures</a></li>
33
<li><a href="#control1">Conditional structure</a></li>
34
<li><a href="#control2">Loop structure</a></li>
35
<li><a href="#control3">Conditions</a></li>
37
<li><a href="#functions">Additional functions and procedures</a></li>
39
<li><a href="#function1">Procedures</a></li>
40
<li><a href="#function2">Functions</a></li>
42
<li><a href="#generators">Random data generators</a></li>
44
<li><a href="#generator1">Overview of the generators</a></li>
45
<li><a href="#generator2">Sequence and seeding</a></li>
46
<li><a href="#generator3">Data generators</a></li>
50
<!-- ---------------------------------------------------------------------- -->
52
<h3><a name="overview"></a>Overview</h3>
53
<p>pgScript is composed of pgScript commands:</p>
54
<pre class="screen">pgScript command
55
: Regular PostgreSQL SQL Command (SELECT INSERT CREATE ...)
56
| Variable declaration or assignment (DECLARE SET)
57
| Control-of-flow structure (IF WHILE)
58
| Procedure (ASSERT PRINT LOG RMLINE)</pre>
59
<p>Command names (<strong class="command">SELECT</strong>, <strong class="command">IF</strong>, <strong class="command">SET</strong>, ...) are case-insensitive and must be ended with a
60
semi-column <code>;</code>. Identifiers are case-sensitive.</p>
62
<!-- ---------------------------------------------------------------------- -->
64
<h3><a name="examples"></a>Examples</h3>
65
<h4><a name="example1"></a>Batch table creations</h4>
66
<pre class="screen">DECLARE @I, @T; -- Variable names begin with a @
67
SET @I = 0; -- @I is an integer
70
SET @T = 'table' + CAST (@I AS STRING); -- Casts @I
71
CREATE TABLE @T (id integer primary key, data text);
75
<h4><a name="example2"></a>Insert random data</h4>
76
<pre class="screen">DECLARE @I, @J, @T, @G;
78
SET @G1 = INTEGER(10, 29, 1); /* Random integer generator
79
Unique numbers between 10 and 29 */
80
SET @G2 = STRING(10, 20, 3); /* Random string generator
81
3 words between 10 and 20 characters */
84
SET @T = 'table' + CAST (@I AS STRING);
89
INSERT INTO @T VALUES (@G1, '@G2');
95
<h4><a name="example3"></a>Batch table deletions</h4>
96
<pre class="screen">DECLARE @I, @T; -- Declaring is optional
98
WHILE 1 -- Always true
101
BREAK; -- Exit the loop if @I > 20
103
SET @T = 'table' + CAST (@I AS STRING);
108
<h4><a name="example4"></a>Print information on screen</h4>
109
<pre class="screen">SET @PROGR@M#TITLE = 'pgScript';
111
PRINT @PROGR@M#TITLE + ' features:';
113
PRINT ' * Regular PostgreSQL commands';
114
PRINT ' * Control-of-flow language';
115
PRINT ' * Local variables';
116
PRINT ' * Random data generators';</pre>
118
<!-- ---------------------------------------------------------------------- -->
120
<h3><a name="commands"></a>SQL Commands</h3>
121
<p>You can run ANY PostgreSQL query from a pgScript EXCEPT those
123
<pre class="screen">BEGIN;
125
<p>This is because <strong class="command">BEGIN</strong> and <strong class="command">END</strong> are used for delimiting blocks. Instead
127
<pre class="screen">BEGIN TRANSACTION;
128
END TRANSACTION;</pre>
129
<p>For a list of PostgreSQL commands: <a href="http://www.postgresql.org/docs/8.3/interactive/sql-commands.html" target="_blank">http://www.postgresql.org/docs/8.3/interactive/sql-commands.html</a></p>
131
<!-- ---------------------------------------------------------------------- -->
133
<h3><a name="variables"></a>Variables</h3>
134
<p>There are two main types of variables : simple variables and records
135
(result sets composed of lines and columns).</p>
136
<p>Variable names begin with a <code>@</code> and can be composed of
137
letters, digits, <code>_</code>, <code>#</code>, <code>@</code>.</p>
138
<p>Variable type is guessed automatically according to the kind of
139
value it contains. This can be one of: number (real or integer), string,
141
<h4><a name="variable1"></a>Simple variables</h4>
142
<h5>Simple variable declaration</h5>
143
<p>Declaring simple variable is optional.</p>
144
<pre class="screen">DECLARE @A, @B;
146
<h5>Simple variable affectation</h5>
147
<p>This is done with the <strong class="command">SET</strong> command. The
148
variable type depends on the value assigned to this variable.</p>
149
<pre class="screen">SET @A = 1000, @B = 2000; -- @A and @B are <strong>integer numbers</strong>
150
SET @C = 10e1, @D = 1.5; -- @C and @D are <strong>real numbers</strong>
151
SET @E = 'ab', @F = 'a''b'; -- @E and @F are <strong>strings</strong>
152
SET @G = "ab", @H = "a\"b"; -- @G and @H are <strong>strings</strong></pre>
153
<p>An uninitialized variable defaults to an empty string. It is
154
possible to override variables as many times as wanted.</p>
155
<pre class="screen">PRINT @A; -- Prints an empty string
156
SET @A = 1000; -- @A is initialized an integer
157
PRINT @A; -- Prints 1000
158
SET @A = 'ab'; -- @A becomes a string
159
PRINT @A; -- Prints ab</pre>
160
<h5>Data generators</h5>
161
<p>Data generators allows users to generate random values. There
162
are various types of generators, each one producing different type
163
of data. A variable initialized with a data generator behaves like a
164
regular simple variable except that it has a different value each
166
<pre class="screen">SET @A = INTEGER(100, 200);
167
PRINT @A; -- Prints an integer between 100 and 200
168
PRINT @A; -- Prints another integer between 100 and 200</pre>
169
<p>A variable can contain a generator but its type is one of:
170
number (real or integer), string. For a list of available generators
171
and their associated type, see <a href="#generators">Random data generators</a>.</p>
172
<h4><a name="variable2"></a>Records</h4>
173
<h5>Record declaration</h5>
174
<p>Declaring a record is <strong>required</strong>.
175
A name for each column must be specified even if they will not be used
176
anymore afterwards.</p>
177
<pre class="screen">DECLARE @R1 { @A, @B }, @R2 { @A, @C }; -- Two records with two columns
178
DECLARE @R3 { @A, @B, @C, @D }; -- One record with four columns</pre>
179
<p>The number of lines is dynamic: see the next section.</p>
180
<h5>Record affectation</h5>
181
<p>To access a specific location in a record, one must use the line
182
number (starts at 0) and can use either the column name (between
183
quotes) or the column number (starts at 0). This specific location
184
behaves like a simple variable. Note that a record cannot contain a
186
<pre class="screen">SET @R1[0]['@A'] = 1; -- First line & first column
187
SET @R1[0][0] = 1; -- Same location
188
SET @R1[4]['@B'] = 1; -- Fifth line & second column
189
SET @R1[0][1] = 1; -- Same location</pre>
190
<p>In the above example, three empty lines are automatically
191
inserted between the first and the fifth. Using an invalid column
192
number or name results in an exception.</p>
193
<p>Specific location can be used as right values as well. A
194
specific line can also be used as right value.</p>
195
<pre class="screen">SET @R1[0][0] = @R3[0][1], @A = @R2[0][0]; -- Behaves like simple variables
196
SET @A = @R1[1]; -- @A becomes a record which is the first line of @R1</pre>
197
<p>Remember that <code>SET @R1[0][0] = @R2</code> is impossible
198
because a record cannot contain a record.</p>
199
<p>It is possible to assign a record to a variable, in this case
200
the variable does not need to be declared:</p>
201
<pre class="screen">SET @A = @R3; -- @A becomes a record because it is assigned a record</pre>
203
<p>Any SQL query executed returns a record. If the query is a <code>SELECT</code> query then it returns the results of the query. If
204
it is something else then it returns a one-line record
205
(<code>true</code>) if this is a success otherwise a zero-line record
206
(<code>false</code>).</p>
207
<pre class="screen">SET @A = SELECT * FROM table; -- @A is a record with the results of the query
208
SET @B = INSERT INTO table ...; -- @B is a one-line record if the query succeeds</pre>
209
<h5>Record functions</h5>
210
<p>See <a href="#function2">Functions</a>.</p>
211
<h4><a name="variable3"></a>Cast</h4>
212
<p>It is possible to convert a variable from one type to another with
213
the cast function:</p>
214
<pre class="screen">SET @A = CAST (@B AS STRING);
215
SET @A = CAST (@B AS REAL);
216
SET @A = CAST (@B AS INTEGER);
217
SET @A = CAST (@B AS RECORD);</pre>
218
<p>When a record is converted to a string, it is converted to its flat
219
representation. When converted to a number, the record is first converted
220
to a string and then to a number (see string conversion for more
222
<p>When a number is converted to a string, it is converted to its
223
string representation. When converted to a record, it is converted to a
224
one-line-one-column record whose value is the number.</p>
225
<p>When a string is converted to a number, if the string represents a
226
number then this number is returned else an exception is thrown. When
227
converted to a record, either the program can find a <strong>record pattern</strong> in the string or it converts it to
228
a one-line-one-column record whose value is the string. A record pattern
230
<pre class="screen">SET @B = '(1, "abc", "ab\\"")(1, "abc", "ab\\"")'; -- @B is a string
231
SET @B = CAST (@B AS RECORD); @B becomes a two-line-three-column record</pre>
232
<p>Remember a string is surrounded by simple quotes. Strings
233
composing a record must be surrounded by double quotes which are escaped
234
with <code>\\</code> (we double the slash because it is already a
235
special character for the enclosing simple quotes).</p>
236
<h4><a name="variable4"></a>Operations</h4>
237
<p>Operations can only be performed between operands of the same
238
type. Cast values in order to conform to this criterion.</p>
239
<p>Comparisons result in a number which is 0 or 1.</p>
241
<p>Comparisons: <code>= <> > < <= >= AND
243
<p>Concatenation: <code>+</code></p>
244
<pre class="screen">SET @B = @A + 'abcdef'; -- @A must be a string and @B will be a string</pre>
245
<p>Boolean value: non-empty string is <code>true</code>, empty
246
string is <code>false</code></p>
247
<p>Inverse boolean value: <code>NOT</code></p>
248
<p>Case-insensitive comparison: <code>~=</code></p>
250
<p>Comparisons: <code>= <> > < <= >= AND
252
<p>Arithmetic: <code>+ - * / %</code></p>
253
<pre class="screen">SET @A = CAST ('10' AS INTEGER) + 5; -- '10' string is converted to a number</pre>
254
<p>Boolean value: 0 is <code>false</code>, anything else is <code>true</code></p>
255
<p>Inverse boolean value: <code>NOT</code> (note that <code>NOT NOT
257
<p>An arithmetic operation involving at least one real number gives
258
a real number as a result:</p>
259
<pre class="screen">SET @A = 10 / 4.; -- 4. is a real so real division: @A = 2.5
260
SET @A = 10 / 4; -- 4 is an integer so integer division: @A = 2</pre>
262
<p>Comparisons: <code>= <> > < <= >= AND
264
<p>Boolean value: zero-line record is <code>false</code>, anything
265
else is <code>true</code></p>
266
<p>Inverse boolean value: <code>NOT</code></p>
267
<p>Comparisons for records are about inclusion and exclusion. Order
268
of lines does not matter. <code><=</code> means that each row in
269
the left operand has a match in the right operand. <code>>=</code> means the opposite. <code>=</code> means that <code><=</code> and <code>>=</code> are both true at the same time...</p>
270
<p>Comparisons are performed on strings: even if a record contains
271
numbers like <code>10</code> and <code>1e1</code> we will have <code>'10' <> '1e1'</code>.</p>
273
<!-- ---------------------------------------------------------------------- -->
275
<h3><a name="control"></a>Control-of-flow structures</h3>
276
<h4><a name="control1"></a>Conditional structure</h4>
277
<pre class="screen">IF condition
285
<p>pgScript commands are optional. <strong class="command">BEGIN</strong> and <strong class="command">END</strong> keywords are optional if there is only one pgScript command.</p>
286
<h4><a name="control2"></a>Loop structure</h4>
287
<pre class="screen">WHILE condition
291
<p>pgScript commands are optional. <strong class="command">BEGIN</strong> and <strong class="command">END</strong> keywords are optional if there is only one pgScript command.</p>
292
<p><strong class="command">BREAK</strong> ends the enclosing <strong class="command">WHILE</strong> loop, while <strong class="command">CONTINUE</strong> causes
293
the next iteration of the loop to execute. <strong class="command">RETURN</strong> behaves like <strong class="command">BREAK</strong>.</p>
294
<pre class="screen">WHILE condition1
301
<h4><a name="control3"></a>Conditions</h4>
302
<p>Conditions are in fact results of operations. For example the
303
string comparison <code>'ab' = 'ac'</code> will result in a number which
304
is <code>false</code> (the equality is not true).</p>
305
<pre class="screen">IF 'ab' ~= 'AB' -- Case-insensitive comparison which result in 1 (true) which is true
312
-- This does <strong>not</strong> happen
321
-- Infinite loop: use BREAK for exiting
323
<p>It is possible to the result of a SQL SELECT query directly as a
324
condition. The query needs to be surrounded by parenthesis:</p>
325
<pre class="screen">IF (SELECT 1 FROM table)
327
-- This means that table exists otherwise the condition would be false
330
<!-- ---------------------------------------------------------------------- -->
332
<h3><a name="functions"></a>Additional functions and procedures</h3>
333
<h4><a name="function1"></a>Procedures</h4>
334
<p>Procedures do not return a result. They must be used alone on a
335
line and cannot be assigned to a variable.</p>
337
<p>Prints an expression on the screen:</p>
338
<pre class="screen">PRINT 'The value of @A is' + CAST (@A AS STRING);</pre>
340
<p>Throws an exception if the expression evaluated is false:</p>
341
<pre class="screen">ASSERT 5 > 3 AND 'a' = 'a';</pre>
343
<p>Removes the specified line of a record:</p>
344
<pre class="screen">RMLINE(@R[1]); -- Removes @R second line</pre>
345
<h4><a name="function2"></a>Functions</h4>
346
<p>Functions do return a result. Their return value can be assigned
347
to a variable, like the <code>CAST</code> operation.</p>
349
<p>Removes extra spaces surrounding a string:</p>
350
<pre class="screen">SET @A = TRIM(' a '); -- @A = 'a'</pre>
352
<p>Gives the number of lines in a record:</p>
353
<pre class="screen">IF LINES(@R) > 0
358
<p>Gives the number of columns in a record:</p>
359
<pre class="screen">IF COLUMNS(@R) > 0
364
<!-- ---------------------------------------------------------------------- -->
366
<h3><a name="generators"></a>Random data generators</h3>
367
<h4><a name="generator1"></a>Overview of the generators</h4>
368
<p>One can assign a variable (<strong class="command">SET</strong>) with a random
369
data generators. This means each time the variable will be used it will
370
have a different value.</p>
371
<p>However the variable is still used as usual:</p>
372
<pre class="screen">SET @G = STRING(10, 20, 2);
373
SET @A = @G; -- @A will hold a random string
374
SET @B = @G; -- @B will hold another random string
375
PRINT @G, -- This will print another third random string</pre>
376
<h4><a name="generator2"></a>Sequence and seeding</h4>
377
<p>Common parameters for data generators are <span class="emphasis"><em>sequence</em></span> and <span class="emphasis"><em>seed</em></span>.</p>
378
<p><span class="emphasis"><em>sequence</em></span> means that a sequence of values is
379
generated in a random order, in other words each value appears only once
380
before the sequence starts again: this is useful for columns with a <code>UNIQUE</code> constraint. For example, this generator:</p>
381
<pre class="screen">SET @G = INTEGER(10, 15, 1); -- 1 means generate a sequence</pre>
382
<p>It can generate such values: <code class="computeroutput">14 12 10 13 11 15 14
383
12 10 13 11</code>... Where each number appears once before
384
the sequence starts repeating.</p>
385
<p><span class="emphasis"><em>sequence</em></span> parameter must be an integer: if it
386
is 0 then no sequence is generated (default) and if something other than
387
0 then generate a sequence.</p>
388
<p><span class="emphasis"><em>seed</em></span> is an integer value for initializing a
389
generator: two generators with the same parameters and the same seed
390
will generate <strong>exactly</strong> the same
392
<p><span class="emphasis"><em>seed</em></span> must be an integer: it is used directly
393
to initialize the random data generator.</p>
394
<h4><a name="generator3"></a>Data generators</h4>
395
<p>Optional parameters are put into brackets.</p>
396
<pre class="screen">Generator
397
: INTEGER ( min, max, [sequence], [seed] );
398
| REAL ( min, max, precision, [sequence], [seed] );
399
| DATE ( min, max, [sequence], [seed] );
400
| TIME ( min, max, [sequence], [seed] );
401
| DATETIME ( min, max, [sequence], [seed] );
402
| STRING ( min, max, [nb], [seed] );
403
| REGEX ( regex, [seed] );
404
| FILE ( path, [sequence], [seed], [encoding] );
405
| REFERENCE ( table, column, [sequence], [seed] );</pre>
406
<h5>Integer numbers</h5>
407
<pre class="screen">INTEGER ( min, max, [sequence], [seed] );
408
INTEGER ( -10, 10, 1, 123456 );</pre>
409
<p><code>min</code> is an integer, <code>max</code> is an integer, <code>sequence</code> is an integer and <code>seed</code> is an
411
<h5>Real numbers</h5>
412
<pre class="screen">REAL ( min, max, precision, [sequence], [seed] );
413
REAL ( 1.5, 1.8, 2, 1 );</pre>
414
<p><code>min</code> is a number, <code>max</code> is a number, <code>precision</code> is an integer that indicates the number of
415
decimals (should be less than 30), <code>sequence</code> is an integer
416
and <code>seed</code> is an integer.</p>
418
<pre class="screen">DATE ( min, max, [sequence], [seed] );
419
DATE ( '2008-05-01', '2008-05-05', 0 );</pre>
420
<p><code>min</code> is a string representing a date, <code>max</code> is a string representing a date, <code>sequence</code> is an integer and <code>seed</code> is an
423
<pre class="screen">TIME ( min, max, [sequence], [seed] );
424
TIME ( '00:30:00', '00:30:15', 0 );</pre>
425
<p><code>min</code> is a string representing a time, <code>max</code> is a string representing a time, <code>sequence</code> is an integer and <code>seed</code> is an
427
<h5>Timestamps (date/times)</h5>
428
<pre class="screen">DATETIME ( min, max, [sequence], [seed] );
429
DATETIME ( '2008-05-01 14:00:00', '2008-05-05 15:00:00', 1 );</pre>
430
<p><code>min</code> is a string representing a timestamp, <code>max</code> is a string representing a timestamp, <code>sequence</code> is an integer and <code>seed</code> is an
433
<pre class="screen">STRING ( min, max, [nb], [seed] );
434
STRING ( 10, 20, 5 );</pre>
435
<p><code>min</code> is an integer representing the minimum length
436
of a word, <code>max</code> is an integer representing the maximum
437
length of a word, <code>nb</code> is an integer representing the
438
number of words (default: <code>1</code>) and <code>seed</code> is an
440
<p>In the above example we generate 5 words (separated with a
441
space) whose size is between 10 and 20 characters.</p>
442
<h5>Strings from regular expressions</h5>
443
<pre class="screen">REGEX ( regex, [seed] );
444
REGEX ( '[a-z]{1,3}@[0-9]{3}' );</pre>
445
<p><code>regex</code> is a string representing a simplified regular
446
expressions and <code>seed</code> is an integer.</p>
447
<p>Simplified regular expressions are composed of:</p>
450
<p>Sets of possible characters like <code>[a-z_.]</code> for
451
characters between <code>a</code> and <code>z</code> + <code>_</code> and <code>.</code></p>
454
<p>Single characters</p>
457
<p>It is possible to specify the minimum and maximum
458
length of the preceding set or single character:</p>
461
<p><code>{min, max}</code> like <code>{1,3}</code> which stands
462
for length between <code>1</code> and <code>3</code></p>
465
<p><code>{min}</code> like <code>{3}</code> which stands for
466
length of <code>3</code></p>
469
<p>Default (when nothing is specified) is length of <code>1</code></p>
472
<p>Note: be careful with spaces because <code>'a {3}'</code> means
473
one <code>a</code> followed by three spaces because the <code>3</code> is about the last character or set of characters which is a space in this example.</p>
474
<p>If you need to use <code>[</code> <code>]</code> <code>\</code> <code>{</code> or <code>}</code>, they must be escaped because they are
475
special characters. Remember to use <strong>double
476
backslash</strong>: <code>'\\[{3}'</code> for three <code>[</code>.</p>
477
<h5>Strings from dictionary files</h5>
478
<pre class="screen">FILE ( path, [sequence], [seed], [encoding] );
479
FILE ( 'file.txt', 0, 54321, 'utf-8' );</pre>
480
<p><code>path</code> is a string representing the path to a text
481
file, <code>sequence</code> is an integer, <code>seed</code> is an
482
integer and <code>encoding</code> is a string representing the file
483
character set (default is system encoding).</p>
484
<p>This generates a random integer between 1 and the number of
485
lines in the file and then returns that line. If the file does not
486
exist then an exception is thrown.</p>
487
<p><code>encoding</code> supports the most known encoding like
488
utf-8, utf-16le, utf-16be, iso-8859-1, ...</p>
489
<h5>Reference to another field</h5>
490
<pre class="screen">REFERENCE ( table, column, [sequence], [seed] );
491
REFERENCE ( 'tab', 'col', 1 );</pre>
492
<p><code>table</code> is a string representing a table, <code>column</code> is a string representing a column of the table, <code>sequence</code> is an integer and <code>seed</code> is an
494
<p>This is useful for generating data to put into
495
foreign-key-constrained columns.</p>
added
removed
docs/en_US/pgscript.html
