PL/Tcl functions provide you with SPI functions to access the database and run DML/DDL statements.
The functions are the following:
spi_exec
: This executes a SQL statementspi_prepare
: This prepares a SQL statementspi_execp
: This executes a prepared statementThe spi_exec
function has the following syntax:
spi_exec ?-count n? ?-array name? command ?loop-body?
The spi_exec
function runs a SQL statement, and it takes some optional parameters, as follows:
-count
: This parameter allows you to specify the maximum number of rows processed by the command. If you provide the value 3, only 3 rows will be processed. This is similar to specifying FETCH [n]
in a cursor.-array
: If this parameter is specified, the column values are stored into a named associative array and the column names are used as array indexes. If this parameter is not specified, the result values are stored in the Tcl variables of the same name.If there is a loop body specified, then it is treated as a script that is run for each row.
Let's take a look at an example of how to run SQL statements inside a PL/Tcl function. The following example, creates a function that loops over the rows in a table and updates a column in each row:
CREATE TABLE emp_sales(empid int PRIMARY KEY, sales_amnt decimal, comm_perc decimal, comm_amnt decimal); INSERT INTO emp_sales VALUES (1,32000, 5, NULL); INSERT INTO emp_sales VALUES (2,5231.23, 3, NULL); INSERT INTO emp_sales VALUES (3,64890, 7.5, NULL); CREATE OR REPLACE FUNCTION tcl_calc_comm() RETURNS int AS $$ spi_exec -array C "SELECT * FROM emp_sales" { set camnt [ expr ($C(sales_amnt) * $C(comm_perc))/100 ] spi_exec "update emp_sales set comm_amnt = [format "%.2f" $camnt] where empid = $C(empid)" } $$ LANGUAGE pltcl;
The preceding example, does the following:
emp_sales
, which contains the employee ID, how much sales the employee has made, and what is their commission percentage. The last column that represents the total commission to be paid to the employee based on his/her sales and his/her commission percentage is left blank intentionally, and it is filled by our function.spi_exec
command, and the column values are returned in the associative array called C
.comm_amnt
column for each row.You can read more about accessing the database in a PL/Tcl function at http://www.postgresql.org/docs/current/interactive/pltcl-dbaccess.html.
18.116.14.118