Postgresql tablefunc Module

Products

• VMware Tanzu Greenplum

Issue/Introduction

This article discusses about the Postgresql tablefunc Module.

Postresql provides a module called tablefunc which includes various functions that return tables. This module is not provided by the Greenplum database distribution in 4.3.x nor 5.x. The module and the functions are not supported by Pivotal.

Environment

Product Version: 4.3

Resolution

The functions provided in the tablefunc are listed in the table below.
Further information on the module can be found in the PostgreSql documentation.

 

 

 

Function	Returns	Description
normal_rand(int numvals, float8 mean, float8 stddev)	setof float8	Produces a set of normally distributed random values
crosstab(text sql)	setof record	Produces a "pivot table" containing row names plus N value columns, where N is determined by the row type specified in the calling query
crosstabN(text sql)	setof table_crosstab_N	Produces a "pivot table" containing row names plus N value columns. crosstab2, crosstab3, and crosstab4 are predefined, but you can create additional crosstabN functions as described below
crosstab(text source_sql, text category_sql)	setof record	Produces a "pivot table" with the value columns specified by a second query
crosstab(text sql, int N)	setof record	Obsolete version of crosstab(text). The parameter N is now ignored, since the number of value columns is always determined by the calling query
connectby(text relname, text keyid_fld, text parent_keyid_fld [, text orderby_fld ], text start_with, int max_depth [, text branch_delim ])	setof record	Produces a representation of a hierarchical tree structure

The module can be installed in Greenplum, but it is the responsibility of the user/database owner to verify the functions are working properly. Pivotal does not support the module and functions provided by the module.