Postgres Pro
Downloads
Home   >   mailing lists

Thread: parsing relname in pg_class

parsing relname in pg_class

From
"Johnson, Shaunn"
Date:
Howdy:  
  
Running Postgres 7.1.3 on RedHat 7.2 kernel 2.4.7 rel. 10.  
 
I'm writing a perl script where I only want to parse
out a list of tables from the database I'm connecting
to.
 
I'm doing something like this:
 
[snip]
 
select relname from pg_class
where relname not like '%_i%';
 
[/snip]
 
And that will get me halfway, but I still get a list
of tables with numbers attached and primary keys and the
like (for instance, what is pg_toast?)
 
Is there an easier way to get a list of the tables ONLY
with a select statement?  I don't think I can pass a "\d"
as a variable and get a return and I don't want to make
a case statement for every possibility.
 
PS: If this comes through as a MIME-type email, let me know -
I'm trying to stop that (using M$ Outlook '97).
Thanks!
 
-X
 

Re: parsing relname in pg_class

From
"Johnson, Shaunn"
Date:

--thanks for emailing:

--not sure what the 'r' in reltype
is, but i'm getting an error.  reltype
is defined as oid ... should there be
a letter next to it?

[snip error]

bcn=> select relname from pg_class
bcn-> where reltype = 'r'
bcn-> and relanem not like 'pg%';
ERROR:  oidin: error in "r": can't parse "r"

[/snip error]

--not sure, but i think i can use this:

[sql]

select relname from pg_class                                        where relname not like '%_i'
  and relname !~ '[$0-9]'
  and relname not like 'pg_%'
  order by 1;

[/sql]

--but i have to say, it looks like it's a
bit much (more work than needed) ...
oh well ... at least i'm learning ...

--thanks again!

-X

-----Original Message-----
From: Andrew Sullivan [mailto:andrew@libertyrms.info]

On Mon, Apr 01, 2002 at 04:29:34PM -0500, Johnson, Shaunn wrote:
> Howdy: 
>  
> Running Postgres 7.1.3 on RedHat 7.2 kernel 2.4.7 rel. 10. 
> 
> I'm writing a perl script where I only want to parse
> out a list of tables from the database I'm connecting
> to.
> 
> I'm doing something like this:
> 
> [snip]
> 
> select relname from pg_class
> where relname not like '%_i%';
> 
> [/snip]

What about

        SELECT relname FROM pg_class
        WHERE reltype = 'r'
        AND relname not like 'pg%'

?

[snip]

Re: parsing relname in pg_class

From
Andrew Sullivan
Date:
On Mon, Apr 01, 2002 at 04:51:52PM -0500, Johnson, Shaunn wrote:
> --thanks for emailing:
>
> --not sure what the 'r' in reltype

Sorry, that should have been 'relkind'.

A

----
Andrew Sullivan                               87 Mowat Avenue
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110

Re: parsing relname in pg_class

From
Neil Conway
Date:
On Mon, 1 Apr 2002 16:29:34 -0500
"Johnson, Shaunn" <SJohnson6@bcbsm.com> wrote:
> I'm writing a perl script where I only want to parse
> out a list of tables from the database I'm connecting
> to.

If it's Perl with DBD, $dbh->tables() will get it for you.

> Is there an easier way to get a list of the tables ONLY
> with a select statement?

select * from pg_tables;

should do the trick. If you're not interested in system catalogs, filter
out all the relations that start with "pg_".

>  I don't think I can pass a "\d"
> as a variable and get a return and I don't want to make
> a case statement for every possibility.

All these kinds of queries can be obtained by starting psql with "-E",
and then taking a look at how psql itself is getting the information.

Cheers,

Neil

--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Re: parsing relname in pg_class

From
Tom Lane
Date:
"Johnson, Shaunn" <SJohnson6@bcbsm.com> writes:
> I'm writing a perl script where I only want to parse
> out a list of tables from the database I'm connecting
> to.

> select relname from pg_class
> where relname not like '%_i%';

That doesn't seem very helpful; what's worse is it might hide tables you
want.  Something like

select relname from pg_class
where relname not like 'pg\\_%' and relkind = 'r';

would probably be a lot closer to what you want.

            regards, tom lane