A psql hack for select * except some columns

Sometimes when writing queries that start with SELECT * FROM ..., we’d wish to exclude a few columns from the results. Unfortunately, the SQL syntax does not provide a solution to that need ¹; instead we need to list all the columns to include. Recently, a sub-discussion in the pgsql-general mailing-list was suggesting that for some use cases, a client-side solution might be good enough, if only the client was able to convert that * into actual column names.

That got me thinking: doesn’t psql already have the parts to do that?

• it can query the structure of a result set with \gdesc since version 11, from a query, without executing it (the query gets only prepared). The result is a list of columns with their names and types.
• it can output that structure in CSV since version 12 with \pset format csv.
• that output can go into a file if \o filename is given before calling \gdesc.
• it can invoke a configurable editor with \e. If that editor performs the replacement of select * by select <post-processed list obtained from \gdesc> then we’re close to the goal.
• finally, the editor can be passed out-of-buffer context in its environment through \setenv

But, can we compose these parts into some kind of command, to automate this entirely? As it happens, it’s possible. Let’s see how…

psqlrc

First, we need these two declarations in the .psqlrc file.

-- custom front-end to the normal editor
\setenv PSQL_EDITOR ~/bin/psql-edit-replace.sh

-- declare an :expand variable to use as a macro (sort of)
-- must be kept as a single long line

\set expand ' \\set _tmpstruct `tempfile` \\setenv PSQL_TMP_STRUCT :_tmpstruct \\set QUIET on \\pset format csv \\x off \\pset tuples_only off \\o :_tmpstruct \\gdesc \\o \\pset format aligned \\set QUIET off \\e \\unset _tmpstruct'

Now when we type :expand and the return key in psql at the end of a valid query (instead of the final semi-colon ; or the \g meta-command), it will run this long string of meta-commands that essentially describe the query into a separate temporary file, and launch our custom editor pointed to by PSQL_EDITOR.

There are some side-effects to that invocation: it resets the following parameters to their default values: QUIET=off, format=aligned, tuples_only=off, expanded=off. This is not easy to avoid, so I haven’t insisted on saving and restoring these parameters, although I think it could be done with some additional work.

Custom front-end editor

My custom editor is a bash script (the source code is at the end of the post) acting as front-end to the normal editor. Before passing the query to it, it will check for a file pointed to by $PSQL_TMP_STRUCT, and for the presence of specific markers in the first line of the query buffer. The markers I’ve choosen are:

• * /*expand*/ : this text will be replaced by the list of columns of the query.
• * /*except:col1,col2,...*/: this text will be replaced by the list of columns of the query, except those listed.
• * /*except-type:bytea,jsonb,...*/ : this text will be replaced by the list of columns of the query, except those of the types listed.

If the structure file is present and one of these markers is found, an embedded Perl script goes through the query buffer to rewrite it in-place. Then it calls the “normal” editor, pointed to by $EDITOR (or vi by default). Of course, once in the real editor, any additional edit can normally be done, as if the injected list of columns had been originally typed inside psql.

Examples

Let’s create a couple of tables and try each form of invocation.

CREATE TABLE users(user_id uuid PRIMARY KEY, name text, picture bytea);
CREATE TABLE posts(post_id uuid PRIMARY KEY, user_id uuid REFERENCES users(user_id), subject text, contents text, created_at timestamptz);

Full expansion

Invocation in psql:

SELECT * /*expand*/ FROM users JOIN posts USING(user_id) :expand

Obtained in the editor:

SELECT "user_id",
        "name",
        "picture",
        "post_id",
        "subject",
        "contents",
        "created_at"
 FROM users JOIN posts USING(user_id)

Remove some columns by name

Invocation in psql:

SELECT * /*except:picture,contents*/ FROM users JOIN posts USING(user_id) :expand

Result:

SELECT "user_id",
        "name",
        "post_id",
        "subject",
        "created_at"
 FROM users JOIN posts USING(user_id)

Remove some columns by type

Invocation in psql:

SELECT * /*except-type:uuid,bytea*/ FROM users JOIN posts USING(user_id) :expand

Result:

SELECT "name",
        "subject",
        "contents",
        "created_at"
 FROM users JOIN posts USING(user_id)

Front-end implementation

Here’s the source code for a bash+perl editor’s front-end doing what’s described above. Note that the Perl part is run only when :expand has been used in psql, so that it minimizes both the overhead and the risk of messing up the buffer when invoked normally with \e or \ef.

The files can be found on github. Feel free to open issues over there if you want to talk about the code.

#!/bin/bash

# A custom editor for psql that pre-processes the query string
# to replace "* /* special comment */" with a list of columns.
# The columns are passed in a temporary file pointed to by
# the PSQL_TMP_STRUCT environment variable.

# Set up PSQL_EDITOR to point to that script.
# See the macro invocation in psqlrc-for-edit-replace

read -r line1 < "$1"
rx='\*\s*/\*(expand|except:|except-type:).*\*/'
if [[ $line1 =~ $rx && -r "$PSQL_TMP_STRUCT" ]]; then
  perl - $1 "$PSQL_TMP_STRUCT" << "EOP"
require 5.014;
use Text::CSV qw(csv);

sub expand {
  # filter and format the list of columns
  my ($cols,$filter_type,$filter) = @_;
  # filter_type => undef:none, 0:by name, 1: by type
  my $qi = 1; # quote the columns (for case sensitive names and reserved keywords)
  if (defined $filter_type) {
    my @xcols = split /,/, $filter;	# list of arguments inside the comment
    my %xhcols = map { $_=>1 } @xcols;
    $cols = [ grep { !defined $xhcols{$_->[$filter_type]} } @{$cols} ];
  }
  return join ",\n\t", (map { $qi?('"' . $_->[0]=~ s/"/""/r . '"') : $_->[0]}
  	      	        @{$cols});
}

my $cols = csv(in=>$ARGV[1], headers=>"skip", binary=>1);
open(my $fi, "<", $ARGV[0]) or die "cannot open $ARGV[0]: $!";
my $lines = <$fi>;   # 1st line of query

my $rx = qr{^(.*)\*\s*/\*expand\*/(.*)$};
if ($lines =~ $rx) {
  # expand to all columns
  $lines = "$1" . expand($cols, undef, undef) . "\n$2";
}
else {
  $rx = qr{^(.*)\*\s*/\*except:(.*)\*/(.*)$};
  if ($lines =~ $rx) {
    # expand to all columns except those listed
    $lines = "$1" . expand($cols, 0, $2) . "\n$3";
  }
  else {
    $rx = qr{^(.*)\*\s*/\*except-type:(.*)\*/(.*)$};
    if ($lines =~ $rx) {
	  # expand to all column except for the types listed
      $lines = "$1" . expand($cols, 1, $2) . "\n$3";
    }
  }
}
# copy the rest of the lines
do {
  $lines .= $_;
} while (<$fi>);
close $fi;
# overwrite the file with the new query
open (my $fo, ">", $ARGV[0]) or die "cannot open $ARGV[0] for writing: $!";
print $fo $lines;
close $fo;
EOP

  # When the replacement in the query buffer occurred, we could
  # return into psql at this point rather than going into the actual
  # editor.
  # But before version 13, psql won't display the modified
  # query when returning at this point, so it might seem opaque.
  # Let's always call the actual editor, but you may uncomment
  # the line below to skip it.

  # rm -f "$PSQL_TMP_STRUCT" ; exit
fi
rm -f "$PSQL_TMP_STRUCT"
${EDITOR:-vi} $*