From: | Shaun Clements <ShaunC(at)relyant(dot)co(dot)za> |
---|---|
To: | 'Sim Zacks' <sim(at)compulab(dot)co(dot)il> |
Cc: | "'pgsql-general(at)postgresql(dot)org'" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: pl sql to check if table of table_name exists |
Date: | 2005-03-10 13:10:10 |
Message-ID: | 100F78F2B203444BB161BBA7077FF6131CD89F@srldbexc003.relyant.co.za |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi Sim
Thanks for your input.
Kind Regards,
Shaun Clements
-----Original Message-----
From: Sim Zacks [mailto:sim(at)compulab(dot)co(dot)il]
Sent: 10 March 2005 02:47 PM
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] pl sql to check if table of table_name exists
I'm glad to hear you got it working.
In explanation to my response:
the pg_class internal table lists all the relationships in the database.
relkind='r' means that the relation you are looking for is a table
(relation), I believe that will also find views.
relname is the name of the object if your table is called parts
select * from pg_class where relkind='r' and relname='parts' will give you
the pg_class record for the table if it exists and nothing if it doesn't.
you could also do a select count(*) or select 1 In any case if there is a
resultset the table exists and if there is no resultset the the table does
not.
Using the pg_tables view is a better idea in any case, as it is cleaner.
Sim
"Shaun Clements" < ShaunC(at)relyant(dot)co(dot)za <mailto:ShaunC(at)relyant(dot)co(dot)za> >
wrote in message
news:100F78F2B203444BB161BBA7077FF6131CD89E(at)srldbexc003(dot)relyant(dot)co(dot)za
<news:100F78F2B203444BB161BBA7077FF6131CD89E(at)srldbexc003(dot)relyant(dot)co(dot)za> ...
Hi Sim
Thanks for your response. I had it working from a previous post by Adam
Tomjack.
<snip>
-- A list of tables:
SELECT schemaname, tablename FROM pg_tables;
-- Returns true if a table exists:
SELECT count(*)>0 FROM pg_tables
WHERE schemaname='...' AND tablename='...'
</snip>
Your response does not work for me. Perhaps you can explain the posted
command
<snip>
* from pg_class where relkind='r' and relname=your_tablename
</snip>
Kind Regards,
Shaun Clements
-----Original Message-----
From: Sim Zacks [mailto:sim(at)compulab(dot)co(dot)il]
Sent: 10 March 2005 01:24 PM
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] pl sql to check if table of table_name exists
i mean
select * from pg_class where relkind='r' and relname=your_tablename
"Sim Zacks" < sim(at)compulab(dot)co(dot)il <mailto:sim(at)compulab(dot)co(dot)il> > wrote in
message news:d0pamh$2l83$1(at)news(dot)hub(dot)org <news:d0pamh$2l83$1(at)news(dot)hub(dot)org>
...
select your_tablename from pg_class where relkind='r'
"Shaun Clements" < ShaunC(at)relyant(dot)co(dot)za <mailto:ShaunC(at)relyant(dot)co(dot)za> >
wrote in message
news:100F78F2B203444BB161BBA7077FF6131CD89C(at)srldbexc003(dot)relyant(dot)co(dot)za
<news:100F78F2B203444BB161BBA7077FF6131CD89C(at)srldbexc003(dot)relyant(dot)co(dot)za> ...
Hi
Hate to ask, but it isnt obvious to me from the documentation.
How do I perform a query in pgplsql, to check it a table exists of a
particular name.
Thanks in advance
Kind Regards,
Shaun Clements
From | Date | Subject | |
---|---|---|---|
Next Message | Mike Rylander | 2005-03-10 13:14:19 | Re: postgres 8 settings |
Previous Message | Sim Zacks | 2005-03-10 12:46:49 | Re: pl sql to check if table of table_name exists |