On 01/13/2012 02:50 PM, Andrew Dunstan wrote:
On 01/13/2012 12:31 AM, Hitoshi Harada wrote:
So my conclusion is it's better than nothing, but we could do
better job here.
From timeline perspective, it'd be ok to apply this patch and improve
more later in 9.3+.
I agree, let's look at the items other than the target list during
9.3. But I do think this addresses the biggest pain point.
Actually, it turns out to be very simple to add wrapping logic for the
FROM clause, as in the attached updated patch, and I think we should do
that for this round.
cheers
andrew
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 13758,13764 **** SELECT pg_type_is_visible('myschema.widget'::regtype);
<row>
<entry><literal><function>pg_get_viewdef(<parameter>view_name</parameter>, <parameter>pretty_bool</>)</function></literal></entry>
<entry><type>text</type></entry>
! <entry>get underlying <command>SELECT</command> command for view (<emphasis>deprecated</emphasis>)</entry>
</row>
<row>
<entry><literal><function>pg_get_viewdef(<parameter>view_oid</parameter>)</function></literal></entry>
--- 13758,13765 ----
<row>
<entry><literal><function>pg_get_viewdef(<parameter>view_name</parameter>, <parameter>pretty_bool</>)</function></literal></entry>
<entry><type>text</type></entry>
! <entry>get underlying <command>SELECT</command> command for view,
! lines with fields are wrapped to 80 columns if pretty_bool is true (<emphasis>deprecated</emphasis>)</entry>
</row>
<row>
<entry><literal><function>pg_get_viewdef(<parameter>view_oid</parameter>)</function></literal></entry>
***************
*** 13768,13774 **** SELECT pg_type_is_visible('myschema.widget'::regtype);
<row>
<entry><literal><function>pg_get_viewdef(<parameter>view_oid</parameter>, <parameter>pretty_bool</>)</function></literal></entry>
<entry><type>text</type></entry>
! <entry>get underlying <command>SELECT</command> command for view</entry>
</row>
<row>
<entry><literal><function>pg_options_to_table(<parameter>reloptions</parameter>)</function></literal></entry>
--- 13769,13782 ----
<row>
<entry><literal><function>pg_get_viewdef(<parameter>view_oid</parameter>, <parameter>pretty_bool</>)</function></literal></entry>
<entry><type>text</type></entry>
! <entry>get underlying <command>SELECT</command> command for view,
! lines with fields are wrapped to 80 columns if pretty_bool is true</entry>
! </row>
! <row>
! <entry><literal><function>pg_get_viewdef(<parameter>view_oid</parameter>, <parameter>wrap_int</>)</function></literal></entry>
! <entry><type>text</type></entry>
! <entry>get underlying <command>SELECT</command> command for view,
! wrapping lines with fields as specified, pretty printing is implied</entry>
</row>
<row>
<entry><literal><function>pg_options_to_table(<parameter>reloptions</parameter>)</function></literal></entry>
*** a/src/backend/utils/adt/ruleutils.c
--- b/src/backend/utils/adt/ruleutils.c
***************
*** 73,78 ****
--- 73,80 ----
#define PRETTYFLAG_PAREN 1
#define PRETTYFLAG_INDENT 2
+ #define PRETTY_WRAP_DEFAULT 79
+
/* macro to test if pretty action needed */
#define PRETTY_PAREN(context) ((context)->prettyFlags & PRETTYFLAG_PAREN)
#define PRETTY_INDENT(context) ((context)->prettyFlags & PRETTYFLAG_INDENT)
***************
*** 136,141 **** static SPIPlanPtr plan_getrulebyoid = NULL;
--- 138,144 ----
static const char *query_getrulebyoid = "SELECT * FROM pg_catalog.pg_rewrite WHERE oid = $1";
static SPIPlanPtr plan_getviewrule = NULL;
static const char *query_getviewrule = "SELECT * FROM pg_catalog.pg_rewrite WHERE ev_class = $1 AND rulename = $2";
+ static int pretty_wrap = PRETTY_WRAP_DEFAULT;
/* GUC parameters */
bool quote_all_identifiers = false;
***************
*** 381,386 **** pg_get_viewdef_ext(PG_FUNCTION_ARGS)
--- 384,406 ----
}
Datum
+ pg_get_viewdef_wrap(PG_FUNCTION_ARGS)
+ {
+ /* By OID */
+ Oid viewoid = PG_GETARG_OID(0);
+ int wrap = PG_GETARG_INT32(1);
+ int prettyFlags;
+ char *result;
+
+ /* calling this implies we want pretty printing */
+ prettyFlags = PRETTYFLAG_PAREN | PRETTYFLAG_INDENT;
+ pretty_wrap = wrap;
+ result = pg_get_viewdef_worker(viewoid, prettyFlags);
+ pretty_wrap = PRETTY_WRAP_DEFAULT;
+ PG_RETURN_TEXT_P(string_to_text(result));
+ }
+
+ Datum
pg_get_viewdef_name(PG_FUNCTION_ARGS)
{
/* By qualified name */
***************
*** 3013,3018 **** get_target_list(List *targetList, deparse_context *context,
--- 3033,3039 ----
char *sep;
int colno;
ListCell *l;
+ bool last_was_multiline = false;
sep = " ";
colno = 0;
***************
*** 3021,3026 **** get_target_list(List *targetList, deparse_context *context,
--- 3042,3051 ----
TargetEntry *tle = (TargetEntry *) lfirst(l);
char *colname;
char *attname;
+ StringInfoData targetbuf;
+ int leading_nl_pos = -1;
+ char *trailing_nl;
+ int pos;
if (tle->resjunk)
continue; /* ignore junk entries */
***************
*** 3030,3035 **** get_target_list(List *targetList, deparse_context *context,
--- 3055,3069 ----
colno++;
/*
+ * Put the new field spec into targetbuf so we can
+ * decide after we've got it whether or not it needs
+ * to go on a new line.
+ */
+
+ initStringInfo(&targetbuf);
+ context->buf = &targetbuf;
+
+ /*
* We special-case Var nodes rather than using get_rule_expr. This is
* needed because get_rule_expr will display a whole-row Var as
* "foo.*", which is the preferred notation in most contexts, but at
***************
*** 3063,3070 **** get_target_list(List *targetList, deparse_context *context,
if (colname) /* resname could be NULL */
{
if (attname == NULL || strcmp(attname, colname) != 0)
! appendStringInfo(buf, " AS %s", quote_identifier(colname));
}
}
}
--- 3097,3162 ----
if (colname) /* resname could be NULL */
{
if (attname == NULL || strcmp(attname, colname) != 0)
! appendStringInfo(&targetbuf, " AS %s", quote_identifier(colname));
! }
!
! /* Restore context buffer */
!
! context->buf = buf;
!
! /* Does the new field start with whitespace plus a new line? */
!
! for (pos=0; pos < targetbuf.len; pos++)
! {
! if (targetbuf.data[pos] == '\n')
! {
! leading_nl_pos = pos;
! break;
! }
! if (targetbuf.data[pos] > ' ')
! break;
! }
!
! /* Locate the start of the current line in the buffer */
!
! trailing_nl = (strrchr(buf->data,'\n'));
! if (trailing_nl == NULL)
! trailing_nl = buf->data;
! else
! trailing_nl++;
!
! /*
! * If the field we're adding is the first in the list, or it already
! * has a leading newline, or wrap mode is disabled (pretty_wrap < 0),
! * don't add anything.
! * Otherwise, add a newline, plus some indentation, if either the
! * new field would cause an overflow or the last field used more than
! * one line.
! */
!
! if (colno > 1 &&
! leading_nl_pos == -1 &&
! pretty_wrap >= 0 &&
! ((strlen(trailing_nl) + strlen(targetbuf.data) > pretty_wrap) ||
! last_was_multiline))
! {
! appendContextKeyword(context, "", -PRETTYINDENT_STD,
! PRETTYINDENT_STD, PRETTYINDENT_VAR);
}
+
+ /* Add the new field */
+
+ appendStringInfoString(buf, targetbuf.data);
+
+
+ /* Keep track of this field's status for next iteration */
+
+ last_was_multiline =
+ (strchr(targetbuf.data + leading_nl_pos + 1,'\n') != NULL);
+
+ /* cleanup */
+
+ pfree (targetbuf.data);
}
}
***************
*** 6445,6455 **** get_from_clause(Query *query, const char *prefix, deparse_context *context)
appendContextKeyword(context, prefix,
-PRETTYINDENT_STD, PRETTYINDENT_STD, 2);
first = false;
}
else
appendStringInfoString(buf, ", ");
- get_from_clause_item(jtnode, query, context);
}
}
--- 6537,6588 ----
appendContextKeyword(context, prefix,
-PRETTYINDENT_STD, PRETTYINDENT_STD, 2);
first = false;
+
+ get_from_clause_item(jtnode, query, context);
}
else
+ {
+ StringInfoData targetbuf;
+ char *trailing_nl;
+
appendStringInfoString(buf, ", ");
+
+ initStringInfo(&targetbuf);
+ context->buf = &targetbuf;
+
+ get_from_clause_item(jtnode, query, context);
+
+ context->buf = buf;
+
+ /* Locate the start of the current line in the buffer */
+
+ trailing_nl = (strrchr(buf->data,'\n'));
+ if (trailing_nl == NULL)
+ trailing_nl = buf->data;
+ else
+ trailing_nl++;
+
+ /*
+ * Add a newline, plus some indentation, if pretty_wrap is on and the
+ * new from-clause item would cause an overflow.
+ */
+
+ if (pretty_wrap >= 0 &&
+ (strlen(trailing_nl) + strlen(targetbuf.data) > pretty_wrap))
+ {
+ appendContextKeyword(context, "", -PRETTYINDENT_STD,
+ PRETTYINDENT_STD, PRETTYINDENT_VAR);
+ }
+
+ /* Add the new item */
+
+ appendStringInfoString(buf, targetbuf.data);
+
+ /* cleanup */
+
+ pfree (targetbuf.data);
+ }
}
}
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 3715,3720 **** DATA(insert OID = 2505 ( pg_get_viewdef PGNSP PGUID 12 1 0 0 0 f f f t f s 2
--- 3715,3722 ----
DESCR("select statement of a view with pretty-print option");
DATA(insert OID = 2506 ( pg_get_viewdef PGNSP PGUID 12 1 0 0 0 f f f t f s 2 0 25 "26 16" _null_ _null_ _null_ _null_ pg_get_viewdef_ext _null_ _null_ _null_ ));
DESCR("select statement of a view with pretty-print option");
+ DATA(insert OID = 3145 ( pg_get_viewdef PGNSP PGUID 12 1 0 0 0 f f f t f s 2 0 25 "26 23" _null_ _null_ _null_ _null_ pg_get_viewdef_wrap _null_ _null_ _null_ ));
+ DESCR("select statement of a view with pretty-printing and specified line wrapping");
DATA(insert OID = 2507 ( pg_get_indexdef PGNSP PGUID 12 1 0 0 0 f f f t f s 3 0 25 "26 23 16" _null_ _null_ _null_ _null_ pg_get_indexdef_ext _null_ _null_ _null_ ));
DESCR("index description (full create statement or single expression) with pretty-print option");
DATA(insert OID = 2508 ( pg_get_constraintdef PGNSP PGUID 12 1 0 0 0 f f f t f s 2 0 25 "26 16" _null_ _null_ _null_ _null_ pg_get_constraintdef_ext _null_ _null_ _null_ ));
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
***************
*** 624,629 **** extern Datum pg_get_ruledef(PG_FUNCTION_ARGS);
--- 624,630 ----
extern Datum pg_get_ruledef_ext(PG_FUNCTION_ARGS);
extern Datum pg_get_viewdef(PG_FUNCTION_ARGS);
extern Datum pg_get_viewdef_ext(PG_FUNCTION_ARGS);
+ extern Datum pg_get_viewdef_wrap(PG_FUNCTION_ARGS);
extern Datum pg_get_viewdef_name(PG_FUNCTION_ARGS);
extern Datum pg_get_viewdef_name_ext(PG_FUNCTION_ARGS);
extern Datum pg_get_indexdef(PG_FUNCTION_ARGS);
*** a/src/test/regress/expected/polymorphism.out
--- b/src/test/regress/expected/polymorphism.out
***************
*** 1381,1387 **** select * from dfview;
c3 | bigint | | plain |
c4 | bigint | | plain |
View definition:
! SELECT int8_tbl.q1, int8_tbl.q2, dfunc(int8_tbl.q1, int8_tbl.q2, flag := int8_tbl.q1 > int8_tbl.q2) AS c3, dfunc(int8_tbl.q1, flag := int8_tbl.q1 < int8_tbl.q2, b := int8_tbl.q2) AS c4
FROM int8_tbl;
drop view dfview;
--- 1381,1389 ----
c3 | bigint | | plain |
c4 | bigint | | plain |
View definition:
! SELECT int8_tbl.q1, int8_tbl.q2,
! dfunc(int8_tbl.q1, int8_tbl.q2, flag := int8_tbl.q1 > int8_tbl.q2) AS c3,
! dfunc(int8_tbl.q1, flag := int8_tbl.q1 < int8_tbl.q2, b := int8_tbl.q2) AS c4
FROM int8_tbl;
drop view dfview;
*** a/src/test/regress/expected/rules.out
--- b/src/test/regress/expected/rules.out
***************
*** 1568,1570 **** select * from only t1_2;
--- 1568,1603 ----
19
(10 rows)
+ -- test various flavors of pg_get_viewdef()
+ select pg_get_viewdef('shoe'::regclass) as unpretty;
+ unpretty
+ -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ SELECT sh.shoename, sh.sh_avail, sh.slcolor, sh.slminlen, (sh.slminlen * un.un_fact) AS slminlen_cm, sh.slmaxlen, (sh.slmaxlen * un.un_fact) AS slmaxlen_cm, sh.slunit FROM shoe_data sh, unit un WHERE (sh.slunit = un.un_name);
+ (1 row)
+
+ select pg_get_viewdef('shoe'::regclass,true) as pretty;
+ pretty
+ -------------------------------------------------------------
+ SELECT sh.shoename, sh.sh_avail, sh.slcolor, sh.slminlen, +
+ sh.slminlen * un.un_fact AS slminlen_cm, sh.slmaxlen, +
+ sh.slmaxlen * un.un_fact AS slmaxlen_cm, sh.slunit +
+ FROM shoe_data sh, unit un +
+ WHERE sh.slunit = un.un_name;
+ (1 row)
+
+ select pg_get_viewdef('shoe'::regclass,0) as prettier;
+ prettier
+ -----------------------------------------------
+ SELECT sh.shoename, +
+ sh.sh_avail, +
+ sh.slcolor, +
+ sh.slminlen, +
+ sh.slminlen * un.un_fact AS slminlen_cm, +
+ sh.slmaxlen, +
+ sh.slmaxlen * un.un_fact AS slmaxlen_cm, +
+ sh.slunit +
+ FROM shoe_data sh, +
+ unit un +
+ WHERE sh.slunit = un.un_name;
+ (1 row)
+
*** a/src/test/regress/expected/with.out
--- b/src/test/regress/expected/with.out
***************
*** 277,294 **** SELECT pg_get_viewdef('vsubdepartment'::regclass);
(1 row)
SELECT pg_get_viewdef('vsubdepartment'::regclass, true);
! pg_get_viewdef
! --------------------------------------------------------------------------------------
! WITH RECURSIVE subdepartment AS ( +
! SELECT department.id, department.parent_department, department.name+
! FROM department +
! WHERE department.name = 'A'::text +
! UNION ALL +
! SELECT d.id, d.parent_department, d.name +
! FROM department d, subdepartment sd +
! WHERE d.parent_department = sd.id +
! ) +
! SELECT subdepartment.id, subdepartment.parent_department, subdepartment.name +
FROM subdepartment;
(1 row)
--- 277,295 ----
(1 row)
SELECT pg_get_viewdef('vsubdepartment'::regclass, true);
! pg_get_viewdef
! -------------------------------------------------------------------------------
! WITH RECURSIVE subdepartment AS ( +
! SELECT department.id, department.parent_department, +
! department.name +
! FROM department +
! WHERE department.name = 'A'::text +
! UNION ALL +
! SELECT d.id, d.parent_department, d.name +
! FROM department d, subdepartment sd +
! WHERE d.parent_department = sd.id +
! ) +
! SELECT subdepartment.id, subdepartment.parent_department, subdepartment.name+
FROM subdepartment;
(1 row)
*** a/src/test/regress/sql/rules.sql
--- b/src/test/regress/sql/rules.sql
***************
*** 927,929 **** update t1 set a = 4 where a = 5;
--- 927,935 ----
select * from only t1;
select * from only t1_1;
select * from only t1_2;
+
+ -- test various flavors of pg_get_viewdef()
+
+ select pg_get_viewdef('shoe'::regclass) as unpretty;
+ select pg_get_viewdef('shoe'::regclass,true) as pretty;
+ select pg_get_viewdef('shoe'::regclass,0) as prettier;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
