PGTS PGTS Pty. Ltd.   ACN: 007 008 568

point Site Navigation

point Other Blog Threads



  Valid HTML 4.01 Transitional

   Brilliant XED lighting!
   Australian XED Lighting

   AXL

   Australia/NZ Distributor of
   Reming XED Lighting Products






PGTS Humble Blog

Thread: Tips/Tricks For Programming etc

GP JPG
And if your head explodes with dark forebodings too -- I'll see you on the dark side of the moon!

Query Column Names In A Postgresql Table


Chronogical Blog Entries:



Date: Sun, 29 Jan 2012 16:59:19 +1100

Postgres is a versatile and powerful RDBMS. But sometimes obtaining information from the pg_catalog can be complex and involved. Getting the simplest information from the catalog can take many hours of research. And by the time you realise you need to get some information again, you will have forgotten what you did last time and have to do the research all over again.

For this reason, I decided to create my own mini catalog which would enable me to carry out simple queries like what columns are in which tables and is there a NOT NULL constraint on them, and is there a sequence linked to the primary key (and if so what is it called?) etc.

The SQL code to create the view is shown at the bottom of this blog. If you just want the code, skip to the end of this blog entry by clicking here. Otherwise, if you want to read about how it was constructed, continue reading ...

There are many uses you could probably imagine for such a catalog ... For example writing a bunch of generic scripts for insert, update, delete etc ... Attaching field specific validation procedures to such scripts etc. If you are an absolute postgres SQL guru you can write them as SQL triggers. However I find it easier (and more transparent) to put validation procedures in perl code, since I can intercept the errors before they go the back end and generate a fatal error.

As ever when I have a problem like this I tried to tackle it with perl. The usual way to query the catalog is to use the built in commands in the psql command:

-- display a list of tables:
\dt
 Schema |       Name       | Type  | Owner
--------+------------------+-------+--------
 public | foo              | table | apache
 public | foo_transactions | table | apache
 public | foo_bar_blah     | table | apache


-- describe the table foo
\d foo
                                      Table "public.foo"
    Column    |            Type             |                    Modifiers
--------------+-----------------------------+--------------------------------------------------
 id           | integer                     | not null default nextval('foo_id_seq'::regclass)
 category     | character varying(10)       | not null
 description  | character varying(80)       |
 invoice_num  | integer                     |
 date_created | timestamp without time zone |

I tried using the psql command to get the data from postgres. The following code demonstrates how this might work.

#!/usr/bin/perl
use strict;
use warnings;
my $db_name = "foo";
my $tmpfile = "/tmp/mkcat.$$";
system "psql -c '\\dt' $db_name > $tmpfile";
open TMP,$tmpfile || die "Cannot open $tmpfile - $!";
while (<TMP>) {
	s/\s+$//;
	my @c = split (/\|/,$_);
	next unless (@c == 4 && $c[2] =~/^\s*table\s*$/i);
	my $table_name = $c[1];
	$table_name =~ s/\s+$//;
	$table_name =~ s/^\s+//;
	print "$table_name\n";
}
close TMP;
unlink $tmpfile;

The above snippet is trivial but it does constitute a proof of concept. Reading the values output from the "\d" meta-command to STDOUT would follow a similar process.

However the problem with the above approach is that whenever you alter tables, columns (especially names) or constraints, you have to re-generate your mini catalog. And if you make an alteration and forget to re-generate, your carefully crafted validation (or whatever) scripts will fail.

But there is a more elegant and flexible way to get information regarding columns and tables. This is by creating a view into the catalog which shows the table names, columns and properties.

Postgres is an open source product and it is relatively easy to get information about the back end. There is a way to investigate what SQL commands are sent to the back end when you enter meta commands. This is achieved by starting the command line utility with the -E option. In other words you enter the following command:

psql -E foo

Now when you enter commands you will also see the SQL code that the psql command generates. It should look something like this:

/* SQL Generated for:    \dt

** ********* QUERY ********** */
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table'
                 WHEN 'v' THEN 'view'
		 WHEN 'i' THEN 'index'
		 WHEN 'S' THEN 'sequence'
		 WHEN 's' THEN 'special'
  END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
/* ************************** */


/* SQL generated for: \d foo 

** ********* QUERY ********** */
SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(foo)$'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;

/* ************************** */

/* ********* QUERY ********** */
SELECT c.relchecks,
       c.relkind,
       c.relhasindex,
       c.relhasrules,
       c.relhastriggers,
       c.relhasoids, '', c.reltablespace
FROM pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
WHERE c.oid = '43404'

/* ************************** */

/* ********* QUERY ********** */
SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
   FROM pg_catalog.pg_attrdef d
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
  a.attnotnull, a.attnum
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '43404' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
/* ************************** */

/* ********* QUERY ********** */
SELECT c.oid::pg_catalog.regclass
FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
WHERE c.oid=i.inhparent AND i.inhrelid = '43404' ORDER BY inhseqno
/* ************************** */


SQL Code To Create A Catalog View Of Public Tables In A Postgres DB.

After a little scrutiny of the above queries and some copy and pasting you should be able to put them all together into a single view something like below:

/* You'll need to drop it if you have run this code previously  */
DROP VIEW foo_catalog;

CREATE VIEW foo_catalog AS (
	SELECT c.relname AS tablename,
	  a.attname AS colname,
	  pg_catalog.format_type(a.atttypid, a.atttypmod),
	  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
	   FROM pg_catalog.pg_attrdef d
	   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) as Modifier,
	  a.attnotnull AS notnull
	FROM pg_catalog.pg_attribute a,
	     pg_catalog.pg_class c
	     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
	WHERE a.attrelid = c.oid
	  AND a.attnum > 0 AND NOT a.attisdropped
	  AND c.relkind IN ('r','')
	  AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
	  AND pg_catalog.pg_table_is_visible(c.oid)
	  AND n.nspname = 'public'
	ORDER BY c.relname, a.attnum
);

Now we have a mini catalog view called foo_catalog (substitute your own name). After you create it, you can inspect it by entering the command "\d foo_catalog". You will see something like this.

    View "public.foo_catalog"
   Column    |  Type   | Modifiers
-------------+---------+-----------
 tablename   | name    |
 colname     | name    |
 format_type | text    |
 modifier    | text    |
 notnull     | boolean |

There are lots of ways you might put your new view to work ... For example to find out the tablename and column names of all varchar fields that have been defined with a length greater than 75, you could use the query below:

SELECT * FROM (
    SELECT tablename,
           colname,
           cast( trim (trailing ')' from substr(format_type,19)) as int ) as len
    FROM foo_catalog
    WHERE format_type like 'character varying%') as t
WHERE len > 75;

Other Blog Posts In This Thread:

Copyright     2012, Gerry Patterson. All Rights Reserved.