默认情况下在Postgres psql select语句中截断显示 [英] Truncating display by default in postgres psql select statements

问题描述

我有一个带有长文本列的表格。我希望能够选择所有列，但可以限制文本列而无需写每一列。

  select *来自资源； 
  

产生的输出太长而无法在psql中正确显示。我可以通过在长列上使用 substr（）或 left（）来显示一些内容，但是然后我

 从资源中选择id，left（data，50），file_format_version，...； 
  

有没有一种方法可以让psql在查询第一个时默认截断长列从资源中选择* ？

解决方案

在我会知道的psql选项中。

您可以使用

I have a table with a long text column. I'd like to be able to select all of the columns but limit the text column without needing to write every column.

select * from resources;

Produces an output that is too long to display correctly in psql. I can get something to show up by using substr() or left() on the long column, but then I need to specify each column.

select id, left(data, 50), file_format_version, ... from resources;

Is there a way that I can just get psql to truncate long columns by default when I query the first select * from resources?

解决方案

There is no way with built-in options of psql that I would know of.
You can achieve your goal with a function like @Drazen suggested - just much simpler:

CREATE OR REPLACE FUNCTION f_trunc_columns(_tbl anyelement, _len int = 25)
  RETURNS SETOF anyelement AS
$func$
DECLARE
   _typ  CONSTANT regtype[] := '{bpchar, varchar}';  -- types to shorten
BEGIN
   RETURN QUERY EXECUTE (
   SELECT format('SELECT %s FROM %s'
               , string_agg(CASE WHEN a.atttypid = 'text'::regtype  -- simple case text
                              THEN format('left(%I, %s)', a.attname, _len)
                            WHEN a.atttypid = ANY(_typ)             -- other short types
                              THEN format('left(%I::text, %s)::%s'
                                 , a.attname, _len, format_type(a.atttypid, a.atttypmod))
                            ELSE quote_ident(a.attname) END         -- rest
                          , ', ' ORDER BY a.attnum)
               , pg_typeof(_tbl))
   FROM   pg_attribute a
   WHERE  a.attrelid = pg_typeof(_tbl)::text::regclass
   AND    NOT a.attisdropped  -- no dropped (dead) columns
   AND    a.attnum > 0        -- no system columns
   );
END
$func$  LANGUAGE plpgsql;

Call examples:

SELECT * FROM f_trunc_columns(NULL::my_table);
SELECT * FROM f_trunc_columns(NULL::"MySchema"."My_funny_tbl", 11);

SQL Fiddle.

Notes

• Works for any table with columns of any data type.

• This builds and executes a query of the form:

  SELECT "FoO_id", left(c_text, 11), left(c_vc, 11)::character varying
  FROM   "FoO";
  

• It shortens only columns of chosen data types and leaves others alone. I included basic character types:
  bpchar is the internal name for character and all variants.
  varchar is the internal name for character varying and all variants.
  Extend to your needs.

• The function returns original column names and data types for all columns. I cast short columns to text before feeding to left(), which returns text, so text columns don't need another cast. All other shortened types need a cast back to the original type. Some types break if you truncate! So this does not work for all types.

• You can append LIMIT n to the function call, but the function can easily be extended with a built-in LIMIT - which is much more efficient for big tables, since the query inside the plpgsql function is planned independently.

• Performance is not much worse than a plain SELECT * FROM tbl - except for said LIMIT case or other cases where you nest the function. Set-returning PL/pgSQL functions are generally best not nested:

  • PostgreSQL Stored Procedure Performance

• I built in a default max. length of 25 characters, pass a custom length as 2nd parameter or adapt the default in the function header to your needs.

• This function is safe against possible SQL injection attacks via maliciously crafted identifiers.

Related answers with more explanation and links:

• Replace empty strings with null values
• Refactor a PL/pgSQL function to return the output of various SELECT queries
• Table name as a PostgreSQL function parameter
• Postgres data type cast
• Query the schema details of a table in PostgreSQL?
• How to check if a table exists in a given schema

pgAdmin

... has the feature you are asking for, btw (for all columns):

这篇关于默认情况下在Postgres psql select语句中截断显示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！