Re: pl sql to check if table of table_name exists

Shaun Clements wrote:

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

-- 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='...'

-- Here's an untested function:
CREATE OR REPLACE FUNCTION table_exists(TEXT, TEXT)
RETURNS BOOLEAN AS '
DECLARE
r RECORD;
BEGIN
SELECT INTO r count(*)>0 AS exists
FROM pg_tables WHERE schemaname='$1' AND tablename='$2'
RETURN r.exists;
END;
' LANGUAGE plpgsql STABLE;

Check out http://www.postgresql.org/docs/8.0/static/catalogs.html for
more info.

Adam

Much appreciated.
Thanks

Kind Regards,
Shaun Clements

-----Original Message-----
From: Adam Tomjack [mailto:adamtj@adamtj.org]
Sent: 10 March 2005 11:04 AM
To: Shaun Clements
Cc: postgresql
Subject: Re: [GENERAL] pl sql to check if table of table_name exists

Shaun Clements wrote:

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

-- 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='...'

-- Here's an untested function:
CREATE OR REPLACE FUNCTION table_exists(TEXT, TEXT)
RETURNS BOOLEAN AS '
DECLARE
r RECORD;
BEGIN
SELECT INTO r count(*)>0 AS exists
FROM pg_tables WHERE schemaname='$1' AND tablename='$2'
RETURN r.exists;
END;
' LANGUAGE plpgsql STABLE;

Check out http://www.postgresql.org/docs/8.0/static/catalogs.html for
more info.

Adam

Shaun Clements wrote:

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.

Check the manual. There are two ways to d othis. You could query the
data catalogs directly (something like count(*) from pg_class where
relname = $1), but this is not preferred because you have the
possibilities that the data catalogs will be changed in the future.

The better way to do this is to query the information schema. I forget
the table name but it may be something like (select count(*) from
information_schema.tables where table_name = $1). the structure of the
information schema is defined in the SQL standards and will be stable
between versions.

Best Wishes,
Chris Travers
Metatron Technology COnsulting

RE: [GENERAL] pl sql to check if table of table_name existsselect your_tablename from pg_class where relkind='r'
"Shaun Clements" <ShaunC@relyant.co.za> wrote in message news:100F78F2B203444BB161BBA7077FF6131CD89C@srldbexc003.relyant.co.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

RE: [GENERAL] pl sql to check if table of table_name existsi mean
select * from pg_class where relkind='r' and relname=your_tablename
"Sim Zacks" <sim@compulab.co.il> wrote in message news:d0pamh$2l83$1@news.hub.org...
select your_tablename from pg_class where relkind='r'
"Shaun Clements" <ShaunC@relyant.co.za> wrote in message news:100F78F2B203444BB161BBA7077FF6131CD89C@srldbexc003.relyant.co.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

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@compulab.co.il]
Sent: 10 March 2005 01:24 PM
To: pgsql-general@postgresql.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@compulab.co.il <mailto:sim@compulab.co.il> > wrote in
message news:d0pamh$2l83$1@news.hub.org <news:d0pamh$2l83$1@news.hub.org>
...
select your_tablename from pg_class where relkind='r'

"Shaun Clements" < ShaunC@relyant.co.za <mailto:ShaunC@relyant.co.za> >
wrote in message
news:100F78F2B203444BB161BBA7077FF6131CD89C@srldbexc003.relyant.co.za
<news:100F78F2B203444BB161BBA7077FF6131CD89C@srldbexc003.relyant.co.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

RE: [GENERAL] pl sql to check if table of table_name existsI'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@relyant.co.za> wrote in message news:100F78F2B203444BB161BBA7077FF6131CD89E@srldbexc003.relyant.co.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@compulab.co.il]
Sent: 10 March 2005 01:24 PM
To: pgsql-general@postgresql.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@compulab.co.il> wrote in message news:d0pamh$2l83$1@news.hub.org...
select your_tablename from pg_class where relkind='r'
"Shaun Clements" <ShaunC@relyant.co.za> wrote in message news:100F78F2B203444BB161BBA7077FF6131CD89C@srldbexc003.relyant.co.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

Hi Sim

Thanks for your input.

Kind Regards,
Shaun Clements

-----Original Message-----
From: Sim Zacks [mailto:sim@compulab.co.il]
Sent: 10 March 2005 02:47 PM
To: pgsql-general@postgresql.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@relyant.co.za <mailto:ShaunC@relyant.co.za> >
wrote in message
news:100F78F2B203444BB161BBA7077FF6131CD89E@srldbexc003.relyant.co.za
<news:100F78F2B203444BB161BBA7077FF6131CD89E@srldbexc003.relyant.co.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@compulab.co.il]
Sent: 10 March 2005 01:24 PM
To: pgsql-general@postgresql.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@compulab.co.il <mailto:sim@compulab.co.il> > wrote in
message news:d0pamh$2l83$1@news.hub.org <news:d0pamh$2l83$1@news.hub.org>
...
select your_tablename from pg_class where relkind='r'

"Shaun Clements" < ShaunC@relyant.co.za <mailto:ShaunC@relyant.co.za> >
wrote in message
news:100F78F2B203444BB161BBA7077FF6131CD89C@srldbexc003.relyant.co.za
<news:100F78F2B203444BB161BBA7077FF6131CD89C@srldbexc003.relyant.co.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