Subject: Re: [SQL] system tables inquiry & db Link inquiry
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Karthikeyan Sundaram <skarthi98@hotmail.com>
Cc: pgsql-admin@postgresql.org, pgsql-sql@postgresql.org
In-Reply-To: <BAY131-F14526BA01BB66AA5D4DB75B0810@phx.gbl>
References: <BAY131-F14526BA01BB66AA5D4DB75B0810@phx.gbl>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1172687717.20651.156.camel@state.g2switchworks.com>
Mime-Version: 1.0
Date: Wed, 28 Feb 2007 12:35:17 -0600

On Wed, 2007-02-28 at 12:19, Karthikeyan Sundaram wrote:
> Hi,
> 
>     We are using Postgres 8.1.0

Stop.  Do not pass go, do not collect $200.  Update your postgresql
installation now to 8.1.8.  There were a lot of bugs fixed between 8.1.0
and 8.1.8.

After that...

>   Question No 1:
>   =========
>    There are lots of system tables that are available in postgres. For 
> example pg_tables will have all the information about the tables that are 
> present in a given schema.  pg_views will have all the information about the 
> views for the given schema.
> 
>     I want to find all the sequences.  What is the system tables that have 
> the information about all the sequences?

In the future, you can use this trick to find those things out:

psql -E template1
\?   (command to list all the backslash commands from psql)
\ds  (<- command for listing sequences from psql)
Tada, you now get the sql that psql used to make that display.

For 8.2.3 that's:

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",
  r.rolname as "Owner"
FROM pg_catalog.pg_class c
     JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('S','')
      AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

>    Question No 2:
>    =========
> 
>      I have 2 postgres instance located in two different servers.   I want 
> to create a DBlink (like in Oracle) between these 2.  What are the steps 
> involved to create this.
> 
>    Any examples?  Please advise.

I'm pretty sure there's some examples in the contrib/dblink/doc
directory in the source file to do that.  It's pretty simple, I had it
working about 5 minutes after installing dblink.