|
|
|
|
Variables can be defined using the .set and .setex commands. Variables are then automatically looked for when executing SQL statements for which a variable is required; they are not typed and are converted to the correct type when needed. Note that variables are shared by all the opened connections.
When setting values, the textual representation must respect the following format:
for booleans: "true" or "false" (case insensitive)
for numerical types: the dot as a fraction separator
for dates, time and timestamp: the ISO 8601 format (dates as "YYYY-MM-DD", time as "HH:MM:SS")
Use the .set <variablename> <variable value> command to define a variable, and the .set command to list all defined variables. The following example illustrates variables usage:
SalesTest> select * from customers where id = ##theid::int;
ERROR: No internal parameter named 'theid' required by query
SalesTest> .set theid 3
SalesTest> select * from customers where id = ##theid::int;
id | name | default_served_by | country | city
---+------------+-------------------+---------+-----
3 | Lew Bonito | 1 | FR | TLS
(1 row)
SalesTest> .set theid 5
SalesTest> select * from customers where id = ##theid::int;
id | name | default_served_by | country | city
---+------+-------------------+---------+-----
(0 rows)
SalesTest> .set
List of defined parameters
Name | Value
------+------
theid | 5
(1 row)
SalesTest>
The .setex command also sets an internal parameter, it has two usages:
The .set <variablename> <filename> usage loads the contents of the named filename into the named variable (usually to be used as a BLOB)
The .set <variablename> <table> <column> <row condition> usage creates a named variable which contents is the value of the <table> table and <column> column for the row identified by <row condition>. Note that this command will fail if the <row condition> condition does not return exactly one value.
The following example defined a "bl10" variable containing the value of the "blob" column in the "blobs" table for the "id=10" condition:
.setex bl10 blobs blob "id=10"
The .export command exports to a file the contents of an internal variable or the the contents of a table's value:
The .export <variablename> <filename> usage exports the contents of the named variable into the named filename
The .set <table> <column> <row condition> <filename> usage exports the value of the <table> table and <column> column for the row identified by <row condition>. Note that this command will fail if the <row condition> condition does not return exactly one value.