One of the most common questions is “how do I get select * from table, but without one of the column".
Short answer is of course – name your columns, instead of using *. Or use a view.
But I decided to take a look at the problem.
I haven't found a way to hide column – although it might be possible to do it, but would require a bit heavier wizardry. But, I have found a way to hide values in a column.
For example – let's assume that I'd like to view pg_proc table, but without prosrc column which is usually too big.
So, I wrote this function:
CREATE OR REPLACE FUNCTION hide_column( in_table regclass, VARIADIC in_column TEXT[] ) RETURNS setof RECORD AS $$ DECLARE v_sql TEXT; v_table RECORD; v_table_name TEXT; temprec RECORD; BEGIN SELECT c.relname, n.nspname INTO v_table FROM pg_class c join pg_namespace n ON c.relnamespace = n.oid WHERE c.oid = in_table; FOR temprec IN SELECT a.attname, t.typname FROM pg_attribute a join pg_type t ON a.atttypid = t.oid WHERE a.attrelid = in_table AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum LOOP v_sql := COALESCE( v_sql || ', ', 'SELECT ' ); IF temprec.attname = ANY( in_column ) THEN v_sql := v_sql || 'NULL::' || quote_ident( temprec.typname ) || ' as ' || quote_ident( temprec.attname ); ELSE v_sql := v_sql || quote_ident( temprec.attname ); END IF; END LOOP; v_table_name := quote_ident( v_table.nspname ) || '.' || quote_ident( v_table.relname ); v_sql := v_sql || ' FROM ' || v_table_name; v_sql := 'SELECT x::' || v_table_name || ' as rec FROM (' || v_sql || ') as x'; RETURN QUERY EXECUTE v_sql; END; $$ language plpgsql;
No, I will not explain how it works – mostly because I don't really want anybody to use it. But if you have this function you can:
SELECT (rec).* FROM hide_column('pg_proc', 'prosrc', 'probin', 'proowner', 'proargnames' ) AS q (rec pg_proc);
Of course this is still to wide to show in here, so let's instead test it on some smaller table:
CREATE TABLE test_it ( a TEXT, b TEXT, c TEXT, d TEXT, e TEXT ); INSERT INTO test_it SELECT 'column a : ' || i, 'column b : ' || i, 'column c : ' || i, 'column d : ' || i, 'column e : ' || i FROM generate_series(1,3) i;
It looks like this:
SELECT * FROM test_it; a | b | c | d | e --------------+--------------+--------------+--------------+-------------- COLUMN a : 1 | COLUMN b : 1 | COLUMN c : 1 | COLUMN d : 1 | COLUMN e : 1 COLUMN a : 2 | COLUMN b : 2 | COLUMN c : 2 | COLUMN d : 2 | COLUMN e : 2 COLUMN a : 3 | COLUMN b : 3 | COLUMN c : 3 | COLUMN d : 3 | COLUMN e : 3 (3 ROWS)
And now let's use my magic function:
# SELECT (rec).* FROM hide_column('test_it', 'b', 'd') AS i(rec test_it); a | b | c | d | e --------------+---+--------------+---+-------------- COLUMN a : 1 | | COLUMN c : 1 | | COLUMN e : 1 COLUMN a : 2 | | COLUMN c : 2 | | COLUMN e : 2 COLUMN a : 3 | | COLUMN c : 3 | | COLUMN e : 3 (3 ROWS)
Of course it doesn't look nice (invocation of the function), but that's not the point. The point is that it's possible.
It would be better if normal SELECT syntax could just say what you don’t want; eg:
select * except b, d from foo
Surely Postgres could support such a syntax extension, even if the SQL standard had allegedly refused to.
On the other side I think it would be better to remove “select *” from the SQL standard. 😀
It is nice example of hard coded idiocy :). You remove useless reading, but you store complete result in MEMORY – so it is useful only for small sets (10000 rows).
to Duncan: Your idea is wrong. Wrong designers have to suffer when he designs too wide tables.
@MIGUEL A.
Yes, having to say “select * from foo” is very stupid; one should just be able to say “foo” to mean “just get foo”. And in Muldis D that’s just how it works. But that’s an old SQL problem.
@PAVEL STEHULE
The point of that one should be able to say something like “select all but a, b from baz” is simply a matter of language completeness.
When selecting rows, you can put a “not” in front of any filter expression in a where clause to get the complement of rows.
Similarly, one should be able to effectively put “not” in the select list to get the complement of the named rows.
Arguing against this being useful is like arguing against “not” being useful.
In my second to last sentence I meant to say “complement of the named columns”.
@Darren Duncan:
It could be nice addition – especially for people who design too wide tables, but it’s not yet here, so there is no point in arguing over it.
@Pavel:
Thank you. 🙂
Well, sure it’s idiotic. Of course you could further modify the function to allow addition of custom where/order/limit/… clauses 🙂
And no, I will not do it.
Depesz: The are two problems: a) RETURN QUERY implementation (use only memory), b) planner – plpgsql is black box for planner, c) application stability – using a explicit list of columns add some order to returned data. Without it, the column’s order is “random” (you can change it via ALTER TABLE, CREATE TABLE, ..). What I know, the advice of not using * is based first on source code maintainability. With PostgreSQL is more important, using TOAST columns can slowness query.
Neat for tables with few rows and a couple of columns you want to exclude, which sadly is a bit to specific for me to have any use for me. I have a 200K row table with a column with ~20K of binary in each row in it that I often want to leave out when peeking at it. And I’m happy you didn’t explain that thing, if I ever catch anyone using something like this in production I’ll throw them out the nearest window. Don’t worry, I work on the ground floor. 🙂
What I’d rather want is an option for psql to truncate or replace wide columns of a type without size restriction with their size or a hash, not to mention something like that for DML statement logging in PostgreSQL. Oh well.
@Thor
Oracle’s SQL*Plus has similar functionality to what you describe in your last sentence. Probably hackable into psql.
@depesz much nicer than similar function I wrote on 7.3 once 😉
@darren in sql spec, you can do “table foo”, which will return all of foo. better than select * I guess.
@pavel I don’t understand how you can miss the point so much when depesz gives a perfect example. this really isnt about too wide table definitions, its often more about 1 column which has large text data in it which makes result display harder. pg_proc.prosrc is great example.
btw, i’ve often though select *, !bar from baz would be simple enough syntax to remove unwanted columns, but sadly we’re probably unlikey to see anything until sql std defines it.
This function would actually be extremely useful for PostGIS users. Surely every developer runs numerous SELECT * queries throughout the day to view sample data from a table. Unfortunately, geometry columns have an extremely wide internal representation. This doesn’t present an issue when running a SELECT * in any good graphical SQL client as column widths can be set and long data values are not wrapped. However, when run in psql, the value in the geometry field will wrap and generally fill up the entire terminal window. This type of function would be quite useful to me for quickly viewing data in psql for spatially enabled tables minus the geometry field.