Re: psql: add \pset true/false

2015-12-04 9:37 GMT+01:00 Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp
>:

> Hello, I think this is the my last proposal of an idea on
> psql-side generic solution. Sorry for bothering.
>
> > My environment is CentOS7. But completely forgot Windows
> > environment (or other platforms). I agree with you. It will
> > indeed too much considering all possible platforms.
>
> Ok, DLL is not practical since it would not only be rather
> complex considering multi platform but used only by this feature
> and no other user of DLL is not in sight. It's convincing enough
> for me.
>
> Then, how about calling subprocess for purpose? popen() looks to
> be platform-independent so it is available to call arbitrary
> external programs or scripts.
>
>
> There are several obvious problems on this.
>
> - Tremendously slow since this executes the program for every value.
>
> - Dangerous in some aspect. Setting it by environment variable
> is too dengerous but I'm not confidento whether settig by
> \pset is safer as acceptable.
>
> Is it still too complex? Or too slow?
>

long time I am dream about integrating Lua to psql

It is fast enough for these purpose and can be used for custom macros, ..

Regards

Pavel

>
> regards,
>
> --
> Kyotaro Horiguchi
> NTT Open Source Software Center
>
> diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
> index 438a4ec..5dad70b 100644
> --- a/src/bin/psql/command.c
> +++ b/src/bin/psql/command.c
> @@ -1148,7 +1148,8 @@ exec_command(const char *cmd,
>
> int i;
> static const char *const my_list[] = {
> - "border", "columns", "expanded",
> "fieldsep", "fieldsep_zero",
> + "border", "columns", "column_filter",
> + "expanded", "fieldsep", "fieldsep_zero",
> "footer", "format", "linestyle", "null",
> "numericlocale", "pager",
> "pager_min_lines",
> "recordsep", "recordsep_zero",
> @@ -2695,6 +2696,17 @@ do_pset(const char *param, const char *value,
> printQueryOpt *popt, bool quiet)
> if (value)
> popt->topt.columns = atoi(value);
> }
> + /* set column filter */
> + else if (strcmp(param, "columnfilter") == 0)
> + {
> + if (vallen > 0)
> + popt->topt.columnfilter = pg_strdup(value);
> + else
> + {
> + if (popt->topt.columnfilter)
> pg_free(popt->topt.columnfilter);
> + popt->topt.columnfilter = NULL;
> + }
> + }
> else
> {
> psql_error("\\pset: unknown option: %s\n", param);
> @@ -2726,6 +2738,15 @@ printPsetInfo(const char *param, struct
> printQueryOpt *popt)
> printf(_("Target width is %d.\n"),
> popt->topt.columns);
> }
>
> + /* show the target width for the wrapped format */
> + else if (strcmp(param, "columnfilter") == 0)
> + {
> + if (!popt->topt.columnfilter)
> + printf(_("Column filter is unset.\n"));
> + else
> + printf(_("Column filter is \"%s\".\n"),
> popt->topt.columnfilter);
> + }
> +
> /* show expanded/vertical mode */
> else if (strcmp(param, "x") == 0 || strcmp(param, "expanded") == 0
> || strcmp(param, "vertical") == 0)
> {
> @@ -2938,6 +2959,8 @@ pset_value_string(const char *param, struct
> printQueryOpt *popt)
> return psprintf("%d", popt->topt.border);
> else if (strcmp(param, "columns") == 0)
> return psprintf("%d", popt->topt.columns);
> + else if (strcmp(param, "columnfilter") == 0)
> + return pstrdup(popt->topt.columnfilter);
> else if (strcmp(param, "expanded") == 0)
> return pstrdup(popt->topt.expanded == 2
> ? "auto"
> diff --git a/src/bin/psql/print.c b/src/bin/psql/print.c
> index ad4350e..9731e54 100644
> --- a/src/bin/psql/print.c
> +++ b/src/bin/psql/print.c
> @@ -195,7 +195,7 @@ static void IsPagerNeeded(const printTableContent
> *cont, const int extra_lines,
> FILE **fout, bool *is_pager);
>
> static void print_aligned_vertical(const printTableContent *cont, FILE
> *fout);
> -
> +static char *valfilter(char *filtname, char *origcell, int colnum, int
> type, bool *mustfree);
>
> /* Count number of digits in integral part of number */
> static int
> @@ -3145,6 +3145,86 @@ printTable(const printTableContent *cont, FILE
> *fout, FILE *flog)
> ClosePager(fout);
> }
>
> +static char *
> +valfilter(char *filtname, char *origcell, int colnum, int type, bool
> *mustfree)
> +{
> + char *cmdline;
> + int buflen = strlen(filtname) + 1 +
> + log10(colnum > 0 ? colnum : 1) + 2 + log10(type) + 2 +
> + + strlen(origcell) + 1 ;
> + FILE *fp;
> + size_t celllen;
> + char *newcell = NULL;
> + char *p;
> + int additional_bytes;
> + bool escape_needed = false;
> +
> + /* Check necessity of escaping */
> + for (additional_bytes = 0, p = origcell ; *p ; p++)
> + {
> + if (*p == '\'')
> + {
> + additional_bytes += 4; /* strlen('"'"') - 1 */
> + escape_needed = true;
> + }
> + else if (*p == ' ')
> + escape_needed = true;
> + }
> +
> + /* Escaping needed */
> + if (escape_needed)
> + {
> + char *q;
> + char *str;
> + int newlen;
> +
> + additional_bytes += 2;
> + newlen = strlen(origcell) + 1 + additional_bytes;
> + str = (char *)pg_malloc(newlen);
> +
> + q = str;
> + *q++ = '\'';
> + for (p = origcell ; *p ; p++)
> + {
> + if (*p == '\'')
> + {
> + strcpy(q, "'\"'\"'");
> + q += 5;
> + }
> + else
> + *q++ = *p;
> + }
> + *q++ = '\'';
> + *q++ = '\0';
> + Assert(q - str == newlen);
> +
> + if(*mustfree) free(origcell);
> + origcell = str;
> + *mustfree = true;
> + buflen += additional_bytes;
> + }
> +
> + cmdline = pg_malloc(buflen);
> + snprintf(cmdline, buflen, "%s %d %d %s", filtname, type, colnum,
> origcell);
> + fp = popen(cmdline, "r");
> +
> + /* fail silently */
> + if (!fp) return origcell;
> +
> +
> + /* receive the result from the filter */
> + if (*mustfree) free(origcell);
> + getline(&newcell, &celllen, fp); /* POSIX 2008 */
> + fclose(fp);
> + pg_free(cmdline);
> +
> + /* chop newlines */
> + for (p = newcell ; *p && *p != '\n' && *p != '\r' ; p++);
> + *p = 0;
> + *mustfree = true;
> + return newcell;
> +}
> +
> /*
> * Use this to print query results
> *
> @@ -3209,7 +3289,14 @@ printQuery(const PGresult *result, const
> printQueryOpt *opt, FILE *fout, FILE *f
> cell = opt->nullPrint ? opt->nullPrint :
> "";
> else
> {
> - cell = PQgetvalue(result, r, c);
> + /* This filter is quite slow */
> + if (opt->topt.columnfilter)
> + cell =
> valfilter(opt->topt.columnfilter,
> +
> PQgetvalue(result, r, c),
> + c,
> +
> PQftype(result, c),
> +
> &mustfree);
> +
> if (cont.aligns[c] == 'r' &&
> opt->topt.numericLocale)
> {
> cell = format_numeric_locale(cell);
> diff --git a/src/bin/psql/print.h b/src/bin/psql/print.h
> index b0b6bf5..5f2e2b3 100644
> --- a/src/bin/psql/print.h
> +++ b/src/bin/psql/print.h
> @@ -109,6 +109,7 @@ typedef struct printTableOpt
> unicode_linestyle unicode_border_linestyle;
> unicode_linestyle unicode_column_linestyle;
> unicode_linestyle unicode_header_linestyle;
> + char *columnfilter; /* the name of column filter program*/
> } printTableOpt;
>
> /*
>
> #! /usr/bin/perl
>
> ($type, $colnum, $org) = @ARGV;
>
> if ($type == 16) { # BOOLOID
> print ($org eq "t" ? "TRUEEEE" : "FAAALSE");
> } elsif ($type == 25 || $type == 705) { # TEXTOID || UNKNOWNOID
> print '$$'.$org.'$$';
> } else {
> print $type;
> }
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
>