Site Navigation
|
|
PGTS Humble BlogThread: Tips/Tricks For Programming etc |
|
 |
Gerry Patterson. The world's most humble blogger |
| Edited and endorsed by PGTS, Home of the world's most humble blogger | |
| | |
Query Column Names In A Postgresql Table |
|
Chronogical Blog Entries:
|
|
| | |
Date: Sun, 29 Jan 2012 16:59:19 +1100Postgres 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; |