array_ops.sql_in

Go to the documentation of this file.

/* ----------------------------------------------------------------------- *//** 
 *
 * @file array_ops.sql_in
 *
 * @brief implementation of array operations in SQL
 * @date   April 2011
 *
 *
 *//* ----------------------------------------------------------------------- */

m4_include(`SQLCommon.m4')

/**
@addtogroup grp_array


@about

This module provide a set of basic array operations implemented in C. It is a support module for several machine learning algorithms that
require fast array operations. 

@implementation

-# At present these functions support several numeric types:
    - SHORTINT
    - INT
    - BIGINT
    - REAL
    - FLOAT
As of now they do not support variable size NUMERIC input. 
-# Also several of them may require NO NULL VALUES, while others omit NULLs and 
return results.

@sa File array_ops.sql_in for list of functions and usage.
*/

/**
 * @brief Adds two arrays. It requires that all the values are NON-NULL. Return type is the same as the input type.
 *
 * @param x Array x
 * @param y Array y
 * @returns Sum of x and y.
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.array_add(x anyarray, y anyarray) RETURNS anyarray 
AS 'MODULE_PATHNAME', 'array_add'
LANGUAGE C IMMUTABLE;

/**
 * @brief Subtracts two arrays. It requires that all the values are NON-NULL. Return type is the same as the input type.
 *
 * @param x Array x
 * @param y Array y
 * @returns x-y.
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.array_sub(x anyarray, y anyarray) RETURNS anyarray 
AS 'MODULE_PATHNAME', 'array_sub'
LANGUAGE C IMMUTABLE;

/**
 * @brief Element-wise product of two arrays. It requires that all the values are NON-NULL. Return type is the same as the input type.
 *
 * @param x Array x
 * @param y Array y
 * @returns Element-wise product of x and y.
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.array_mult(x anyarray, y anyarray) RETURNS anyarray 
AS 'MODULE_PATHNAME', 'array_mult'
LANGUAGE C IMMUTABLE;

/**
 * @brief Element-wise division of two arrays. It requires that all the values are NON-NULL. Return type is the same as the input type.
 *
 * @param x Array x
 * @param y Array y
 * @returns Element-wise division of x and y.
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.array_div(x anyarray, y anyarray) RETURNS anyarray 
AS 'MODULE_PATHNAME', 'array_div'
LANGUAGE C IMMUTABLE;

/**
 * @brief Dot-product of two arrays. It requires that all the values are NON-NULL. Return type is the same as the input type.
 *
 * @param x Array x
 * @param y Array y
 * @returns Dot-product of x and y.
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.array_dot(x anyarray, y anyarray) RETURNS FLOAT8 
AS 'MODULE_PATHNAME', 'array_dot'
LANGUAGE C IMMUTABLE;

/**
 * @brief Checks whether one array contains the other.
 *
 * @param x Array x
 * @param y Array y
 * @returns Returns true if x contains y.
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.array_contains(x anyarray, y anyarray) RETURNS BOOL 
AS 'MODULE_PATHNAME', 'array_contains'
LANGUAGE C IMMUTABLE;

/**
 * @brief This function finds the maximum value in the array. NULLs are ignored. Return type is the same as the input type.
 *
 * @param x Array x
 * @returns Max of x.
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.array_max(x anyarray) RETURNS anyelement 
AS 'MODULE_PATHNAME', 'array_max'
LANGUAGE C IMMUTABLE;

/**
 * @brief This function finds the minimum value in the array. NULLs are ignored. Return type is the same as the input type.
 *
 * @param x Array x
 * @returns Min of x.
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.array_min(x anyarray) RETURNS anyelement 
AS 'MODULE_PATHNAME', 'array_min'
LANGUAGE C IMMUTABLE;

/**
 * @brief This function finds the sum of the values in the array. NULLs are ignored. Return type is the same as the input type.
 *
 * @param x Array x
 * @returns Sum of x.
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.array_sum(x anyarray) RETURNS anyelement 
AS 'MODULE_PATHNAME', 'array_sum'
LANGUAGE C IMMUTABLE;

/**
 * @brief This function finds the sum of the values in the array. NULLs are ignored. Return type is always FLOAT8 regardless of input. This function is meant to replace array_sum in the cases when sum may overflow the array type.
 *
 * @param x Array x
 * @returns Sum of x.
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.array_sum_big(x anyarray) RETURNS FLOAT8 
AS 'MODULE_PATHNAME', 'array_sum_big'
LANGUAGE C IMMUTABLE;

/**
 * @brief TThis function finds the mean of the values in the array. NULLs are ignored. Return type is the same as the input type.
 *
 * @param x Array x
 * @returns Mean of x.
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.array_mean(x anyarray) RETURNS FLOAT8 
AS 'MODULE_PATHNAME', 'array_mean'
LANGUAGE C IMMUTABLE;

/**
 * @brief This function finds the standard deviation of the values in the array. NULLs are ignored. Return type is the same as the input type.
 *
 * @param x Array x
 * @returns Standard deviation of x.
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.array_stddev(x anyarray) RETURNS FLOAT8 
AS 'MODULE_PATHNAME', 'array_stddev'
LANGUAGE C IMMUTABLE;

/**
 * @brief This function creates an array of set size (the argument value) of FLOAT8, initializing the values to 0.0;
 *
 * @param k Array size
 * @returns Array of size k.
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.array_of_float(k INT4) RETURNS FLOAT8[] 
AS 'MODULE_PATHNAME', 'array_of_float'
LANGUAGE C IMMUTABLE;

/**
 * @brief This function creates an array of set size (the argument value) of BIGINT, initializing the values to 0;
 *
 * @param k Array size.
 * @returns Array of size k.
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.array_of_bigint(k INT4) RETURNS INT8[] 
AS 'MODULE_PATHNAME', 'array_of_bigint'
LANGUAGE C IMMUTABLE;

/**
 * @brief This functions set every values in the array to some desired value (provided as the argument).
 *
 * @param x Some array
 * @param k Desired value
 * @returns Fills array with desired value.
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.array_fill(x anyarray, k anyelement) RETURNS anyarray 
AS 'MODULE_PATHNAME', 'array_fill'
LANGUAGE C IMMUTABLE;

/**
 * @brief This function takes an array as the input and executes element with multiplication by the scalar provided as the second argument, returning the resulting array. It requires that all the values are NON-NULL. Return type is the same as the input type.
 * @param x Array x
 * @param k Scalar
 * @returns Array with each element of x multiplied by scalar.
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.array_scalar_mult(x anyarray, k anyelement) RETURNS anyarray 
AS 'MODULE_PATHNAME', 'array_scalar_mult'
LANGUAGE C IMMUTABLE;

/**
 * @brief This function takes an array as the input and finds square root of each element in the array, returning the resulting array. It requires that all the values are NON-NULL. Return type is the same as the input type. This means that if the input if of the size INT, the results would also be rounded.
 *
 * @param x Array x
 * @returns Square root of all elements of x.
 *
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.array_sqrt(x anyarray) RETURNS anyarray 
AS 'MODULE_PATHNAME', 'array_sqrt'
LANGUAGE C IMMUTABLE;


/**
 * @brief Array normalization function.
 *
 * @param x Array x.
 * @return  Array normalized by its norm.
 *      
 */
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.normalize(x float8[]) RETURNS float8[]
AS 'MODULE_PATHNAME', 'array_normalize' LANGUAGE C IMMUTABLE STRICT;

/**
 * @brief ARRAY_AGG aggregate for compatibility with GPDB < 4.1 and Postgres < 9.0
 *        This is a slower solution than the built in array_agg that appears
 *        in later GPDB and Postgres versions
 */
CREATE AGGREGATE MADLIB_SCHEMA.array_agg( anyelement) ( 
   SFUNC     = array_append, 
   STYPE     = anyarray 
   m4_ifdef( `__GREENPLUM__',`, PREFUNC   = array_cat')
);