Re: [GENERAL] pl sql to check if table of table_name exists

[Adam Tomjack]

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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings