Not really psql, but everything is possible with code :)
This one turned out to be quite tricky, I'm not happy with the final solution as it is a bit clunky, but it gets the job done, still it should be treated as a proof of concept. There is always room to improve and streamline a bit :)
Anyway, as there is nothing built in and I don't like the idea of doing this outside of postgres, ie. PAGER output piped to awk than less and finally to you :) I went with functions.
Python is my weapon of choice most of the time, so I made a plpython function that does the truncating, and a plpgsql wrapper so that it can be nicely called, using all the lovely things SQL offers.
Lets start with a wrapper function:
create or replace function wrapper(t text, x anyelement, l integer)
returns setof anyelement as
$$
begin
-- call the logic bearing function
execute format($f$select truncate('%1$s', %2$s)$f$, t, l);
-- return results
return query execute format($f$select * from trunc_%1$s$f$, t);
end;
$$ language plpgsql;
As you can see it is declared with polymorphic input so that it can work on all tables you give it, or rather it can return the same type of table you feed to it, keeping all your constraints, indexes and so on (this is accomplished by the plpython function)... So without further ado, let's see it:
create or replace function truncate(tbl text, l integer) returns void as
$$
import arrow
import json
# Drops if needed and creates a table to hold your truncated results
plpy.execute('drop table if exists trunc_{0}'.format(tbl))
plpy.execute('create table trunc_{0} ( like {0} including defaults including constraints including indexes )'.format(tbl))
r = plpy.execute("select * from {}".format(tbl))
for i in xrange(r.nrows()):
# These two lists help us get the order of columns and values right
ins = []
cols = []
for x in r[i]:
if type(r[i][x]) is str:
'''
Two booleans below are used for type checking, I had an example table
with some json, timestamps and integers lying around so used that for
testing, this way we will truncate only text-like fields, but not json.
'''
ts = False
js = False
'''
Check if we can parse date or json, note that if you have a valid json
stored in a text or varchar field it will still get marked as json, by
digging in the information_schema you could easily add better type
checking here.
'''
try:
arrow.get(r[i][x])
ts = True
except (arrow.parser.ParserError, UnicodeDecodeError):
pass
try:
json.loads(r[i][x])
js = True
except ValueError:
pass
# If it is a string and its not json or timestamp lets truncate it
# whatever you specify as the last argument in the call, `l`
if not ts and not js:
r[i][x] = r[i][x][:l]
# Additional check for nulls and ints, and appropriate appends
if r[i][x] is None:
ins.append("null")
elif r[i][x] is int:
ins.append(r[i[x]])
```
Finally we can append our values to insert, this is done inefficiently as
each row will be inserted individually, again treat this as a POC, better
would be to first form a list of all inserts and then fire them in one statement.
```
else:
ins.append("'{}'".format(r[i][x]))
cols.append(x)
q = 'insert into trunc_{0}({2}) values({1})'.format(tbl, ','.join(ins), ','.join(cols))
plpy.execute(q)
$$ language plpythonu;
Provided I managed to format this correctly you should be able to call this by running:
select * from wrapper(resources, null::resources, 50);
Again the clunkiness shows it ugly face, so you give the table name, table column types so it knows what to return and character limit for truncation, you should be able to use WHERE, GROUP BY and similar without any problems.
Obvious problem is the performance as you will essentially be reinserting your entire table and this could be problematic, but that at least is easily solvable.
As an afterthought, in case you are unfamiliar with plpython, you enable it by running create extension plpythonu from within psql. json module comes built in to python, whereas arrow can be installed by running pip install arrow from your shell of choice, provided pip and python are in order, in case they are not Google is your friend ;)
Hope this gets you at least part of the way to where you want to be :)
psql– Oyster\x– Gyronny