Accesing PostgreSQL objects information using SQL
July 2017
Sun Mon Tue Wed Thu Fri Sat
            1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31          
About
This site is an effort to share some of the base knowledge I have gathered through all this years working with Linux, FreeBSD, OpenBSD, Python or Zope, among others. So, take a look around and I hope you will find the contents useful.
Recent Entries
Recent Comments
Recent Trackbacks
Categories
OpenBSD (9 items)
BSD (0 items)
FreeBSD (19 items)
Linux (3 items)
Security (3 items)
Python (22 items)
Zope (13 items)
Daily (144 items)
e-shell (9 items)
Hacks (14 items)
PostgreSQL (3 items)
OSX (8 items)
Nintendo DS (0 items)
enlightenment (0 items)
Apache (3 items)
Nintendo Wii (1 items)
Django (24 items)
Music (12 items)
Plone (7 items)
Varnish (0 items)
Lugo (2 items)
Sendmail (0 items)
europython (7 items)
Cherokee (1 items)
self (1 items)
Nature (1 items)
Hiking (0 items)
uwsgi (0 items)
nginx (0 items)
cycling (9 items)
Networking (1 items)
DNS (0 items)
Archives

Syndicate this site (XML)

RSS/RDF 0.91

06 marzo
2009

Accesing PostgreSQL objects information using SQL

... translating the usual psql shortcuts (\dt, \d table_name, etc)

Yesterday, one of my colleagues at work asked me an interesting question:

  • hey!, how could I know the number of fields a view has, in PostgreSQL?

The answer was pretty easy:

  • sure, just go into psql and write \d view_name.

That \d thing is a shortcut you can use to get information from any object located inside the db, while in psql (for those of you more familiar with MySQL, it is like the describe table_name command). Let me show you an example of \d table_name:

pastesite=# \d paste_source
                                    Tabla «public.paste_source»
   Columna   |           Tipo           |                       Modificadores
-------------+--------------------------+-----------------------------------------------------------
 id          | integer                  | not null default nextval('paste_source_id_seq'::regclass)
 lang_id     | integer                  | not null
 src         | text                     | not null
 name        | character varying(100)   | not null
 author      | character varying(150)   | not null
 pub_date    | timestamp with time zone | not null
 description | text                     | not null
Índices:
    «paste_source_pkey» PRIMARY KEY, btree (id)
   «paste_source_lang_id» btree (lang_id)
Restricciones de llave foránea:
    «paste_source_lang_id_fkey» FOREIGN KEY (lang_id) REFERENCES paste_lang(id) DEFERRABLE INITIALLY DEFERRED

pastesite=#

But I was assuming that he needed to know such information by himself, which was wrong. He needed to get the number of fields that view has, within some python code (aka not using psql).

We searched a little bit and finally we found how to do it.

When you go into psql, there is an option -E, which means (from the psql man page):

-E

--echo-hidden
       Echo the actual queries generated by \d and other backslash com-
       mands.  You  can  use  this to study psql's internal operations.
       This is equivalent to  setting  the  variable  ECHO_HIDDEN  from
       within psql.

So, calling psql with the -E option will means we will be able to see the SQL sentence PostgreSQL is using behind the scenes to get such information, in the previous example something like:

pastesite=# \d paste_source
********* 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 ~ '^(paste_source)$'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
**************************

********* QUERY **********
SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules,
relhasoids , reltablespace
FROM pg_catalog.pg_class WHERE oid = '168553'
**************************

********* 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 = '168553' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
**************************

********* QUERY **********
SELECT t.tgname, pg_catalog.pg_get_triggerdef(t.oid)
FROM pg_catalog.pg_trigger t
WHERE t.tgrelid = '168553' AND (not tgisconstraint  OR NOT EXISTS  (SELECT 1 FROM pg_catalog.pg_depend d    JOIN pg_catalog.pg_constraint c ON (d.refclassid = c.tableoid AND d.refobjid = c.oid)    WHERE d.classid = t.tableoid AND d.objid = t.oid AND d.deptype = 'i' AND c.contype = 'f'))   ORDER BY 1
**************************

********* QUERY **********
SELECT conname,
  pg_catalog.pg_get_constraintdef(oid, true) as condef
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = '168553' AND r.contype = 'f' ORDER BY 1
**************************

********* QUERY **********
SELECT c.relname FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '168553' ORDER BY inhseqno ASC
**************************

                                    Tabla «public.paste_source»
   Columna   |           Tipo           |                       Modificadores
-------------+--------------------------+-----------------------------------------------------------
 id          | integer                  | not null default nextval('paste_source_id_seq'::regclass)
 lang_id     | integer                  | not null src         | text                     | not null
 name        | character varying(100)   | not null
 author      | character varying(150)   | not null
 pub_date    | timestamp with time zone | not null
 description | text                     | not null
Índices:
    «paste_source_pkey» PRIMARY KEY, btree (id)
    «paste_source_lang_id» btree (lang_id)
Restricciones de llave foránea:
    «paste_source_lang_id_fkey» FOREIGN KEY (lang_id) REFERENCES paste_lang(id) DEFERRABLE INITIALLY DEFERRED

pastesite=#

Seems pretty complex, but just doing some digging you can get to something like:

pastesite=# select attname, attnum from pg_attribute, pg_class where attrelid = pg_class.oid and relname ='paste_source';
   attname   | attnum
-------------+--------
 tableoid    |     -7
 cmax        |     -6
 xmax        |     -5
 cmin        |     -4
 xmin        |     -3
 ctid        |     -1
 id          |      1
 lang_id     |      2
 src         |      3
 name        |      4
 author      |      5
 pub_date    |      6
 description |      7
(13 filas)

pastesite=#

Which is pretty much like the needs from my colleague. He could parse the results from such query to gather the information he needed or he could work a little bit more to get a more-refined sql sentence.

NOTE: My colleague needed a view information, I'm using a table as the example for this post, but the procedure would be pretty similar for a view, a sequence or any other type of object available in our db.

Posted by wu at 08:31 | Comments (0) | Trackbacks (0)
<< Aupa! | Main | quotes by santi >>
Comments
There are no comments.
Trackbacks
Please send trackback to:http://blog.e-shell.org/146/tbping
There are no trackbacks.
Post a comment