To conclude (to the best of my current knowledge), here is a plpgsql
code sample based on what was said here:
EXECUTE
'SELECT '
|| (SELECT array_to_string( ARRAY(
SELECT column_name::text
FROM information_schema.columns
WHERE table_schema = 'my_schema'
AND table_name = 'my_relation'
AND column_name <> 'bad_field'), ', '))
|| ' FROM my_schema.my_relation';
Comments:
- Don't forget to additionally specify the schema - a relation of the
same name may be present in multiple schemas!
- The function array_to_string is the easiest way to form the string
we need.
Actually this is awkwardly slow, as information_schema.columns queries
a ton of other information from various system relations (just have a
look at "explain analyze <query>"!).
So, if you have the necessary privileges to access pg_catalog and
speed is important, this generates the code multiple times faster:
EXECUTE
'SELECT '
|| (SELECT array_to_string(ARRAY(
SELECT a.attname
FROM pg_class c, pg_namespace nc, pg_attribute a
WHERE c.relname = 'v_event'
AND c.relnamespace = nc.oid
AND nc.nspname = 'stdat'
AND a.attrelid = c.oid
AND a.attname <> 'log_up'), ', '))
|| ' FROM stdat.v_event';
Regards
Erwin
