compatibility.sql_in

Go to the documentation of this file.

/* ----------------------------------------------------------------------- *//**
 *
 * @file compatibility.sql_in
 *
 * @brief Compatibility SQL functions
 *
 * @sa For a brief overview of compatibility functions, see the
 *     module description \ref grp_compatibility.
 *
 *//* ----------------------------------------------------------------------- */

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


/**
@addtogroup grp_compatibility

@about

The compatibility module replicates standard SQL functionality that cannot be
used on all platforms supported by MADlib -- be it due to incomplete
implementation of the SQL standard or bugs.

This module contains workarounds for the following issues:

- <tt>CREATE TABLE <em>table_name</em> AS <em>query</em></tt> statements where
  <em>query</em> contains certain MADlib functions fails with the error
  “function cannot execute on segment because it issues a non-SELECT statement”
  (Greenplum versions before version 4.2, and in rare special cases also later
  versions).
  The workaround is:
  <pre>SELECT \ref create_table_as('<em>table_name</em>', $$
    <em>query</em>
$$, 'BY (<em>column</em>, [...]) | RANDOMLY');</pre>
- <tt>INSERT INTO <em>table_name</em> <em>query</em></tt> where <em>query</em>
  contains certain MADlib functions fails with the error “function cannot
  execute on segment because it issues a non-SELECT statement” (Greenplum
  versions before version 4.2, and in rare special cases also later versions).
  The workaround is:
  <pre>SELECT \ref insert_into('<em>table_name</em>', $$
    <em>query</em>
$$);</pre>

@note
These functions are not needed on PostgreSQL and are usually not needed on
Greenplum 4.2 and later. However, they are always installed for compatibility
with other platforms.
Workarounds should be used only when necessary. For portability and best
performance, standard SQL should be prefered whenever possible.

@literature

[1] Greenplum Admin Guide

@sa File compatibility.sql_in (documenting the SQL functions)

*/

/**
 * @brief Mimick <tt>INSERT INTO</tt>. Create new rows in a table.
 *
 * @param inTableName The name (optionally schema-qualified) of an existing
 *     table.
 * @param inSQL A SELECT command.
 *
 * @usage
 * Use in the same way as the <tt>INSERT INTO</tt> statement:
 * <pre>SELECT insert_into('<em>table_name</em>', $$
 *    <em>query</em>
 *$$);</pre>
 *
 * @examp
 * <pre>SELECT insert_into('public.test', $$
 *    SELECT * FROM generate_series(1,10) AS id
 *$$);\n
 *SELECT insert_into('logregr_results', $$
 *    SELECT * FROM logregr('data', 'y', 'x')
 *$$);</pre>
 *
 * @note This function is a workaround. For compliance with the SQL standard and
 *     for optimal performance, please use the normal <tt>INSERT INTO</tt>
 *     statement whenever possible.
 *     Known caveats of this workaround:
 *     - For queries returning a large number of rows, this function will be
 *       significantly slower than the <tt>INSERT INTO</tt> statement.
 */
CREATE FUNCTION MADLIB_SCHEMA.insert_into(
    "inTableName" VARCHAR,
    "inSQL" VARCHAR)
RETURNS VOID
LANGUAGE plpgsql
VOLATILE
RETURNS NULL ON NULL INPUT
AS $$
DECLARE
    oldClientMinMessages VARCHAR;
BEGIN
    oldClientMinMessages :=
        (SELECT setting FROM pg_settings WHERE name = 'client_min_messages');
    EXECUTE 'SET client_min_messages TO warning';

    PERFORM MADLIB_SCHEMA.create_schema_pg_temp();

    EXECUTE
        'DROP FUNCTION IF EXISTS pg_temp._madlib_temp_function();
        CREATE FUNCTION pg_temp._madlib_temp_function()
            RETURNS VOID
            LANGUAGE plpgsql
            AS $_madlib_temp_function$
            DECLARE
                result ' || "inTableName" || '%ROWTYPE;
            BEGIN
                FOR result IN EXECUTE
                    $_madlib_temp_function_string$
                    ' || "inSQL" || '
                    $_madlib_temp_function_string$
                    LOOP

                    INSERT INTO ' || "inTableName" || ' VALUES (result.*);
                END LOOP;
            END;
            $_madlib_temp_function$';

    -- We call _madlib_temp_function() in a separate
    -- EXECUTE statement because it is not yet visible before
    -- (we would see "ERROR: function _madlib_temp_function() does not exist")
    EXECUTE
        'SELECT pg_temp._madlib_temp_function();
        SET client_min_messages TO ' || oldClientMinMessages || ';';
END;
$$;


CREATE FUNCTION MADLIB_SCHEMA.internal_create_table_as(
    "inTemporary" BOOLEAN,
    "inTableName" VARCHAR,
    "inSQL" VARCHAR,
    "inDistributed" VARCHAR)
RETURNS VOID
LANGUAGE plpgsql
VOLATILE
RETURNS NULL ON NULL INPUT
AS $$
DECLARE
    whatToCreate VARCHAR;
BEGIN
    IF "inTemporary" = TRUE THEN
        whatToCreate := 'TEMPORARY TABLE';
    ELSE
        whatToCreate := 'TABLE';
    END IF;

    -- We separate the following EXECUTE statement because it is prone
    -- to generate an exception -- e.g., if the table already exists
    -- In that case we want to keep the context in the error message short
    EXECUTE
        'CREATE ' || whatToCreate || ' ' || "inTableName" || ' AS
        SELECT * FROM (' || "inSQL" || ') AS _madlib_ignore
        WHERE FALSE
m4_ifdef(<!__GREENPLUM__!>,<!
        DISTRIBUTED ' || "inDistributed";
!>,<!
        ';
!>)

    PERFORM MADLIB_SCHEMA.insert_into("inTableName", "inSQL");
END;
$$;


/**
 * @brief Mimick <tt>CREATE TABLE AS</tt>. Create a table and fill it with data
 *     computed by a \c SELECT command.
 *
 * @param inTableName The name (optionally schema-qualified) of the table to be
 *     created.
 * @param inSQL A SELECT command.
 * @param inDistributed The Greenplum Database distribution policy for the
 *     table. This can be either \c RANDOMLY, which is the default, or
 *     <tt>BY (<em>column</em>, [...])</tt>. This parameter is ignored on
 *     PostgreSQL.
 *
 * @usage
 * Use in the same way as the <tt>CREATE TABLE AS</tt> statement:
 * <pre>SELECT create_table_as('<em>table_name</em>', $$
 *    <em>query</em>
 *$$, 'BY (<em>column</em>, [...]) | RANDOMLY');</pre>
 *
 * @examp
 * <pre>SELECT create_table_as('public.test', $$
 *    SELECT * FROM generate_series(1,10) AS id
 *$$, 'BY (id)');\n
 *SELECT create_table_as('logregr_result', $$
 *    SELECT * FROM logregr('data', 'y', 'x')
 *$$, 'RANDOMLY');</pre>
 *
 * @note This function is a workaround. For compliance with the SQL standard and
 *     for optimal performance, please use the normal <tt>CREATE TABLE AS</tt>
 *     statement whenever possible.
 *     Known caveats of this workaround:
 *     - For queries returning a large number of rows, this function will be
 *       significantly slower than the <tt>CREATE TABLE AS</tt> statement.
 */
CREATE FUNCTION MADLIB_SCHEMA.create_table_as(
    "inTableName" VARCHAR,
    "inSQL" VARCHAR,
    "inDistributed" VARCHAR /*+ = 'RANDOMLY' */)
RETURNS VOID
LANGUAGE sql
VOLATILE
RETURNS NULL ON NULL INPUT
AS $$
    SELECT MADLIB_SCHEMA.internal_create_table_as(FALSE,
        $1, $2, $3);
$$;


CREATE FUNCTION MADLIB_SCHEMA.create_table_as(
    "inTableName" VARCHAR,
    "inSQL" VARCHAR)
RETURNS VOID
LANGUAGE sql
VOLATILE
RETURNS NULL ON NULL INPUT
AS $$
    SELECT MADLIB_SCHEMA.internal_create_table_as(FALSE,
        $1, $2, 'RANDOMLY');
$$;


/**
 * @brief Mimick <tt>CREATE TEMPORARY TABLE AS</tt>. Create a temporary table
 *     and fill it with data computed by a \c SELECT command.
 *
 * @sa create_table_as()
 */
CREATE FUNCTION MADLIB_SCHEMA.create_temporary_table_as(
    "inTableName" VARCHAR,
    "inSQL" VARCHAR,
    "inDistributed" VARCHAR /*+ = 'RANDOMLY' */)
RETURNS VOID
LANGUAGE sql
VOLATILE
RETURNS NULL ON NULL INPUT
AS $$
    SELECT MADLIB_SCHEMA.internal_create_table_as(TRUE,
        $1, $2, $3);
$$;


CREATE FUNCTION MADLIB_SCHEMA.create_temporary_table_as(
    "inTableName" VARCHAR,
    "inSQL" VARCHAR)
RETURNS VOID
LANGUAGE sql
VOLATILE
RETURNS NULL ON NULL INPUT
AS $$
    SELECT MADLIB_SCHEMA.internal_create_table_as(TRUE,
        $1, $2, 'RANDOMLY');
$$;


/*
 * We also provide create_temp_table_as, but we do not advertise it in our
 * documentation.
 */
CREATE FUNCTION MADLIB_SCHEMA.create_temp_table_as(
    "inTableName" VARCHAR,
    "inSQL" VARCHAR,
    "inDistributed" VARCHAR /*+ = 'RANDOMLY' */)
RETURNS VOID
LANGUAGE sql
VOLATILE
RETURNS NULL ON NULL INPUT
AS $$
    SELECT MADLIB_SCHEMA.internal_create_table_as(TRUE,
        $1, $2, $3);
$$;


CREATE FUNCTION MADLIB_SCHEMA.create_temp_table_as(
    "inTableName" VARCHAR,
    "inSQL" VARCHAR)
RETURNS VOID
LANGUAGE sql
VOLATILE
RETURNS NULL ON NULL INPUT
AS $$
    SELECT MADLIB_SCHEMA.internal_create_table_as(TRUE,
        $1, $2, 'RANDOMLY');
$$;

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