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

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

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

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>

Simple variable declaration

Declaring simple variable is optional:

DECLARE @A, @B;
DECLARE @VAR1;

Simple variable affectation

This is done with the SET command. The variable type depends on the value assigned to this variable:

SET @A = 1000, @B = 2000;   -- @A and @B are <strong>integer numbers**
SET @C = 10e1, @D = 1.5;    -- @C and @D are <strong>real numbers**
SET @E = 'ab', @F = 'a''b'; -- @E and @F are <strong>strings**
SET @G = "ab", @H = "a\"b"; -- @G and @H are <strong>strings**

An uninitialized variable defaults to an empty string. It is possible to override variables as many times as wanted:

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

Data generators

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:

SET @A = INTEGER(100, 200);
PRINT @A; -- Prints an integer between 100 and 200
PRINT @A; -- Prints another integer between 100 and 200

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

Record declaration

Declaring a record is required. A name for each column must be specified even if they will not be used anymore afterwards:

DECLARE @R1 { @A, @B }, @R2 { @A, @C }; -- Two records with two  columns
DECLARE @R3 { @A, @B, @C, @D };         -- One record  with four columns

The number of lines is dynamic: see the next section.

Record affectation

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:

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

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.

Specific location can be used as right values as well. A specific line can also be used as right value:

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

Remember that SET @R1[0][0] = @R2 is impossible because a record cannot contain a record.

It is possible to assign a record to a variable, in this case the variable does not need to be declared:

SET @A = @R3; -- @A becomes a record because it is assigned a record

SQL queries

Any SQL query executed returns a record. If the query is a SELECT query then it returns the results of the query. If it is something else then it returns a one-line record (true) if this is a success otherwise a zero-line record (false):

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

Record functions

See function2.

SET @A = CAST (@B AS STRING);
SET @A = CAST (@B AS REAL);
SET @A = CAST (@B AS INTEGER);
SET @A = CAST (@B AS RECORD);

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

Strings

Comparisons: = <> > < <= >= AND OR

Concatenation: +

SET @B = @A + 'abcdef'; -- @A must be a string and @B will be a string

Boolean value: non-empty string is true, empty string is false

Inverse boolean value: NOT

Case-insensitive comparison: ~=

Numbers

Comparisons: = <> > < <= >= AND OR

Arithmetic: + - * / %

SET @A = CAST ('10' AS INTEGER) + 5; -- '10' string is converted to a number

Boolean value: 0 is false, anything else is true

Inverse boolean value: NOT (note that NOT NOT 10 = 1)

An arithmetic operation involving at least one real number gives a real number as a result:

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

Records

Comparisons: = <> > < <= >= AND OR

Boolean value: zero-line record is false, anything else is true

Inverse boolean value: NOT

Comparisons for records are about inclusion and exclusion. Order of lines does not matter. <= means that each row in the left operand has a match in the right operand. >= means the opposite. = means that <= and >= are both true at the same time...

Comparisons are performed on strings: even if a record contains numbers like 10 and 1e1 we will have '10' <> '1e1'.

IF condition
BEGIN
    pgScript commands
END
ELSE
BEGIN
    pgScript commands
END

WHILE condition
BEGIN
    pgScript commands
END

WHILE condition1
BEGIN
    IF condition2
    BEGIN
        BREAK;
    END
END

IF 'ab' ~= 'AB' -- Case-insensitive comparison which result in 1 (true) which is true
BEGIN
    -- This happens
END

IF 0 -- false
BEGIN
    -- This does **not** happen
END
ELSE
BEGIN
    -- This happens
END

WHILE 1
BEGIN
    -- Infinite loop: use BREAK for exiting
END

IF (SELECT 1 FROM table)
BEGIN
    -- This means that table exists otherwise the condition would be false
END

Print

Prints an expression on the screen:

PRINT 'The value of @A is' + CAST (@A AS STRING);

Assert

Throws an exception if the expression evaluated is false:

ASSERT 5 > 3 AND 'a' = 'a';

Remove line

Removes the specified line of a record:

RMLINE(@R[1]); -- Removes @R second line

Trim

Removes extra spaces surrounding a string:

SET @A = TRIM(' a '); -- @A = 'a'</pre>

Lines

Gives the number of lines in a record:

IF LINES(@R) > 0
BEGIN
    -- Process
END

Columns

Gives the number of columns in a record:

IF COLUMNS(@R) > 0
BEGIN
    -- Process
END

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

SET @G = INTEGER(10, 15, 1); -- 1 means generate a sequence

14 12 10 13 11 15 14 12 10 13 11

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] );

Integer numbers

INTEGER ( min, max, [sequence], [seed] );
INTEGER ( -10, 10, 1, 123456 );

min is an integer, max is an integer, sequence is an integer and seed is an integer.

Real numbers

REAL ( min, max, precision, [sequence], [seed] );
REAL ( 1.5, 1.8, 2, 1 );

min is a number, max is a number, precision is an integer that indicates the number of decimals (should be less than 30), sequence is an integer and seed is an integer.

Dates

DATE ( min, max, [sequence], [seed] );
DATE ( '2008-05-01', '2008-05-05', 0 );

min is a string representing a date, max is a string representing a date, sequence is an integer and seed is an integer.

Times

TIME ( min, max, [sequence], [seed] );
TIME ( '00:30:00', '00:30:15', 0 );

min is a string representing a time, max is a string representing a time, sequence is an integer and seed is an integer.

Timestamps (date/times)

DATETIME ( min, max, [sequence], [seed] );
DATETIME ( '2008-05-01 14:00:00', '2008-05-05 15:00:00', 1 );

min is a string representing a timestamp, max is a string representing a timestamp, sequence is an integer and seed is an integer.

Strings

STRING ( min, max, [nb], [seed] );
STRING ( 10, 20, 5 );

min is an integer representing the minimum length of a word, max is an integer representing the maximum length of a word, nb is an integer representing the number of words (default: 1) and seed is an integer.

In the above example we generate 5 words (separated with a space) whose size is between 10 and 20 characters.

Strings from regular expressions

REGEX ( regex, [seed] );
REGEX ( '[a-z]{1,3}@[0-9]{3}' );

regex is a string representing a simplified regular expressions and seed is an integer.

Simplified regular expressions are composed of:

• Sets of possible characters like [a-z_.] for characters between a and z + _ and .
• Single characters

It is possible to specify the minimum and maximum length of the preceding set or single character:

• {min, max} like {1,3} which stands for length between 1 and 3
• {min} like {3} which stands for length of 3
• Default (when nothing is specified) is length of 1

Note: be careful with spaces because 'a {3}' means one a followed by three spaces because the 3 is about the last character or set of characters which is a space in this example.

If you need to use [ ] \ { or }, they must be escaped because they are special characters. Remember to use double backslash: '\\[{3}' for three [.

Strings from dictionary files

FILE ( path, [sequence], [seed], [encoding] );
FILE ( 'file.txt', 0, 54321, 'utf-8' );

path is a string representing the path to a text file, sequence is an integer, seed is an integer and encoding is a string representing the file character set (default is system encoding).

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.

encoding supports the most known encoding like utf-8, utf-16le, utf-16be, iso-8859-1, ...

Reference to another field

REFERENCE ( table, column, [sequence], [seed] );
REFERENCE ( 'tab', 'col', 1 );

table is a string representing a table, column is a string representing a column of the table, sequence is an integer and seed is an integer.

This is useful for generating data to put into foreign-key-constrained columns.