Обсуждение: Re: pl sql to check if table of table_name exists
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
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 >
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
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
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