UDXs to extend the NZPLSQL language

Users create stored procedures by writing applications that use the NZPLSQL language. NZPLSQL is an interpreted language that is based on Postgres PL/pgSQL language and which is designed for the host environment.

You can extend the NZPLSQL language with the C++ UDF functionality of the user-defined functions feature. These UDFs must be invoked by using SQL that is structured in such a way as to ensure that the UDFs always run on the Netezza Performance Server host inside Postgres. Since the UDFs are restricted to the host, they can take advantage of the full range of LIBC functions, features, and other standard libraries which are not present on the Nucleus-based SPUs. These UDFs must also be registered to run in unfenced mode.

C runtime library functions describes the recommended set of LIBC supported libraries for UDFs that can run on the SPUs and on the host. This is a subset of the full LIBC library set.

To ensure that your language-extension UDFs run only on the Netezza Performance Server host inside Postgres, you use the getCurrentLocus() function. This function detects the locus or place of execution of the UDF. Design your UDFs to throw an exception if the return value is anything other than UDX_LOCUS_POSTGRES. (The other possible values are UDX_LOCUS_DBOS or UDX_LOCUS_SPU.)

As an example, the following UDF C++ file named dir.cpp defines three functions that are called OpenDir, ReadDir and CloseDir (based on the LIBC functions), which can be used to open a local directory on the host, read its contents, and close the connection to the directory. These functions are defined so that they can be called from NZPLSQL, and must run in unfenced mode to do these actions. The dir.cpp file follows:

#include <udxinc.h>
#include <dirent.h>
#include <sys/types.h>
#include <string.h>
#include <errno.h>

using namespace nz::udx;

class OpenDir : public Udf
{
public:

    Udf * instantiate();
    ReturnValue evaluate() {
        if (getCurrentLocus() != UDX_LOCUS_POSTGRES)
            throwUdxException("opendir only supported in frontend");

#ifndef FOR_SPU
    if (isArgNull(0))
        NZ_UDX_RETURN_NULL();
    StringArg *arg = stringArg(0);
    char path[2048];
    memcpy(path, arg->data, arg->length);
    path[arg->length] = 0;

    DIR *dir = opendir(path);
    if (dir == NULL) {
        char format[2500];
        sprintf(format, "Can't open dir %s: %s", path, 
strerror(errno));
        throwUdxException(format);
    }
    NZ_UDX_RETURN_INT32((int32)dir);
#endif
    }       
};

Udf* OpenDir::instantiate()
{
    return new OpenDir;
}

class ReadDir : public Udf
{
public:

    Udf * instantiate();
    ReturnValue evaluate() {
        if (getCurrentLocus() != UDX_LOCUS_POSTGRES)
            throwUdxException("readdir only supported in frontend");
#ifndef FOR_SPU
        if (isArgNull(0))
            NZ_UDX_RETURN_NULL();
        int32 arg = int32Arg(0);
        DIR *dir = (DIR*)arg;
        struct dirent *dp;

        dp = readdir(dir);
        if (dp == NULL)
            NZ_UDX_RETURN_NULL();

        StringReturn* info = stringReturnInfo();
        memcpy(info->data, dp->d_name, strlen(dp->d_name));
        info->size = strlen(dp->d_name);
        NZ_UDX_RETURN_STRING(info);
#endif
    }
};

Udf* ReadDir::instantiate()
{
    return new ReadDir;
}

class CloseDir : public Udf
{
public:

    Udf * instantiate();

    ReturnValue evaluate() {
        if (getCurrentLocus() != UDX_LOCUS_POSTGRES)
            throwUdxException("closedir only supported in frontend");
#ifndef FOR_SPU
        if (isArgNull(0))
            NZ_UDX_RETURN_NULL();
        int32 arg = int32Arg(0);
        DIR *dir = (DIR*)arg;
        closedir(dir);        
        NZ_UDX_RETURN_BOOL(true);
#endif
    }     
};

Udf* CloseDir::instantiate()
{
    return new CloseDir;
}

The sample dir.cpp file stores several pointers into an int32 field. If the Netezza Performance Server operating system changes to a 64-bit version in the future, these pointers would have to switch to use int64 instead.

You can compile and register the three UDFs in the dir.cpp file by using the following three commands or by using CREATE AND REPLACE FUNCTION commands:

nzudxcompile dir.cpp --sig "opendir(varchar(any))" --return int4 
--class OpenDir --unfenced
nzudxcompile dir.cpp --sig "readdir(int4)" --return "varchar(512)" 
--class ReadDir --unfenced 
nzudxcompile dir.cpp --sig "closedir(int4)" --return "bool" 
--class CloseDir --unfenced

Then, create a stored procedure similar to the following procedure:

DEV.SCHEMA(MYUSER)=> CREATE OR REPLACE PROCEDURE sp_listdirs01() RETURNS BOOL 
LANGUAGE NZPLSQL AS 
BEGIN_PROC
  DECLARE
    dirp int4;
    nm varchar(512);
    cl bool;
    dir varchar(1024);
    num int4; 
    r record;
  BEGIN
        select count(*) INTO num from _t_object where upper(objname) = 
'SORTER' and objclass = 4905 and objdb = current_db;
            IF num = 1 THEN
                DROP TABLE SORTER;
            END IF;
        CREATE TABLE SORTER (grp int4, name varchar(2000));
        dir := '/tmp/udx_known';           
        dirp := opendir(dir);
        LOOP
            nm = readdir(dirp);
            exit when nm is null;
            EXECUTE IMMEDIATE 'INSERT INTO SORTER VALUES (1, ' || 
quote_literal(nm) || ')';
        END LOOP;
        FOR r in SELECT name from sorter order by name LOOP
        RAISE NOTICE 'got %/%',  dir, r.name;
        END LOOP;
        cl = closedir(dirp);
        DROP TABLE SORTER;
        RETURN cl;
      EXCEPTION WHEN OTHERS THEN
        IF dirp is not NULL THEN
        cl = closedir(dirp);
        RETURN cl;
        END IF;
  END;
END_PROC;

The sample procedure calls the new UDFs opendir(), readdir(), and closedir() to operate on a directory named /tmp/udx_known. As an example, if udx_known contains the dir.cpp program and the object files from nzudxcompile, a sample sp_listdirs01() call returns the following information:

DEV.SCHEMA(MYUSER)=> CALL sp_listdirs01(); 
call sp_listdirs01();
NOTICE:  got /tmp/udx_known/.
NOTICE:  got /tmp/udx_known/..
NOTICE:  got /tmp/udx_known/dir.cpp
NOTICE:  got /tmp/udx_known/dir.o_diab_ppc
NOTICE:  got /tmp/udx_known/dir.o_ppc
NOTICE:  got /tmp/udx_known/dir.o_x86
 SP_LISTDIRS01
---------------
 t
(1 row)

If you attempt to run any of the UDFs OpenDir, ReadDir, or CloseDir on the SPUs or in DBOS, the Netezza Performance Server system reports an error similar to the following message:

DEV.SCHEMA(MYUSER)=> SELECT readdir(grp) FROM customers; 
ERROR:  readdir only supported in frontend