How can I check if table exists in DB?

Поиск
Список
Период
Сортировка
От Sergey Belikov
Тема How can I check if table exists in DB?
Дата
Msg-id 3FB96F51.7080005@bnl.gov
обсуждение исходный текст
Ответы Re: How can I check if table exists in DB?  (Bruno LEVEQUE <bruno.leveque@net6d.com>)
Список pgsql-novice
Dear PSQL experts,
how can I check if some table exists in my database or not? I tried the simplest approach:
PGConn *conn=PQconnectdb("database='mydb'");
PQexec(conn, "BEGIN");
...
PGresult res=PQexec(conn,"SELECT id FROM mytable");
if(PQresultStatus(res)==PGRES_FATAL_ERROR && strstr(PQresultErrorMessage(res),"Relation \"mytable\" does not exist") // mytable does not exist in mydb
{
    res=PQexec(conn,"CREATE TABLE mytable (id int, name text)");
}
....


Funny things happened if mytable did not exist: all  my queries after  PQexec(conn,"SELECT id FROM mytable") returned result status PGRES_FATAL_ERROR, all my attempts to import large objects failed: psql reported that it was unable to open large object with loid # 12345 (but it had created that new large object!), and so on. After multiple attempts I figured out that the only way to run my program smoothly is to restart transaction after the test of the existence:
 PGresult res=PQexec(conn,"SELECT id FROM mytable");
if(PQresultStatus(res)==PGRES_FATAL_ERROR && strstr(PQresultErrorMessage(res),"Relation \"mytable\" does not exist") // mytable does not exist in mydb
{
   
PQexec(conn, "BEGIN");
    res=PQexec(conn,"CREATE TABLE mytable (id int, name text)");
}

....
But it discarded all that I did in between first PQexec(conn, "BEGIN"); and PGresult res=PQexec(conn,"SELECT id FROM mytable");. Finally I was forced to check the existence of all necessary tables at the beginning of the program, to create nonexistent ones, and only then to start my transaction. Too ugly to be right way to solve this problem. Has PSQL some function or macro that permits to do such check without destruction of my transaction?
Thank you, Sergey.

В списке pgsql-novice по дате отправления:

Предыдущее
От: Sergey Belikov
Дата:
Сообщение: Re: Threads in PSQL
Следующее
От: Godshall Michael
Дата:
Сообщение: creating/droping a table from a function stored in public vs a sc hema