On 12/22/2011 06:14 PM, Andrew Dunstan wrote:
On 12/22/2011 06:11 PM, Andrew Dunstan wrote:
On 12/22/2011 02:17 PM, Andrew Dunstan wrote:
On 12/22/2011 01:05 PM, Tom Lane wrote:
Maybe, though I fear it might complicate the ruleutils code a bit.
You'd probably have to build the output for a column first and then
see how long it is before deciding whether to insert a newline.
In short, I don't mind trying to make this work better, but I think it
will take more work than a two-line patch.
OK. Let me whip something up. I had already come to the conclusion
you did about how best to do this.
Here's a WIP patch. At least it's fairly localized, but as expected
it's rather more than 2 lines :-). Sample output:
regression=# \pset format unaligned
Output format is unaligned.
regression=# select pg_get_viewdef('shoelace',true);
pg_get_viewdef
SELECT s.sl_name, s.sl_avail, s.sl_color, s.sl_len, s.sl_unit,
s.sl_len * u.un_fact AS sl_len_cm
FROM shoelace_data s, unit u
WHERE s.sl_unit = u.un_name;
(1 row)
regression=#
I just had an idea. We could invent a flavor of pg_get_viewdef() that
took an int as the second param, that would be the wrap width. For
the boolean case as above, 80 would be implied. 0 would mean always
wrap. psql could be made to default to the window width, or maybe
window width - 1, but we could provide a psql setting that would
override it.
This time with patch.
Updated, with docs and tests. Since the docs mark the versions of
pg_get_viewdef() that take text as the first param as deprecated, I
removed that variant of the new flavor. I left adding extra psql support
to another day - I think this already does a good job of cleaning it up
without any extra adjustments.
I'll add this to the upcoming commitfest.
cheers
andrew
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 2e06346..0418591 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -13758,7 +13758,8 @@ 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>
+ <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,7 +13769,14 @@ 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>
+ <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>
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 29df748..5b774f0 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -73,6 +73,8 @@
#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,6 +138,7 @@ static SPIPlanPtr plan_getrulebyoid = NULL;
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,6 +384,23 @@ pg_get_viewdef_ext(PG_FUNCTION_ARGS)
}
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,6 +3033,7 @@ get_target_list(List *targetList, deparse_context *context,
char *sep;
int colno;
ListCell *l;
+ bool last_was_multiline = false;
sep = " ";
colno = 0;
@@ -3021,6 +3042,10 @@ get_target_list(List *targetList, deparse_context *context,
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,6 +3055,15 @@ get_target_list(List *targetList, deparse_context *context,
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,8 +3097,66 @@ 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));
+ 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);
}
}
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index c893c3a..83f65b8 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -3715,6 +3715,8 @@ DATA(insert OID = 2505 ( pg_get_viewdef PGNSP PGUID 12 1 0 0 0 f f f t f s 2
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_ ));
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index aa36db6..49e7d3b 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -624,6 +624,7 @@ extern Datum pg_get_ruledef(PG_FUNCTION_ARGS);
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);
diff --git a/src/test/regress/expected/polymorphism.out b/src/test/regress/expected/polymorphism.out
index cb3d756..1e87953 100644
--- a/src/test/regress/expected/polymorphism.out
+++ b/src/test/regress/expected/polymorphism.out
@@ -1381,7 +1381,9 @@ 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
+ 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;
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 454e1f9..019ca5c 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1568,3 +1568,35 @@ select * from only t1_2;
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)
+
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index c4b0456..d34492e 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -277,18 +277,19 @@ 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 +
+ 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)
diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql
index 16dc106..ea665a3 100644
--- a/src/test/regress/sql/rules.sql
+++ b/src/test/regress/sql/rules.sql
@@ -927,3 +927,9 @@ update t1 set a = 4 where a = 5;
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
