utilities.sql_in

Go to the documentation of this file.

/* ----------------------------------------------------------------------- *//**
 *
 * @file utilities.sql_in
 *
 * @brief SQL functions for carrying out routine tasks
 *
 * @sa For a brief overview of utility functions, see the
 *     module description \ref grp_utilities.
 *
 *//* ----------------------------------------------------------------------- */

m4_include(`SQLCommon.m4') --'
m4_changequote(<!,!>)

/**
@addtogroup grp_utilities

@about

The utilty module provides functions for routine tasks that need no further
explanation.

@sa File utilities.sql_in documenting the SQL functions.
*/

/**
 * @brief Return MADlib build information.
 *
 * @returns Summary of MADlib build information, consisting of MADlib version,
 *     git revision, cmake configuration time, build type, build system,
 *     C compiler, and C++ compiler
 */
CREATE FUNCTION MADLIB_SCHEMA.version()
RETURNS TEXT
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT (
        'MADlib version: __MADLIB_VERSION__, '
        'git revision: __MADLIB_GIT_REVISION__, '
        'cmake configuration time: __MADLIB_BUILD_TIME__, '
        'build type: __MADLIB_BUILD_TYPE__, '
        'build system: __MADLIB_BUILD_SYSTEM__, '
        'C compiler: __MADLIB_C_COMPILER__, '
        'C++ compiler: __MADLIB_CXX_COMPILER__')::TEXT
$$;


/**
 * @brief Raise an exception if the given condition is not satisfied.
 */
CREATE FUNCTION MADLIB_SCHEMA.assert(condition BOOLEAN, msg VARCHAR)
RETURNS VOID
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
    IF NOT condition THEN
        RAISE EXCEPTION 'Failed assertion: %', msg;
    END IF;
END
$$;

------------------------------------------------------------------------

/**
 * @brief Compute the relative error of an approximate value
 */
CREATE FUNCTION MADLIB_SCHEMA.relative_error(
    approx DOUBLE PRECISION,
    value DOUBLE PRECISION
) RETURNS DOUBLE PRECISION
LANGUAGE sql
AS $$
    SELECT abs(($1 - $2)/$2)
$$;

------------------------------------------------------------------------

/**
 * @brief Compute the relative error (w.r.t. the 2-norm) of an apprixmate vector
 */
CREATE FUNCTION MADLIB_SCHEMA.relative_error(
    approx DOUBLE PRECISION[],
    value DOUBLE PRECISION[]
) RETURNS DOUBLE PRECISION
LANGUAGE sql
AS $$
    SELECT MADLIB_SCHEMA.dist_norm2($1, $2) / MADLIB_SCHEMA.norm2($2)
$$;

------------------------------------------------------------------------

/**
 * @brief Check if a SQL statement raises an error
 *
 * @param sql The SQL statement
 * @returns \c TRUE if an exception is raised while executing \c sql, \c FALSE
 *     otherwise.
 */
CREATE FUNCTION MADLIB_SCHEMA.check_if_raises_error(
    sql TEXT
) RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
    EXECUTE sql;
    RETURN FALSE;
EXCEPTION
    WHEN OTHERS THEN
        RETURN TRUE;
END;
$$;

------------------------------------------------------------------------

/**
 * @brief Check if a floating-point number is NaN (not a number)
 *
 * This function exists for portability. Some DBMSs like PostgreSQL treat
 * floating-point numbers as fully ordered -- contrary to IEEE 754. (See, e.g.,
 * the <a href=
 * "http://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-FLOAT"
 * >PostgreSQL documentation</a>. For portability, MADlib code should not make
 * use of such "features" directly, but only use isnan() instead.
 *
 * @param number
 * @returns \c TRUE if \c number is \c NaN, \c FALSE otherwise
 */
CREATE FUNCTION MADLIB_SCHEMA.isnan(
    number DOUBLE PRECISION
) RETURNS BOOLEAN
LANGUAGE sql
AS $$
    SELECT $1 = 'NaN'::DOUBLE PRECISION;
$$;

------------------------------------------------------------------------

/**
 * @brief Create the temporary schema if it does not exist yet
 */
CREATE FUNCTION MADLIB_SCHEMA.create_schema_pg_temp()
RETURNS VOID
LANGUAGE plpgsql
VOLATILE
AS $$
BEGIN
    -- pg_my_temp_schema() is a built-in function
    IF pg_my_temp_schema() = 0 THEN
        -- The pg_temp schema does not exist, yet. Creating a temporary table
        -- will create it. Note: There is *no* race condition here, because
        -- every session has its own temp schema.
        EXECUTE 'CREATE TEMPORARY TABLE _madlib_temp_table AS SELECT 1;
            DROP TABLE pg_temp._madlib_temp_table CASCADE;';
    END IF;
END;
$$;

------------------------------------------------------------------------

/**
 * @brief Create volatile noop function
 *
 * The only use of this function is as an optimization fence when used in the
 * SELECT list of a query. See, e.g.,
 * http://archives.postgresql.org/pgsql-sql/2012-07/msg00030.php
 */
CREATE FUNCTION MADLIB_SCHEMA.noop()
RETURNS VOID
VOLATILE
LANGUAGE c
AS 'MODULE_PATHNAME';

------------------------------------------------------------------------

/*
 * Create type bytea8 with 8-byte alignment.
 */
CREATE TYPE MADLIB_SCHEMA.bytea8;

CREATE FUNCTION MADLIB_SCHEMA.bytea8in(cstring)
RETURNS MADLIB_SCHEMA.bytea8 AS 'byteain'
LANGUAGE internal IMMUTABLE STRICT;

CREATE FUNCTION MADLIB_SCHEMA.bytea8out(MADLIB_SCHEMA.bytea8)
RETURNS cstring AS 'byteaout'
LANGUAGE internal IMMUTABLE STRICT;

CREATE FUNCTION MADLIB_SCHEMA.bytea8recv(internal)
RETURNS MADLIB_SCHEMA.bytea8 AS 'bytearecv'
LANGUAGE internal IMMUTABLE STRICT;

CREATE FUNCTION MADLIB_SCHEMA.bytea8send(MADLIB_SCHEMA.bytea8)
RETURNS bytea AS 'byteasend'
LANGUAGE internal IMMUTABLE STRICT;

CREATE TYPE MADLIB_SCHEMA.bytea8(
    INPUT = MADLIB_SCHEMA.bytea8in,
    OUTPUT = MADLIB_SCHEMA.bytea8out,
    RECEIVE = MADLIB_SCHEMA.bytea8recv,
    SEND = MADLIB_SCHEMA.bytea8send,
    ALIGNMENT = double
);

------------------------------------------------------------------------
/**
 * @brief Generate random remporary names for temp table and other names
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.__unique_string ()
RETURNS VARCHAR AS $$
PythonFunction(utilities, utilities, __unique_string)
$$ LANGUAGE plpythonu;

------------------------------------------------------------------------
/**
 * @brief Cast boolean into text
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.bool_to_text (BOOLEAN)
RETURNS TEXT
STRICT
LANGUAGE SQL AS '
    SELECT CASE
        WHEN $1 THEN ''t''
        ELSE ''f''
    END;
';

m4_ifdef(`__GREENPLUM__', `
CREATE CAST (BOOLEAN AS TEXT)
     WITH FUNCTION MADLIB_SCHEMA.bool_to_text(BOOLEAN)
     AS ASSIGNMENT;
')


------------------------------------------------------------------------

/*
 * An array_agg() function is defined in module array_ops (to compatibility with
 * GP 4.0.
 */

m4_changequote(<!`!>,<!'!>)