Using untrusted Tcl (pltclu) is one of the oldest ways to do things outside the database. pltclu is executed using a normal Tcl interpreter and is pretty much free to do anything you'd like. Tcl has a lot of commands available to interact with the operating system and the environment. We will now take a look at a few simple examples.

The first example, reads the contents of the file and returns them as text, as shown in the following code:

CREATE OR REPLACE FUNCTION read_file(text) RETURNS text
AS $$
  set fptr [open $1]
  set file_data [read $fptr]
  close $fptr #close the file as it is already read
  return $file_data
$$ LANGUAGE pltclu;

The function is quite simple; it opens a file provided as a parameter, reads all its contents at once, and returns the text.

Let's run the preceding function:

postgres=# select read_file('/usr/local/pgsql/data/postmaster.pid'),
       read_file       
-----------------------
 61588                +
 /usr/local/pgsql/data+
 1401041744           +
 5432                 +
 /tmp                 +
 localhost            +
   5432001    196608  +
 
(1 row)

Here is another function that does a directory listing, similar to the plperlu example in Chapter 11, PL/Perl – Perl Procedural Language. Since PL/Tcl does not support the returning of the SETOF text, we will simply return the complete directory listing as one string. As you can see in the following code, this can be done with a single line in Tcl:

CREATE OR REPLACE FUNCTION list_directory(text) RETURNS text
AS $$
  set dirList [glob -nocomplain -directory $1 *.*]
  return $dirList
$$ LANGUAGE pltclu;
testdb=# SELECT list_directory('/tmp'),
               list_directory                
---------------------------------------------
 /tmp/lu84iont.tmp /tmp/unity_support_test.0
(1 row)

We read the contents of the file using Tcl's glob function and just returned the contents as a string.

Let's take a look at one last PL/Tclu function that will export the contents to a table as a .csv file:

CREATE OR REPLACE FUNCTION dump_table(text, text) RETURNS int
AS $$
  set filename $1
    set fileId [open $filename "w"]
  spi_exec -array emp "SELECT * FROM $2" {
    set row [format "%d,%.2f,%.2f,%.2f" $emp(empid) $emp(sales_amnt) $emp(comm_perc) $emp(comm_amnt)]
      puts $fileId $row
    }
    close $fileId
    return 0;
 $$ LANGUAGE pltclu STRICT;

Again, this function is quite simple and uses the concepts we have discussed before. It iterates over the table provided as the second parameter to this function and stores the data in a file named, as per the first parameter of this function. The file is written line by line as comma-separated values by iterating over the table data using spi_exec.

Let's run this function on the emp_sales table that we created earlier in this chapter:

postgres=# select dump_table('emp.txt','emp_sales'),
 dump_table 
------------
          0
(1 row)

This seems to work. We can verify this by running the read_file function we wrote earlier:

postgres=# select read_file('emp.txt'),
        read_file        
-------------------------
 1,32000.00,5.00,1600.00+
 2,5231.23,3.00,156.94  +
 3,64890.00,7.50,4866.75+
 
(1 row)

It seems that the function works, and we have a CSV dump of the table in no time.