<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<link rel="STYLESHEET" type="text/css" href="pgadmin3.css">
<title>pgScript Scripting Language Reference</title>
</head>
<body>
<h3>pgScript Scripting Language Reference</h3>
<p><b>Table of Contents</b></p>
<ul>
<li><a href="#overview">Overview</a></li>
<li><a href="#examples">Examples</a></li>
<ul>
<li><a href="#example1">Batch table creations</a></li>
<li><a href="#example2">Insert random data</a></li>
<li><a href="#example3">Batch table deletions</a></li>
<li><a href="#example4">Print information on screen</a></li>
</ul>
<li><a href="#commands">SQL Commands</a></li>
<li><a href="#variables">Variables</a></li>
<ul>
<li><a href="#variable1">Simple variables</a></li>
<li><a href="#variable2">Records</a></li>
<li><a href="#variable3">Cast</a></li>
<li><a href="#variable4">Operations</a></li>
</ul>
<li><a href="#control">Control-of-flow structures</a></li>
<ul>
<li><a href="#control1">Conditional structure</a></li>
<li><a href="#control2">Loop structure</a></li>
<li><a href="#control3">Conditions</a></li>
</ul>
<li><a href="#functions">Additional functions and procedures</a></li>
<ul>
<li><a href="#function1">Procedures</a></li>
<li><a href="#function2">Functions</a></li>
</ul>
<li><a href="#generators">Random data generators</a></li>
<ul>
<li><a href="#generator1">Overview of the generators</a></li>
<li><a href="#generator2">Sequence and seeding</a></li>
<li><a href="#generator3">Data generators</a></li>
</ul>
</ul>
<!-- ---------------------------------------------------------------------- -->
<h3><a name="overview"></a>Overview</h3>
<p>pgScript is composed of pgScript commands:</p>
<pre class="screen">pgScript command
: Regular PostgreSQL SQL Command (SELECT INSERT CREATE ...)
| Variable declaration or assignment (DECLARE SET)
| Control-of-flow structure (IF WHILE)
| Procedure (ASSERT PRINT LOG RMLINE)</pre>
<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
semi-column <code>;</code>. Identifiers are case-sensitive.</p>
<!-- ---------------------------------------------------------------------- -->
<h3><a name="examples"></a>Examples</h3>
<h4><a name="example1"></a>Batch table creations</h4>
<pre class="screen">DECLARE @I, @T; -- Variable names begin with a @
SET @I = 0; -- @I is an integer
WHILE @I < 20
BEGIN
SET @T = 'table' + CAST (@I AS STRING); -- Casts @I
CREATE TABLE @T (id integer primary key, data text);
SET @I = @I + 1;
END</pre>
<h4><a name="example2"></a>Insert random data</h4>
<pre class="screen">DECLARE @I, @J, @T, @G;
SET @I = 0;
SET @G1 = INTEGER(10, 29, 1); /* Random integer generator
Unique numbers between 10 and 29 */
SET @G2 = STRING(10, 20, 3); /* Random string generator
3 words between 10 and 20 characters */
WHILE @I < 20
BEGIN
SET @T = 'table' + CAST (@I AS STRING);
SET @J = 0;
WHILE @J < 20
BEGIN
INSERT INTO @T VALUES (@G1, '@G2');
SET @J = @J + 1;
END
SET @I = @I + 1;
END</pre>
<h4><a name="example3"></a>Batch table deletions</h4>
<pre class="screen">DECLARE @I, @T; -- Declaring is optional
SET @I = 0;
WHILE 1 -- Always true
BEGIN
IF @I >= 20
BREAK; -- Exit the loop if @I > 20
SET @T = 'table' + CAST (@I AS STRING);
DROP TABLE @T;
SET @I = @I + 1;
END</pre>
<h4><a name="example4"></a>Print information on screen</h4>
<pre class="screen">SET @PROGR@M#TITLE = 'pgScript';
PRINT '';
PRINT @PROGR@M#TITLE + ' features:';
PRINT '';
PRINT ' * Regular PostgreSQL commands';
PRINT ' * Control-of-flow language';
PRINT ' * Local variables';
PRINT ' * Random data generators';</pre>
<!-- ---------------------------------------------------------------------- -->
<h3><a name="commands"></a>SQL Commands</h3>
<p>You can run ANY PostgreSQL query from a pgScript EXCEPT those
ones:</p>
<pre class="screen">BEGIN;
END;</pre>
<p>This is because <strong class="command">BEGIN</strong> and <strong class="command">END</strong> are used for delimiting blocks. Instead
use:</p>
<pre class="screen">BEGIN TRANSACTION;
END TRANSACTION;</pre>
<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>
<!-- ---------------------------------------------------------------------- -->
<h3><a name="variables"></a>Variables</h3>
<p>There are two main types of variables : simple variables and records
(result sets composed of lines and columns).</p>
<p>Variable names begin with a <code>@</code> and can be composed of
letters, digits, <code>_</code>, <code>#</code>, <code>@</code>.</p>
<p>Variable type is guessed automatically according to the kind of
value it contains. This can be one of: number (real or integer), string,
record.</p>
<h4><a name="variable1"></a>Simple variables</h4>
<h5>Simple variable declaration</h5>
<p>Declaring simple variable is optional.</p>
<pre class="screen">DECLARE @A, @B;
DECLARE @VAR1;</pre>
<h5>Simple variable affectation</h5>
<p>This is done with the <strong class="command">SET</strong> command. The
variable type depends on the value assigned to this variable.</p>
<pre class="screen">SET @A = 1000, @B = 2000; -- @A and @B are <strong>integer numbers</strong>
SET @C = 10e1, @D = 1.5; -- @C and @D are <strong>real numbers</strong>
SET @E = 'ab', @F = 'a''b'; -- @E and @F are <strong>strings</strong>
SET @G = "ab", @H = "a\"b"; -- @G and @H are <strong>strings</strong></pre>
<p>An uninitialized variable defaults to an empty string. It is
possible to override variables as many times as wanted.</p>
<pre class="screen">PRINT @A; -- Prints an empty string
SET @A = 1000; -- @A is initialized an integer
PRINT @A; -- Prints 1000
SET @A = 'ab'; -- @A becomes a string
PRINT @A; -- Prints ab</pre>
<h5>Data generators</h5>
<p>Data generators allows users to generate random values. There
are various types of generators, each one producing different type
of data. A variable initialized with a data generator behaves like a
regular simple variable except that it has a different value each
time it is used.</p>
<pre class="screen">SET @A = INTEGER(100, 200);
PRINT @A; -- Prints an integer between 100 and 200
PRINT @A; -- Prints another integer between 100 and 200</pre>
<p>A variable can contain a generator but its type is one of:
number (real or integer), string. For a list of available generators
and their associated type, see <a href="#generators">Random data generators</a>.</p>
<h4><a name="variable2"></a>Records</h4>
<h5>Record declaration</h5>
<p>Declaring a record is <strong>required</strong>.
A name for each column must be specified even if they will not be used
anymore afterwards.</p>
<pre class="screen">DECLARE @R1 { @A, @B }, @R2 { @A, @C }; -- Two records with two columns
DECLARE @R3 { @A, @B, @C, @D }; -- One record with four columns</pre>
<p>The number of lines is dynamic: see the next section.</p>
<h5>Record affectation</h5>
<p>To access a specific location in a record, one must use the line
number (starts at 0) and can use either the column name (between
quotes) or the column number (starts at 0). This specific location
behaves like a simple variable. Note that a record cannot contain a
record.</p>
<pre class="screen">SET @R1[0]['@A'] = 1; -- First line & first column
SET @R1[0][0] = 1; -- Same location
SET @R1[4]['@B'] = 1; -- Fifth line & second column
SET @R1[0][1] = 1; -- Same location</pre>
<p>In the above example, three empty lines are automatically
inserted between the first and the fifth. Using an invalid column
number or name results in an exception.</p>
<p>Specific location can be used as right values as well. A
specific line can also be used as right value.</p>
<pre class="screen">SET @R1[0][0] = @R3[0][1], @A = @R2[0][0]; -- Behaves like simple variables
SET @A = @R1[1]; -- @A becomes a record which is the first line of @R1</pre>
<p>Remember that <code>SET @R1[0][0] = @R2</code> is impossible
because a record cannot contain a record.</p>
<p>It is possible to assign a record to a variable, in this case
the variable does not need to be declared:</p>
<pre class="screen">SET @A = @R3; -- @A becomes a record because it is assigned a record</pre>
<h5>SQL queries</h5>
<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
it is something else then it returns a one-line record
(<code>true</code>) if this is a success otherwise a zero-line record
(<code>false</code>).</p>
<pre class="screen">SET @A = SELECT * FROM table; -- @A is a record with the results of the query
SET @B = INSERT INTO table ...; -- @B is a one-line record if the query succeeds</pre>
<h5>Record functions</h5>
<p>See <a href="#function2">Functions</a>.</p>
<h4><a name="variable3"></a>Cast</h4>
<p>It is possible to convert a variable from one type to another with
the cast function:</p>
<pre class="screen">SET @A = CAST (@B AS STRING);
SET @A = CAST (@B AS REAL);
SET @A = CAST (@B AS INTEGER);
SET @A = CAST (@B AS RECORD);</pre>
<p>When a record is converted to a string, it is converted to its flat
representation. When converted to a number, the record is first converted
to a string and then to a number (see string conversion for more
details).</p>
<p>When a number is converted to a string, it is converted to its
string representation. When converted to a record, it is converted to a
one-line-one-column record whose value is the number.</p>
<p>When a string is converted to a number, if the string represents a
number then this number is returned else an exception is thrown. When
converted to a record, either the program can find a <strong>record pattern</strong> in the string or it converts it to
a one-line-one-column record whose value is the string. A record pattern
is:</p>
<pre class="screen">SET @B = '(1, "abc", "ab\\"")(1, "abc", "ab\\"")'; -- @B is a string
SET @B = CAST (@B AS RECORD); @B becomes a two-line-three-column record</pre>
<p>Remember a string is surrounded by simple quotes. Strings
composing a record must be surrounded by double quotes which are escaped
with <code>\\</code> (we double the slash because it is already a
special character for the enclosing simple quotes).</p>
<h4><a name="variable4"></a>Operations</h4>
<p>Operations can only be performed between operands of the same
type. Cast values in order to conform to this criterion.</p>
<p>Comparisons result in a number which is 0 or 1.</p>
<h5>Strings</h5>
<p>Comparisons: <code>= <> > < <= >= AND
OR</code></p>
<p>Concatenation: <code>+</code></p>
<pre class="screen">SET @B = @A + 'abcdef'; -- @A must be a string and @B will be a string</pre>
<p>Boolean value: non-empty string is <code>true</code>, empty
string is <code>false</code></p>
<p>Inverse boolean value: <code>NOT</code></p>
<p>Case-insensitive comparison: <code>~=</code></p>
<h5>Numbers</h5>
<p>Comparisons: <code>= <> > < <= >= AND
OR</code></p>
<p>Arithmetic: <code>+ - * / %</code></p>
<pre class="screen">SET @A = CAST ('10' AS INTEGER) + 5; -- '10' string is converted to a number</pre>
<p>Boolean value: 0 is <code>false</code>, anything else is <code>true</code></p>
<p>Inverse boolean value: <code>NOT</code> (note that <code>NOT NOT
10 = 1</code>)</p>
<p>An arithmetic operation involving at least one real number gives
a real number as a result:</p>
<pre class="screen">SET @A = 10 / 4.; -- 4. is a real so real division: @A = 2.5
SET @A = 10 / 4; -- 4 is an integer so integer division: @A = 2</pre>
<h5>Records</h5>
<p>Comparisons: <code>= <> > < <= >= AND
OR</code></p>
<p>Boolean value: zero-line record is <code>false</code>, anything
else is <code>true</code></p>
<p>Inverse boolean value: <code>NOT</code></p>
<p>Comparisons for records are about inclusion and exclusion. Order
of lines does not matter. <code><=</code> means that each row in
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>
<p>Comparisons are performed on strings: even if a record contains
numbers like <code>10</code> and <code>1e1</code> we will have <code>'10' <> '1e1'</code>.</p>
<!-- ---------------------------------------------------------------------- -->
<h3><a name="control"></a>Control-of-flow structures</h3>
<h4><a name="control1"></a>Conditional structure</h4>
<pre class="screen">IF condition
BEGIN
pgScript commands
END
ELSE
BEGIN
pgScript commands
END</pre>
<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>
<h4><a name="control2"></a>Loop structure</h4>
<pre class="screen">WHILE condition
BEGIN
pgScript commands
END</pre>
<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>
<p><strong class="command">BREAK</strong> ends the enclosing <strong class="command">WHILE</strong> loop, while <strong class="command">CONTINUE</strong> causes
the next iteration of the loop to execute. <strong class="command">RETURN</strong> behaves like <strong class="command">BREAK</strong>.</p>
<pre class="screen">WHILE condition1
BEGIN
IF condition2
BEGIN
BREAK;
END
END</pre>
<h4><a name="control3"></a>Conditions</h4>
<p>Conditions are in fact results of operations. For example the
string comparison <code>'ab' = 'ac'</code> will result in a number which
is <code>false</code> (the equality is not true).</p>
<pre class="screen">IF 'ab' ~= 'AB' -- Case-insensitive comparison which result in 1 (true) which is true
BEGIN
-- This happens
END
IF 0 -- false
BEGIN
-- This does <strong>not</strong> happen
END
ELSE
BEGIN
-- This happens
END
WHILE 1
BEGIN
-- Infinite loop: use BREAK for exiting
END</pre>
<p>It is possible to the result of a SQL SELECT query directly as a
condition. The query needs to be surrounded by parenthesis:</p>
<pre class="screen">IF (SELECT 1 FROM table)
BEGIN
-- This means that table exists otherwise the condition would be false
END</pre>
<!-- ---------------------------------------------------------------------- -->
<h3><a name="functions"></a>Additional functions and procedures</h3>
<h4><a name="function1"></a>Procedures</h4>
<p>Procedures do not return a result. They must be used alone on a
line and cannot be assigned to a variable.</p>
<h5>Print</h5>
<p>Prints an expression on the screen:</p>
<pre class="screen">PRINT 'The value of @A is' + CAST (@A AS STRING);</pre>
<h5>Assert</h5>
<p>Throws an exception if the expression evaluated is false:</p>
<pre class="screen">ASSERT 5 > 3 AND 'a' = 'a';</pre>
<h5>Remove line</h5>
<p>Removes the specified line of a record:</p>
<pre class="screen">RMLINE(@R[1]); -- Removes @R second line</pre>
<h4><a name="function2"></a>Functions</h4>
<p>Functions do return a result. Their return value can be assigned
to a variable, like the <code>CAST</code> operation.</p>
<h5>Trim</h5>
<p>Removes extra spaces surrounding a string:</p>
<pre class="screen">SET @A = TRIM(' a '); -- @A = 'a'</pre>
<h5>Lines</h5>
<p>Gives the number of lines in a record:</p>
<pre class="screen">IF LINES(@R) > 0
BEGIN
-- Process
END</pre>
<h5>Columns</h5>
<p>Gives the number of columns in a record:</p>
<pre class="screen">IF COLUMNS(@R) > 0
BEGIN
-- Process
END</pre>
<!-- ---------------------------------------------------------------------- -->
<h3><a name="generators"></a>Random data generators</h3>
<h4><a name="generator1"></a>Overview of the generators</h4>
<p>One can assign a variable (<strong class="command">SET</strong>) with a random
data generators. This means each time the variable will be used it will
have a different value.</p>
<p>However the variable is still used as usual:</p>
<pre class="screen">SET @G = STRING(10, 20, 2);
SET @A = @G; -- @A will hold a random string
SET @B = @G; -- @B will hold another random string
PRINT @G, -- This will print another third random string</pre>
<h4><a name="generator2"></a>Sequence and seeding</h4>
<p>Common parameters for data generators are <span class="emphasis"><em>sequence</em></span> and <span class="emphasis"><em>seed</em></span>.</p>
<p><span class="emphasis"><em>sequence</em></span> means that a sequence of values is
generated in a random order, in other words each value appears only once
before the sequence starts again: this is useful for columns with a <code>UNIQUE</code> constraint. For example, this generator:</p>
<pre class="screen">SET @G = INTEGER(10, 15, 1); -- 1 means generate a sequence</pre>
<p>It can generate such values: <code class="computeroutput">14 12 10 13 11 15 14
12 10 13 11</code>... Where each number appears once before
the sequence starts repeating.</p>
<p><span class="emphasis"><em>sequence</em></span> parameter must be an integer: if it
is 0 then no sequence is generated (default) and if something other than
0 then generate a sequence.</p>
<p><span class="emphasis"><em>seed</em></span> is an integer value for initializing a
generator: two generators with the same parameters and the same seed
will generate <strong>exactly</strong> the same
values.</p>
<p><span class="emphasis"><em>seed</em></span> must be an integer: it is used directly
to initialize the random data generator.</p>
<h4><a name="generator3"></a>Data generators</h4>
<p>Optional parameters are put into brackets.</p>
<pre class="screen">Generator
: INTEGER ( min, max, [sequence], [seed] );
| REAL ( min, max, precision, [sequence], [seed] );
| DATE ( min, max, [sequence], [seed] );
| TIME ( min, max, [sequence], [seed] );
| DATETIME ( min, max, [sequence], [seed] );
| STRING ( min, max, [nb], [seed] );
| REGEX ( regex, [seed] );
| FILE ( path, [sequence], [seed], [encoding] );
| REFERENCE ( table, column, [sequence], [seed] );</pre>
<h5>Integer numbers</h5>
<pre class="screen">INTEGER ( min, max, [sequence], [seed] );
INTEGER ( -10, 10, 1, 123456 );</pre>
<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
integer.</p>
<h5>Real numbers</h5>
<pre class="screen">REAL ( min, max, precision, [sequence], [seed] );
REAL ( 1.5, 1.8, 2, 1 );</pre>
<p><code>min</code> is a number, <code>max</code> is a number, <code>precision</code> is an integer that indicates the number of
decimals (should be less than 30), <code>sequence</code> is an integer
and <code>seed</code> is an integer.</p>
<h5>Dates</h5>
<pre class="screen">DATE ( min, max, [sequence], [seed] );
DATE ( '2008-05-01', '2008-05-05', 0 );</pre>
<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
integer.</p>
<h5>Times</h5>
<pre class="screen">TIME ( min, max, [sequence], [seed] );
TIME ( '00:30:00', '00:30:15', 0 );</pre>
<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
integer.</p>
<h5>Timestamps (date/times)</h5>
<pre class="screen">DATETIME ( min, max, [sequence], [seed] );
DATETIME ( '2008-05-01 14:00:00', '2008-05-05 15:00:00', 1 );</pre>
<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
integer.</p>
<h5>Strings</h5>
<pre class="screen">STRING ( min, max, [nb], [seed] );
STRING ( 10, 20, 5 );</pre>
<p><code>min</code> is an integer representing the minimum length
of a word, <code>max</code> is an integer representing the maximum
length of a word, <code>nb</code> is an integer representing the
number of words (default: <code>1</code>) and <code>seed</code> is an
integer.</p>
<p>In the above example we generate 5 words (separated with a
space) whose size is between 10 and 20 characters.</p>
<h5>Strings from regular expressions</h5>
<pre class="screen">REGEX ( regex, [seed] );
REGEX ( '[a-z]{1,3}@[0-9]{3}' );</pre>
<p><code>regex</code> is a string representing a simplified regular
expressions and <code>seed</code> is an integer.</p>
<p>Simplified regular expressions are composed of:</p>
<ul type="disc">
<li>
<p>Sets of possible characters like <code>[a-z_.]</code> for
characters between <code>a</code> and <code>z</code> + <code>_</code> and <code>.</code></p>
</li>
<li>
<p>Single characters</p>
</li>
</ul>
<p>It is possible to specify the minimum and maximum
length of the preceding set or single character:</p>
<ul type="disc">
<li>
<p><code>{min, max}</code> like <code>{1,3}</code> which stands
for length between <code>1</code> and <code>3</code></p>
</li>
<li>
<p><code>{min}</code> like <code>{3}</code> which stands for
length of <code>3</code></p>
</li>
<li>
<p>Default (when nothing is specified) is length of <code>1</code></p>
</li>
</ul>
<p>Note: be careful with spaces because <code>'a {3}'</code> means
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>
<p>If you need to use <code>[</code> <code>]</code> <code>\</code> <code>{</code> or <code>}</code>, they must be escaped because they are
special characters. Remember to use <strong>double
backslash</strong>: <code>'\\[{3}'</code> for three <code>[</code>.</p>
<h5>Strings from dictionary files</h5>
<pre class="screen">FILE ( path, [sequence], [seed], [encoding] );
FILE ( 'file.txt', 0, 54321, 'utf-8' );</pre>
<p><code>path</code> is a string representing the path to a text
file, <code>sequence</code> is an integer, <code>seed</code> is an
integer and <code>encoding</code> is a string representing the file
character set (default is system encoding).</p>
<p>This generates a random integer between 1 and the number of
lines in the file and then returns that line. If the file does not
exist then an exception is thrown.</p>
<p><code>encoding</code> supports the most known encoding like
utf-8, utf-16le, utf-16be, iso-8859-1, ...</p>
<h5>Reference to another field</h5>
<pre class="screen">REFERENCE ( table, column, [sequence], [seed] );
REFERENCE ( 'tab', 'col', 1 );</pre>
<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
integer.</p>
<p>This is useful for generating data to put into
foreign-key-constrained columns.</p>
</body>
</html>
distrib
>
Fedora
>
13
>
i386
>
media
>
updates
>
by-pkgid
>
cb664fc35171072d04824accda2566aa
>
files
>
264
