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:

10 March 2005, 12:09:41

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