pgAdmin III macros: get table fields

When writing SQL statements of Stored Procedure code in pgAdmin, it is quite often quite handy to know the names and restrictions on the fields of some table.

Here is a simple macro that can be assigned to a key combination in pgAdmin -> Query Window -> Macros -> Manage macros...

select quote_ident(nspname) || '.' || quote_ident(relname) as table_name, 
       quote_ident(attname) as field_name, 
       format_type(atttypid,atttypmod) as field_type, 
       case when attnotnull then ' NOT NULL' else '' end as null_constraint,
       case when atthasdef then 'DEFAULT ' || 
                                ( select pg_get_expr(adbin, attrelid) 
                                    from pg_attrdef 
                                   where adrelid = attrelid and adnum = attnum )::text else '' 
       end as dafault_value,
       case when nullif(confrelid, 0) is not null 
            then confrelid::regclass::text || '( ' || 
                 array_to_string( ARRAY( select quote_ident( fa.attname ) 
                                           from pg_attribute as fa 
                                          where fa.attnum = ANY ( confkey ) 
                                            and fa.attrelid = confrelid
                                          order by fa.attnum 
                                        ), ',' 
                                 ) || ' )' 
            else '' end as references_to
  from pg_attribute 
       left outer join pg_constraint on conrelid = attrelid 
                                    and attnum = conkey[1] 
                                    and array_upper( conkey, 1 ) = 1,
       pg_class, 
       pg_namespace
 where pg_class.oid = attrelid
   and pg_namespace.oid = relnamespace
   and pg_class.oid = btrim( '$SELECTION$' )::regclass::oid
   and attnum > 0
   and not attisdropped
 order by attrelid, attnum;

Just select a table name, that you are interested in, and press the key binding, that you selected for that macros. pgAdmin will execute the query and show the list of all the columns of that table, including default values and foreign key references...

My preferred key binding is CTRL+1