Differences between version 8 and predecessor to the previous major change of PostgreSQLNotes.
Other diffs: Previous Revision, Previous Author, or view the Annotated Edit History
| Newer page: | version 8 | Last edited on Thursday, October 13, 2005 12:30:06 pm | by MattBrown | Revert |
| Older page: | version 7 | Last edited on Thursday, June 2, 2005 12:45:42 pm | by AristotlePagaltzis | Revert |
@@ -30,4 +30,22 @@
A default server will only listen for connections on a socket. If you want to connect to it via a host name then you need to start the server with the <tt>-i</tt> flag
You will also need to edit your <tt>pg_hba.conf</tt> file to set access permissions.
+
+!! Determining Table Primary Keys
+
+Within a procedure I was writing I needed a way to determine which fields of the table where the primary keys. The following views can help to provide this information.
+
+<verbatim>
+CREATE VIEW relid_pkeys AS SELECT indrelid AS relid, attname AS column_name
+ FROM pg_index, pg_class, pg_attribute WHERE indisprimary='t' AND
+ indexrelid=pg_class.oid AND pg_attribute.attrelid=pg_class.relfilenode;
+
+CREATE VIEW table_pkeys AS SELECT c.relname AS table_name, p.column_name FROM
+ relid_pkeys p, pg_class c WHERE p.relid=c.relfilenode AND c.relowner!=1 AND
+ c.relkind='r';
+</verbatim>
+
+Now you can go ''SELECT column_name FROM table_pkeys WHERE table_name='foo''' and get back a list of the columns in the table ''foo'' that are primary keys. No records will be returned if ''foo'' does not have a primary key defined.
+
+I added the restrictions on the ''relowner'' and ''relkind'' fields so that queries such as ''SELECT * FROM table_pkeys'' are a bit cleaner. The restrictions aim to filter the list so that it only contains user created tables. I chose the values based on trial and error, they work for me, but your milage may vary. If you are having problems simply remove those to restrictions.
