MADlib
0.7 A newer version is available
User Documentation
|
00001 /* ----------------------------------------------------------------------- *//** 00002 * 00003 * @file compatibility.sql_in 00004 * 00005 * @brief Compatibility SQL functions 00006 * 00007 * @sa For a brief overview of compatibility functions, see the 00008 * module description \ref grp_compatibility. 00009 * 00010 *//* ----------------------------------------------------------------------- */ 00011 00012 m4_include(`SQLCommon.m4') 00013 m4_changequote(<!,!>) 00014 00015 00016 /** 00017 @addtogroup grp_compatibility 00018 00019 @about 00020 00021 The compatibility module replicates standard SQL functionality that cannot be 00022 used on all platforms supported by MADlib -- be it due to incomplete 00023 implementation of the SQL standard or bugs. 00024 00025 This module contains workarounds for the following issues: 00026 00027 - <tt>CREATE TABLE <em>table_name</em> AS <em>query</em></tt> statements where 00028 <em>query</em> contains certain MADlib functions fails with the error 00029 “function cannot execute on segment because it issues a non-SELECT statement” 00030 (Greenplum versions before version 4.2, and in rare special cases also later 00031 versions). 00032 The workaround is: 00033 <pre>SELECT \ref create_table_as('<em>table_name</em>', $$ 00034 <em>query</em> 00035 $$, 'BY (<em>column</em>, [...]) | RANDOMLY');</pre> 00036 - <tt>INSERT INTO <em>table_name</em> <em>query</em></tt> where <em>query</em> 00037 contains certain MADlib functions fails with the error “function cannot 00038 execute on segment because it issues a non-SELECT statement” (Greenplum 00039 versions before version 4.2, and in rare special cases also later versions). 00040 The workaround is: 00041 <pre>SELECT \ref insert_into('<em>table_name</em>', $$ 00042 <em>query</em> 00043 $$);</pre> 00044 00045 @note 00046 These functions are not needed on PostgreSQL and are usually not needed on 00047 Greenplum 4.2 and later. However, they are always installed for compatibility 00048 with other platforms. 00049 Workarounds should be used only when necessary. For portability and best 00050 performance, standard SQL should be prefered whenever possible. 00051 00052 @literature 00053 00054 [1] Greenplum Admin Guide 00055 00056 @sa File compatibility.sql_in (documenting the SQL functions) 00057 00058 */ 00059 00060 /** 00061 * @brief Mimick <tt>INSERT INTO</tt>. Create new rows in a table. 00062 * 00063 * @param inTableName The name (optionally schema-qualified) of an existing 00064 * table. 00065 * @param inSQL A SELECT command. 00066 * 00067 * @usage 00068 * Use in the same way as the <tt>INSERT INTO</tt> statement: 00069 * <pre>SELECT insert_into('<em>table_name</em>', $$ 00070 * <em>query</em> 00071 *$$);</pre> 00072 * 00073 * @examp 00074 * <pre>SELECT insert_into('public.test', $$ 00075 * SELECT * FROM generate_series(1,10) AS id 00076 *$$);\n 00077 *SELECT insert_into('logregr_results', $$ 00078 * SELECT * FROM logregr('data', 'y', 'x') 00079 *$$);</pre> 00080 * 00081 * @note This function is a workaround. For compliance with the SQL standard and 00082 * for optimal performance, please use the normal <tt>INSERT INTO</tt> 00083 * statement whenever possible. 00084 * Known caveats of this workaround: 00085 * - For queries returning a large number of rows, this function will be 00086 * significantly slower than the <tt>INSERT INTO</tt> statement. 00087 */ 00088 CREATE FUNCTION MADLIB_SCHEMA.insert_into( 00089 "inTableName" VARCHAR, 00090 "inSQL" VARCHAR) 00091 RETURNS VOID 00092 LANGUAGE plpgsql 00093 VOLATILE 00094 RETURNS NULL ON NULL INPUT 00095 AS $$ 00096 DECLARE 00097 oldClientMinMessages VARCHAR; 00098 BEGIN 00099 oldClientMinMessages := 00100 (SELECT setting FROM pg_settings WHERE name = 'client_min_messages'); 00101 EXECUTE 'SET client_min_messages TO warning'; 00102 00103 PERFORM MADLIB_SCHEMA.create_schema_pg_temp(); 00104 00105 EXECUTE 00106 'DROP FUNCTION IF EXISTS pg_temp._madlib_temp_function(); 00107 CREATE FUNCTION pg_temp._madlib_temp_function() 00108 RETURNS VOID 00109 LANGUAGE plpgsql 00110 AS $_madlib_temp_function$ 00111 DECLARE 00112 result ' || "inTableName" || '%ROWTYPE; 00113 BEGIN 00114 FOR result IN EXECUTE 00115 $_madlib_temp_function_string$ 00116 ' || "inSQL" || ' 00117 $_madlib_temp_function_string$ 00118 LOOP 00119 00120 INSERT INTO ' || "inTableName" || ' VALUES (result.*); 00121 END LOOP; 00122 END; 00123 $_madlib_temp_function$'; 00124 00125 -- We call _madlib_temp_function() in a separate 00126 -- EXECUTE statement because it is not yet visible before 00127 -- (we would see "ERROR: function _madlib_temp_function() does not exist") 00128 EXECUTE 00129 'SELECT pg_temp._madlib_temp_function(); 00130 SET client_min_messages TO ' || oldClientMinMessages || ';'; 00131 END; 00132 $$; 00133 00134 00135 CREATE FUNCTION MADLIB_SCHEMA.internal_create_table_as( 00136 "inTemporary" BOOLEAN, 00137 "inTableName" VARCHAR, 00138 "inSQL" VARCHAR, 00139 "inDistributed" VARCHAR) 00140 RETURNS VOID 00141 LANGUAGE plpgsql 00142 VOLATILE 00143 RETURNS NULL ON NULL INPUT 00144 AS $$ 00145 DECLARE 00146 whatToCreate VARCHAR; 00147 BEGIN 00148 IF "inTemporary" = TRUE THEN 00149 whatToCreate := 'TEMPORARY TABLE'; 00150 ELSE 00151 whatToCreate := 'TABLE'; 00152 END IF; 00153 00154 -- We separate the following EXECUTE statement because it is prone 00155 -- to generate an exception -- e.g., if the table already exists 00156 -- In that case we want to keep the context in the error message short 00157 EXECUTE 00158 'CREATE ' || whatToCreate || ' ' || "inTableName" || ' AS 00159 SELECT * FROM (' || "inSQL" || ') AS _madlib_ignore 00160 WHERE FALSE 00161 m4_ifdef(<!__GREENPLUM__!>,<! 00162 DISTRIBUTED ' || "inDistributed"; 00163 !>,<! 00164 '; 00165 !>) 00166 00167 PERFORM MADLIB_SCHEMA.insert_into("inTableName", "inSQL"); 00168 END; 00169 $$; 00170 00171 00172 /** 00173 * @brief Mimick <tt>CREATE TABLE AS</tt>. Create a table and fill it with data 00174 * computed by a \c SELECT command. 00175 * 00176 * @param inTableName The name (optionally schema-qualified) of the table to be 00177 * created. 00178 * @param inSQL A SELECT command. 00179 * @param inDistributed The Greenplum Database distribution policy for the 00180 * table. This can be either \c RANDOMLY, which is the default, or 00181 * <tt>BY (<em>column</em>, [...])</tt>. This parameter is ignored on 00182 * PostgreSQL. 00183 * 00184 * @usage 00185 * Use in the same way as the <tt>CREATE TABLE AS</tt> statement: 00186 * <pre>SELECT create_table_as('<em>table_name</em>', $$ 00187 * <em>query</em> 00188 *$$, 'BY (<em>column</em>, [...]) | RANDOMLY');</pre> 00189 * 00190 * @examp 00191 * <pre>SELECT create_table_as('public.test', $$ 00192 * SELECT * FROM generate_series(1,10) AS id 00193 *$$, 'BY (id)');\n 00194 *SELECT create_table_as('logregr_result', $$ 00195 * SELECT * FROM logregr('data', 'y', 'x') 00196 *$$, 'RANDOMLY');</pre> 00197 * 00198 * @note This function is a workaround. For compliance with the SQL standard and 00199 * for optimal performance, please use the normal <tt>CREATE TABLE AS</tt> 00200 * statement whenever possible. 00201 * Known caveats of this workaround: 00202 * - For queries returning a large number of rows, this function will be 00203 * significantly slower than the <tt>CREATE TABLE AS</tt> statement. 00204 */ 00205 CREATE FUNCTION MADLIB_SCHEMA.create_table_as( 00206 "inTableName" VARCHAR, 00207 "inSQL" VARCHAR, 00208 "inDistributed" VARCHAR /*+ = 'RANDOMLY' */) 00209 RETURNS VOID 00210 LANGUAGE sql 00211 VOLATILE 00212 RETURNS NULL ON NULL INPUT 00213 AS $$ 00214 SELECT MADLIB_SCHEMA.internal_create_table_as(FALSE, 00215 $1, $2, $3); 00216 $$; 00217 00218 00219 CREATE FUNCTION MADLIB_SCHEMA.create_table_as( 00220 "inTableName" VARCHAR, 00221 "inSQL" VARCHAR) 00222 RETURNS VOID 00223 LANGUAGE sql 00224 VOLATILE 00225 RETURNS NULL ON NULL INPUT 00226 AS $$ 00227 SELECT MADLIB_SCHEMA.internal_create_table_as(FALSE, 00228 $1, $2, 'RANDOMLY'); 00229 $$; 00230 00231 00232 /** 00233 * @brief Mimick <tt>CREATE TEMPORARY TABLE AS</tt>. Create a temporary table 00234 * and fill it with data computed by a \c SELECT command. 00235 * 00236 * @sa create_table_as() 00237 */ 00238 CREATE FUNCTION MADLIB_SCHEMA.create_temporary_table_as( 00239 "inTableName" VARCHAR, 00240 "inSQL" VARCHAR, 00241 "inDistributed" VARCHAR /*+ = 'RANDOMLY' */) 00242 RETURNS VOID 00243 LANGUAGE sql 00244 VOLATILE 00245 RETURNS NULL ON NULL INPUT 00246 AS $$ 00247 SELECT MADLIB_SCHEMA.internal_create_table_as(TRUE, 00248 $1, $2, $3); 00249 $$; 00250 00251 00252 CREATE FUNCTION MADLIB_SCHEMA.create_temporary_table_as( 00253 "inTableName" VARCHAR, 00254 "inSQL" VARCHAR) 00255 RETURNS VOID 00256 LANGUAGE sql 00257 VOLATILE 00258 RETURNS NULL ON NULL INPUT 00259 AS $$ 00260 SELECT MADLIB_SCHEMA.internal_create_table_as(TRUE, 00261 $1, $2, 'RANDOMLY'); 00262 $$; 00263 00264 00265 /* 00266 * We also provide create_temp_table_as, but we do not advertise it in our 00267 * documentation. 00268 */ 00269 CREATE FUNCTION MADLIB_SCHEMA.create_temp_table_as( 00270 "inTableName" VARCHAR, 00271 "inSQL" VARCHAR, 00272 "inDistributed" VARCHAR /*+ = 'RANDOMLY' */) 00273 RETURNS VOID 00274 LANGUAGE sql 00275 VOLATILE 00276 RETURNS NULL ON NULL INPUT 00277 AS $$ 00278 SELECT MADLIB_SCHEMA.internal_create_table_as(TRUE, 00279 $1, $2, $3); 00280 $$; 00281 00282 00283 CREATE FUNCTION MADLIB_SCHEMA.create_temp_table_as( 00284 "inTableName" VARCHAR, 00285 "inSQL" VARCHAR) 00286 RETURNS VOID 00287 LANGUAGE sql 00288 VOLATILE 00289 RETURNS NULL ON NULL INPUT 00290 AS $$ 00291 SELECT MADLIB_SCHEMA.internal_create_table_as(TRUE, 00292 $1, $2, 'RANDOMLY'); 00293 $$; 00294 00295 m4_changequote(<!`!>,<!'!>)