compatibility.sql_in File Reference

Compatibility SQL functions. More...

Go to the source code of this file.

Functions
void	insert_into (varchar inTableName, varchar inSQL)
	Mimick INSERT INTO. Create new rows in a table. More...
void	create_table_as (varchar inTableName, varchar inSQL, varchar inDistributed="RANDOMLY")
	Mimick CREATE TABLE AS. Create a table and fill it with data computed by a SELECT command. More...
void	create_temporary_table_as (varchar inTableName, varchar inSQL, varchar inDistributed="RANDOMLY")
	Mimick CREATE TEMPORARY TABLE AS. Create a temporary table and fill it with data computed by a SELECT command. More...

Detailed Description

See Also

For a brief overview of compatibility functions, see the module description Compatibility.

Definition in file compatibility.sql_in.

Function Documentation

void create_table_as	(	varchar	inTableName,
		varchar	inSQL,
		varchar	inDistributed = "RANDOMLY"
	)

Parameters

inTableName	The name (optionally schema-qualified) of the table to be created.
inSQL	A SELECT command.
inDistributed	The Greenplum Database distribution policy for the table. This can be either RANDOMLY, which is the default, or BY (column, [...]). This parameter is ignored on PostgreSQL.

Usage:

Use in the same way as the CREATE TABLE AS statement:

SELECT create_table_as('table_name', $$
    query
$$, 'BY (column, [...]) | RANDOMLY');

Examples:

SELECT create_table_as('public.test', $$
    SELECT * FROM generate_series(1,10) AS id
$$, 'BY (id)');


SELECT create_table_as('logregr_result', $$
    SELECT * FROM logregr('data', 'y', 'x')
$$, 'RANDOMLY');

Note

This function is a workaround. For compliance with the SQL standard and for optimal performance, please use the normal CREATE TABLE AS statement whenever possible. Known caveats of this workaround:

• For queries returning a large number of rows, this function will be significantly slower than the CREATE TABLE AS statement.

Definition at line 225 of file compatibility.sql_in.

void create_temporary_table_as	(	varchar	inTableName,
		varchar	inSQL,
		varchar	inDistributed = "RANDOMLY"
	)

See Also

create_table_as()

Definition at line 258 of file compatibility.sql_in.

void insert_into	(	varchar	inTableName,
		varchar	inSQL
	)

Parameters

inTableName	The name (optionally schema-qualified) of an existing table.
inSQL	A SELECT command.

Usage:

Use in the same way as the INSERT INTO statement:

SELECT insert_into('table_name', $$
    query
$$);

Examples:

SELECT insert_into('public.test', $$
    SELECT * FROM generate_series(1,10) AS id
$$);


SELECT insert_into('logregr_results', $$
    SELECT * FROM logregr('data', 'y', 'x')
$$);

Note

This function is a workaround. For compliance with the SQL standard and for optimal performance, please use the normal INSERT INTO statement whenever possible. Known caveats of this workaround:

• For queries returning a large number of rows, this function will be significantly slower than the INSERT INTO statement.

Definition at line 110 of file compatibility.sql_in.