Accessing databases

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 statement
  • spi_prepare: This prepares a SQL statement
  • spi_execp: This executes a prepared statement

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

  • It creates a table 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.
  • It fills the table with some random data.
  • Then, the function body runs a SQL statement using the spi_exec command, and the column values are returned in the associative array called C.
  • Finally, the loop body calculates the commission value and updates the comm_amnt column for each row.

Note

You can read more about accessing the database in a PL/Tcl function at http://www.postgresql.org/docs/current/interactive/pltcl-dbaccess.html.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.116.14.118