Use PL/Tcl

Eugenia Kuzina

Contents

Overview
PL/Tcl functions and arguments
Global data
Database access from PL/Tcl
- spi_exec
- spi_prepare
spi_execp

Overview

PL/Tcl is a loadable procedural language for the ADPG/PostgreSQL database system that allows you to use the Tcl language to write functions and procedures.

PL/Tcl offers most of the capabilities that the C language provides with few limitations and allows you to use the powerful string processing libraries that are available for Tcl.

One useful restriction is that the code runs in the security context of the Tcl interpreter. In addition to the limited set of secure Tcl commands, only a few commands are available to access the database using SPI and to issue messages via elog. PL/Tcl does not provide the ability to interact with the internal mechanism of the database server or access the OS with the rights of the PostgreSQL server process, which is possible in C functions. So, unprivileged database users can be trusted to use this language — it does not give them unlimited authority.

Another major limitation is that Tcl functions cannot be used to create input/output functions for new data types.

Sometimes it is necessary to write a Tcl function that goes beyond safe Tcl. For example, you may need a function that sends emails. To handle these cases, there is a variation of PL/Tcl called PL/TclU (untrusted Tcl). This is exactly the same language except that a full Tcl interpreter is used. PL/TclU must be installed as an untrusted procedural language so that only database superusers can create functions in it. The author of a PL/TclU function must ensure that the function cannot be used to do anything unexpected, since it can perform any operation that a user with database administrator rights can do.

To install PL/Tcl and/or PL/TclU in a particular database, use the CREATE EXTENSION command:

CREATE EXTENSION pltcl;

CREATE EXTENSION pltclu;

TIP

If the pltcl or pltclu extension is created in the template1 database used as the default template, all subsequently created databases will have this extension installed.

PL/Tcl functions and arguments

To create a function in the PL/Tcl language, use the standard CREATE FUNCTION command:

CREATE FUNCTION <funcname> (<argument-types>) RETURNS <return-type> AS $$
# PL/Tcl function body
$$ LANGUAGE pltcl [STRICT];

The body of the function is a part of a Tcl script. When the function is called, the argument values are passed to the Tcl script as variables named 1 … n. The result is returned from the Tcl code with the return statement.

The PL/TclU function has the same syntax, except that the language must be specified as pltclu.

For example, the following function returns the greater of two integer values:

CREATE FUNCTION calculate_max(integer, integer) RETURNS integer AS $$
    if {$1 > $2} {return $1}
    return $2
$$ LANGUAGE pltcl STRICT;

With the STRICT clause, if a null value is passed, the function is not called but returns a null result.

If STRICT is not specified and an argument value is null, the corresponding $n variable will be set to an empty string.

Use the argisnull function to determine whether a particular argument is null. To return a null value from a PL/Tcl function, execute return_null. This can be done whether the function contains STRICT or not.

It is possible to rewrite the calculate_max function with argisnull and return_null to return the non-null argument if one of the arguments is null:

CREATE FUNCTION calculate_max(integer, integer) RETURNS integer AS $$
    if {[argisnull 1]} {
        if {[argisnull 2]} { return_null }
        return $2
    }
    if {[argisnull 2]} { return $1 }
    if {$1 > $2} {return $1}
    return $2
$$ LANGUAGE pltcl;

Composite-type arguments are passed to the function as Tcl arrays. The element names of the array are the attribute names of the composite type. If an attribute in a row has the null value, it does not appear in the array. The following example uses an array:

CREATE TABLE book (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(50) NOT NULL,
    author_id INT NOT NULL,
    in_stock INTEGER DEFAULT 0);

INSERT INTO book(title, author_id, in_stock) VALUES
    ('Mrs. Dalloway',1,25),
    ('To the Lighthouse',1,13),
    ('To Kill a Mockingbird',2,0),
    ('The Great Gatsby',3,3),
    ('The Lord of the Rings',4,45);

CREATE FUNCTION is_in_stock(book) RETURNS boolean AS $$
    if {$1(in_stock)> 0} {
        return "t"
    }
    return "f"
$$ LANGUAGE pltcl;

SELECT book_id, title, is_in_stock(b.*) FROM book b;

Result:

 book_id |         title         | is_in_stock
---------+-----------------------+-------------
       1 | Mrs. Dalloway         | t
       2 | To the Lighthouse     | t
       3 | To Kill a Mockingbird | f
       4 | The Great Gatsby      | t
       5 | The Lord of the Rings | t

PL/Tcl functions can also return composite-type results. To do this, the Tcl code must return a list of column name/value pairs matching the expected result type. Any column names omitted from the list return null values, and an error is raised if there are unexpected column names. Example:

CREATE FUNCTION sum_prod(x int, y int, out sum int, out prod int) AS $$
    return [list sum [expr {$1 + $2}] prod [expr {$1 * $2}]]
$$ LANGUAGE pltcl;

SELECT sum_prod(2,6);

Result:

 sum_prod
----------
 (8,12)

You can return output arguments of procedures in the same way, for example:

CREATE PROCEDURE calculate_cube(INOUT x integer, INOUT y integer) AS $$
     return [list x [expr {$1 * $1 * $1}] y [expr {$2 * $2 * $2} ]]
$$ LANGUAGE pltcl;

CALL calculate_cube(2, 15);

Result:

 x |  y
---+------
 8 | 3375

You can use the array get Tcl command to create the result list from an array representation:

CREATE FUNCTION arrival(book, amount int) RETURNS book AS $$
    set 1(in_stock) [expr {$1(in_stock) + $2}]
    return [array get 1]
$$ LANGUAGE pltcl;

SELECT arrival(book::book, 6) FROM book;

Result:

             arrival
----------------------------------
 (1,"Mrs. Dalloway",1,31)
 (2,"To the Lighthouse",1,19)
 (3,"To Kill a Mockingbird",2,6)
 (4,"The Great Gatsby",3,9)
 (5,"The Lord of the Rings",4,51)

PL/Tcl functions can return sets of results. To do this, the Tcl code should call return_next once per row to return an appropriate value for a scalar type, or a list of column name/value pairs for a composite type. The following function returns a set of a scalar type:

CREATE FUNCTION incr_sequence(start_seq int, end_seq int) RETURNS SETOF int AS $$
    for {set i $1} {$i <= $2} {incr i} {
    return_next $i
}
$$ LANGUAGE pltcl;

SELECT incr_sequence(8, 14);

Result:

 incr_sequence
---------------
             8
             9
            10
            11
            12
            13
            14

The example below returns a set of a composite type:

CREATE FUNCTION cube_sequence(start_seq int, end_seq int) RETURNS TABLE ( x int, x_cube int) AS $$
for {set i $1} {$i <= $2} {incr i} {
return_next [list x $i x_cube [expr {$i * $i * $i}]]
}
$$ LANGUAGE pltcl;

SELECT cube_sequence(8, 14);

Result:

 cube_sequence
---------------
 (8,512)
 (9,729)
 (10,1000)
 (11,1331)
 (12,1728)
 (13,2197)
 (14,2744)

NOTE

The argument values passed to the PL/Tcl function are the input arguments converted to text. The return and return_next commands accept any string that matches the format of the declared function result type or the specified column in the result of a composite type.

Global data

Sometimes it is necessary to have some global data that is stored between two function calls or shared between different functions. This is easy to do in PL/Tcl, but there are some limitations.

For security reasons, PL/Tcl runs functions called by a specific SQL role in a separate Tcl interpreter for that role. This prevents one user from accidentally or maliciously interfering with the behavior of another user’s PL/Tcl functions. Each such interpreter has its own values for any Tcl global variables. So, two PL/Tcl functions use the same global variables only if they are executed by the same SQL role. In an application that runs code under multiple SQL roles in the same session (using SECURITY DEFINER or SET ROLE, etc.), it may be necessary to perform additional steps to ensure that PL/Tcl functions can share data. To do this, ensure that the functions that should communicate are owned by the same user and set the SECURITY DEFINER parameter for them. See CREATE FUNCTION.

All PL/TclU functions used in a session are executed in the same Tcl interpreter, which is different from the interpreters used for PL/Tcl functions. In this way, global data is automatically distributed among the PL/TclU functions. This is not considered a security risk because all PL/TclU functions are performed at the same trust level — the database superuser level.

To protect PL/Tcl functions from unintentionally affecting each other, each function is provided with a global array variable using the upvar command. The global name of this variable is the internal name of the function, and the local name is GD. It is recommended to use GD for persistent private function data. Use regular Tcl global variables only for values that will be shared among multiple functions. Note that GD arrays are only global within a particular interpreter, so they do not bypass the security restrictions mentioned above.

You can find an example of using GD in the description of the spi_execp function.

Database access from PL/Tcl

Below are the commands used for accessing a database from the body of a PL/Tcl function. In this section, according to the Tcl convention, question marks (?) replace the brackets ([]) typically used to indicate optional elements in a syntax description.

spi_exec

The spi_exec command executes an SQL command given as a string. An error in the command causes an error to be raised. The return value of spi_exec is the number of rows processed (selected, inserted, updated, or deleted) by the command, or zero if the command is a utility statement. Additionally, if the command is a SELECT statement, the values of the selected columns are placed in Tcl variables, as described below.

spi_exec ?-count <n>? ?-array <name>? <command> ?<loop-body>?

Where:

command — SQL command.
count <n> — an optional parameter indicating that execution of spi_exec should stop immediately after n rows are retrieved, as if the query included a LIMIT clause. If n is zero, the query is executed until completion, the same as if -count is not specified.
array <name> — an optional parameter, used if the SQL command is a SELECT statement. When specified, the column values are stored in a named associative array, and the column names are used as array indexes. The current row number in the result (starting from zero) is stored in an array element named tupno, unless that name is used as a column name. If array is not specified, the values of the result columns are placed in Tcl variables named after the column names.
loop-body — an optional parameter that specifies a Tcl script fragment that is executed once for each row of the query result. If the command is a SELECT statement and no loop-body is specified, only the first row of results is stored in Tcl variables or array, the remaining rows are ignored. Storing does not occur if the query returns no rows. If the SQL command is not SELECT, loop-body is ignored.

The following function sets the value of the count_books variable to the number of rows in the books table:

CREATE OR REPLACE FUNCTION book_count() RETURNS int AS $$

    spi_exec "SELECT count(*) AS count_books FROM book"

return $count_books

$$ LANGUAGE pltcl;

SELECT book_count();

Result:

 book_count
------------
          5

The function below returns a string containing books of a specific author. The column values of the current row are stored in the res array before each iteration:

CREATE OR REPLACE FUNCTION books_by_author() RETURNS text AS $$
    set book_result " Virginia Woolf: "
    set counter 1
    spi_exec -array res "SELECT * FROM book WHERE author_id=1" {
        if {$counter > 1} { append book_result ", " }
        append book_result $res(title)
        incr counter
        }

    return $book_result
$$ LANGUAGE pltcl;

SELECT books_by_author();

Result:

                  books_by_author
---------------------------------------------------
  Virginia Woolf: Mrs. Dalloway, To the Lighthouse

spi_prepare

The spi_prepare function prepares and saves a query plan for later execution. The saved plan is available during the current session.

spi_prepare <query> <typelist>

Where:

query — SQL query;
typelist — list of query parameter types.

The query string processed by spi_prepare supports parameters. You can use numbers $1 … $n to refer to parameters in the query string. The parameter types must be given as a Tcl list. For example, [ list int4 int4 ]. Add an empty list as typelist if no parameters are used.

The spi_prepare function returns a query ID to be used in subsequent calls of the spi_execp function described below.

spi_execp

The spi_execp function executes a query previously prepared with spi_prepare.

spi_execp ?-count <n>? ?-array <name>? ?-nulls <string>? <queryid> ?<value-list>? ?<loop-body>?

Where:

queryid — ID returned by spi_prepare.
count <n> — an optional parameter indicating that execution of spi_execp should stop immediately after n rows are retrieved. If n is zero, the query is executed until completion, the same as if -count is not specified.
array <name> — an optional parameter, used if the SQL command is a SELECT statement. When specified, the column values are stored in a named associative array, and the column names are used as array indexes. The current row number in the result (starting from zero) is stored in an array element named tupno, unless that name is used as a column name. If array is not specified, the values of the result columns are placed in Tcl variables named after the column names.
nulls <string> — an optional parameter indicating which parameters have null values. string is a sequence of characters that includes spaces and n characters for parameters that have null values. The string must have the same length as the value-list. If nulls is not specified, all the parameter values are assumed to be non-null.
value-list — a Tcl list of actual values for query parameters. value-list must be the same length as the parameter type list previously given to spi_prepare. Omit value-list if the query has no parameters.
loop-body — an optional parameter that specifies a Tcl script fragment that is executed once for each row of the query result. If the command is a SELECT statement and no loop-body is specified, only the first row of results is stored in Tcl variables or array, the remaining rows are ignored. Storing does not occur if the query returns no rows. If the SQL command is not SELECT, loop-body is ignored.

Except for the way in which the query and its parameters are specified, spi_execp works like spi_exec.

The following function calculates the number of rows where the aid column value is greater than or equal to the first parameter of the rows_count function but less than or equal to the second parameter. The example operates with the pgbench_accounts table created using pgbench:

CREATE FUNCTION rows_count(integer, integer) RETURNS integer AS $$
    if {![ info exists GD(plan) ]} {
    # prepare a plan on the first call
        set GD(plan) [ spi_prepare "SELECT count(*) AS row_number FROM pgbench_accounts WHERE aid >= \$1 AND aid <= \$2" [ list int4 int4 ] ]
    }
    spi_execp -count 1 $GD(plan) [ list $1 $2 ]
    return $row_number

$$ LANGUAGE pltcl;

SELECT rows_count(28,3678);

Result:

 rows_count
------------
       3651

Note that backslashes inside the query string given to spi_prepare ensure that the $n markers will be passed as is and are not replaced by the Tcl variable substitution.

quote

The quote function doubles all occurrences of a single quote and backslash characters in the given string. This can be used to protect strings that will be inserted into SQL commands passed to spi_exec or spi_prepare.

quote <string>

Example:

"SELECT '[ quote $val ]' AS ret"

As a result, for example, SELECT 'It doesn’t true' AS ret will be transformed into SELECT 'It doesn''t true' AS ret.

subtransaction

The subtransaction function executes the given command within an SQL subtransaction.

subtransaction <command>

Where command is a Tcl script.

If the script returns an error, the entire subtransaction is rolled back before returning the error out to the surrounding code.

Example:

CREATE TABLE accounts(
  acc_number integer PRIMARY KEY,
  balance numeric
);

CREATE TABLE transfer_logs(
  log_id SERIAL PRIMARY KEY,
  result text
);

INSERT INTO accounts VALUES
    (1234,1000.00),
    (4321,2000.00);

CREATE FUNCTION account_transfers() RETURNS void AS $$
    if [catch {
        spi_exec "UPDATE accounts SET balance = balance - 1000 WHERE acc_number = '1234'"
        spi_exec "UPDATE accounts SET balance = balance + 1000 WHERE acc_number = '4321'"
    } errormsg] {
        set result [format "The transfer was canceled: %s" $errormsg]
    } else {
        set result "The transfer is successful"
}
    spi_exec "INSERT INTO transfer_logs (result) VALUES ('[quote $result]')"
$$ LANGUAGE pltcl;

SELECT account_transfers();
SELECT * FROM accounts;

Result:

 acc_number | balance
------------+---------
       1234 |    0.00
       4321 | 3000.00

For more information, see Explicit Subtransactions in PL/Tcl.

elog

The elog function emits a log or error message.

elog <level> <msg>

Where:

level — message priority level. Possible levels are DEBUG, LOG, INFO, NOTICE, WARNING, ERROR, and FATAL.
msg — message text.

Example:

elog DEBUG "The database contains table $table_name"

The ERROR level raises an error condition. If this is not trapped by the Tcl code, the error propagates out to the calling query, causing the current transaction or subtransaction to be aborted. This is the same as the Tcl error command. FATAL aborts the transaction and causes the current session to shut down. There is probably no good reason to use this error level in PL/Tcl functions, but it is provided for completeness. The other levels only generate messages of different priority levels.

Whether messages of a particular priority are reported to the client, written to the server log, or both is controlled by the log_min_messages and client_min_messages configuration parameters. You can specify these settings on the Primary configuration tab of the ADPG service. Expand the ADPG configurations node, open the postgresql.conf field, find and change the log_min_messages parameter. To set client_min_messages, add this parameter to the postgresql.conf field (see Configuration parameters). For more information on error handling, refer to Error handling in PL/Tcl.

Found a mistake? Seleсt text and press Ctrl+Enter to report it