/trunk/schemas/util.sql - Diff - BIEN 3 - NCEAS Projects

« Previous | Next »

Revision 13645

fix: schemas/util.sql: view_def_to_orig(): also need to merge .* expressions resulting from a SELECT * of a join, to avoid duplicated columns

     CREATE FUNCTION view_def_to_orig(view_def text) RETURNS text
         LANGUAGE sql IMMUTABLE
         AS $_$
     SELECT
     regexp_replace(
     regexp_replace(
     $1
+    ,
     /* unexpand expanded * expressions. any list of 6+ cols from the same table is
     treated as a * expression. */
     SELECT regexp_replace($1,
     '((?:"[^"\s]+"|\w+)\.)(?:"[^"\s]+"|\w+)'||
     	/* 1st col, which lacks separator before.
     	*note*: can't prepend \y because it considers only \w chars, not " */
     '(,[[:blank:]]*
     [[:blank:]]*)\1(?:"[^"\s]+"|\w+)'|| -- 2nd col, which has separator before
     '(?:\2\1(?:"[^"\s]+"|\w+)){4,}', -- later cols, w/ same table name and separator
     '\1*'/*prefix w/ table*/, 'g')
     '\1*'/*prefix w/ table*/,
     'g')
+    ,
     /* merge .* expressions resulting from a SELECT * of a join. any list of
     multiple .* expressions is treated as a SELECT * . */
     '(?:"[^"\s]+"|\w+)\.\*'||
     	/* 1st table, which lacks separator before.
     	*note*: can't prepend \y because it considers only \w chars, not " */
     '(,[[:blank:]]*
     [[:blank:]]*)(?:"[^"\s]+"|\w+)\.\*'|| -- 2nd table, which has separator before
     '(?:\1(?:"[^"\s]+"|\w+)\.\*)*', -- later tables, w/ same separator
     '*',
     'g')
     $_$;

Also available in: Unified diff