Postgres Pro
Downloads
Home   >   mailing lists

Thread: Re: pl sql to check if table of table_name exists

Re: pl sql to check if table of table_name exists

From
Shaun Clements
Date:

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: pl sql to check if table of table_name exists

From
Chris Travers
Date:
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

> Thanks in advance
>
> Kind Regards,
> Shaun Clements
>

Re: pl sql to check if table of table_name exists

From
"Sim Zacks"
Date:
select your_tablename from pg_class where relkind='r'

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: pl sql to check if table of table_name exists

From
"Sim Zacks"
Date:
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'

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: pl sql to check if table of table_name exists

From
Adam Tomjack
Date:
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