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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] pl sql to check if table of table_name exists
Title: RE: [GENERAL] 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
Re: [GENERAL] pl sql to check if table of table_name exists
Title: RE: [GENERAL] pl sql to check if table of table_name exists Much appreciated. Thanks Kind Regards, Shaun Clements -Original Message- From: Adam Tomjack [mailto:[EMAIL PROTECTED]] 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
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. 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] pl sql to check if table of table_name exists
Title: RE: [GENERAL] pl sql to check if table of table_name exists selectyour_tablename from pg_class where relkind='r' "Shaun Clements" [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]... 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 exists
Title: 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" [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]... selectyour_tablename from pg_class where relkind='r' "Shaun Clements" [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]... 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 exists
Title: RE: [GENERAL] pl sql to check if table of table_name exists 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:[EMAIL PROTECTED]Sent: 10 March 2005 01:24 PMTo: pgsql-general@postgresql.orgSubject: 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" [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]... selectyour_tablename from pg_class where relkind='r' "Shaun Clements" [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]... 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 exists
Title: RE: [GENERAL] pl sql to check if table of table_name exists I'm gladto hear yougot itworking. 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" [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]... 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:[EMAIL PROTECTED]Sent: 10 March 2005 01:24 PMTo: pgsql-general@postgresql.orgSubject: 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" [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]... selectyour_tablename from pg_class where relkind='r' "Shaun Clements" [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]... 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 exists
Title: RE: [GENERAL] pl sql to check if table of table_name exists Hi Sim Thanks for your input. Kind Regards,Shaun Clements -Original Message-From: Sim Zacks [mailto:[EMAIL PROTECTED]Sent: 10 March 2005 02:47 PMTo: pgsql-general@postgresql.orgSubject: Re: [GENERAL] pl sql to check if table of table_name exists I'm gladto hear yougot itworking. 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" [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]... 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:[EMAIL PROTECTED]Sent: 10 March 2005 01:24 PMTo: pgsql-general@postgresql.orgSubject: 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" [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]... selectyour_tablename from pg_class where relkind='r' "Shaun Clements" [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]... 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
