utilities.sql_in File Reference

SQL functions for carrying out routine tasks. More...

Functions
void	cleanup_madlib_temp_tables (text target_schema)
	Drop all tables matching pattern 'madlib_temp' in a given schema. More...
text	cleanup_madlib_temp_tables_script (text target_schema)
void	cleanup_madlib_temp_views (text target_schema)
text	version ()
	Return MADlib build information. More...
void	assert (boolean condition, varchar msg)
	Raise an exception if the given condition is not satisfied. More...
float8	relative_error (float8 approx, float8 value)
	Compute the relative error of an approximate value. More...
float8	relative_error (float8[] approx, float8[] value)
	Compute the relative error (w.r.t. the 2-norm) of an apprixmate vector. More...
boolean	check_if_raises_error (text sql)
	Check if a SQL statement raises an error. More...
boolean	check_if_col_exists (text source_table, text column_name)
	Check if a column exists in a table. More...
boolean	isnan (float8 number)
	Check if a floating-point number is NaN (not a number) More...
void	create_schema_pg_temp ()
	Create the temporary schema if it does not exist yet. More...
void	noop ()
	Create volatile noop function. More...
bytea8	bytea8in (cstring)
cstring	bytea8out (bytea8)
bytea8	bytea8recv (internal)
bytea	bytea8send (bytea8)
varchar	__internal_get_col_names_except_dep_variable (varchar source_table, varchar dependent_varname)
	Get all column names except dependent variable. More...
varchar	__unique_string ()
	Generate random remporary names for temp table and other names. More...
varchar []	_string_to_array (varchar s)
	Takes a string of comma separated values and puts it into an array. More...
text	bool_to_text (boolean)
	Cast boolean into text. More...
text	__to_char (anyelement val)
	Cast any value to text. More...
set< _grp_state_type >	_gen_state (text[] grp_key, integer[] iteration, float8[] state)
float8	_final_mode (float8[])
aggregate float8	mode (float8)
void	dropcols (varchar source_table, varchar out_table, varchar cols_to_drop)
	Creates a new table with a subset of columns dropped from another source table. More...
boolean	is_pg_major_version_less_than (integer compare_version)
integer	trap_error (text stmt)
boolean	test_error_msg (text stmt, text msg)
boolean	test_input_table (text stmt)
boolean	test_output_table (text stmt)
boolean	is_ver_gp_640_700_or_pg_11 ()
void	assert_guc_value (text guc_name, text expected_guc_value)
boolean	is_table_unlogged (text tbl_name)

Detailed Description

See also

For a brief overview of utility functions, see the module description Database Functions.

Function Documentation

__internal_get_col_names_except_dep_variable()

varchar __internal_get_col_names_except_dep_variable	(	varchar	source_table,
		varchar	dependent_varname
	)

__to_char()

text __to_char

(

anyelement

val

)

Parameters

val	A value with any specific type.

Returns

The text format string for the value.

Note

Greenplum doesn't support bit/boolean to text casting.

__unique_string()

varchar __unique_string

(

)

_final_mode()

float8 _final_mode

(

float8

[]

)

_gen_state()

set<_grp_state_type> _gen_state	(	text []	grp_key,
		integer []	iteration,
		float8 []	state
	)

_string_to_array()

varchar [] _string_to_array

(

varchar

s

)

assert()

void assert	(	boolean	condition,
		varchar	msg
	)

assert_guc_value()

void assert_guc_value	(	text	guc_name,
		text	expected_guc_value
	)

bool_to_text()

text bool_to_text

(

boolean

)

bytea8in()

bytea8 bytea8in

(

cstring

)

bytea8out()

cstring bytea8out

(

bytea8

)

bytea8recv()

bytea8 bytea8recv

(

internal

)

bytea8send()

bytea bytea8send

(

bytea8

)

check_if_col_exists()

boolean check_if_col_exists	(	text	source_table,
		text	column_name
	)

Parameters

source_table	Source table
column_name	Column name in the table

Returns

TRUE if it exists and FALSE if not

check_if_raises_error()

boolean check_if_raises_error

(

text

sql

)

Parameters

sql	The SQL statement

Returns

TRUE if an exception is raised while executing sql, FALSE otherwise.

cleanup_madlib_temp_tables()

void cleanup_madlib_temp_tables

(

text

target_schema

)

Parameters

target_schema

TEXT. The schema that takes affect.

cleanup_madlib_temp_tables_script()

text cleanup_madlib_temp_tables_script

(

text

target_schema

)

cleanup_madlib_temp_views()

void cleanup_madlib_temp_views

(

text

target_schema

)

create_schema_pg_temp()

void create_schema_pg_temp

(

)

dropcols()

void dropcols	(	varchar	source_table,
		varchar	out_table,
		varchar	cols_to_drop
	)

Parameters

source_table	Name of the table containing the source data.
out_table	Name of the generated table containing the output. If a table with the same name already exists, an error will be returned.
cols_to_drop	Comma-separated string of column names from the source table to drop. An error is returned if the output table does not contain any columns or if a requested column is not present in the source table.

is_pg_major_version_less_than()

boolean is_pg_major_version_less_than

(

integer

compare_version

)

is_table_unlogged()

boolean is_table_unlogged

(

text

tbl_name

)

is_ver_gp_640_700_or_pg_11()

boolean is_ver_gp_640_700_or_pg_11

(

)

isnan()

boolean isnan

(

float8

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 PostgreSQL documentation. For portability, MADlib code should not make use of such "features" directly, but only use isnan() instead.

Parameters

number

Returns

TRUE if number is NaN, FALSE otherwise

mode()

aggregate float8 mode

(

float8

)

noop()

void noop

(

)

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

relative_error() [1/2]

float8 relative_error	(	float8	approx,
		float8	value
	)

relative_error() [2/2]

float8 relative_error	(	float8 []	approx,
		float8 []	value
	)

test_error_msg()

boolean test_error_msg	(	text	stmt,
		text	msg
	)

test_input_table()

boolean test_input_table

(

text

stmt

)

test_output_table()

boolean test_output_table

(

text

stmt

)

trap_error()

integer trap_error

(

text

stmt

)

version()

text version

(

)

Returns

Summary of MADlib build information, consisting of MADlib version, git revision, cmake configuration time, build type, build system, C compiler, and C++ compiler