<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>