Обсуждение: Checking for table existence

Hi everyone.  In my old SQL Server days, I used a command such as "IF
exists(select name from sys_objects where name = 'xyztable')"  to check if a
table existed before creating it with a standard CREATE command.  I looked
in the PostgreSQL documentation, but for the life of me, I can't find an
equivalent.  I can view if the table exists by doing a select against the
meta-data tables, but what about the IF statement ?  Any help would be
greatly appreciated.  Thanks.


Julester

Simply look it up in the system tables:

SELECT * FROM pg_class WHERE relname='mydata';
   Hans

On Fri, 14 Sep 2001, Julester wrote:

> Hi everyone.  In my old SQL Server days, I used a command such as "IF
> exists(select name from sys_objects where name = 'xyztable')"  to check if a
> table existed before creating it with a standard CREATE command.  I looked
> in the PostgreSQL documentation, but for the life of me, I can't find an
> equivalent.  I can view if the table exists by doing a select against the
> meta-data tables, but what about the IF statement ?  Any help would be
> greatly appreciated.  Thanks.

AFAIK, not really directly in SQL, but you can wrap such things in
functions in plpgsql/pltcl/etc...

Simply look it up in the system tables:

SELECT * FROM pg_class WHERE relname='mydata';
   Hans

Simply look it up in the system tables:

SELECT * FROM pg_class WHERE relname='mydata';
   Hans

On Fri, Sep 14, 2001 at 06:58:29PM +0000, Julester wrote:
> Hi everyone.  In my old SQL Server days, I used a command such as "IF
> exists(select name from sys_objects where name = 'xyztable')"  to check if a
> table existed before creating it with a standard CREATE command.  I looked
> in the PostgreSQL documentation, but for the life of me, I can't find an
> equivalent.  I can view if the table exists by doing a select against the
> meta-data tables, but what about the IF statement ?  Any help would be
> greatly appreciated.  Thanks.
You can have psql output its internal queries and that will give you
some insight:

roberto@brasileiro:~/documents/pictures$ psql -e foobar
Welcome to psql, the PostgreSQL interactive terminal.
...
foobar=# \d blah
********* QUERY *********
SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules
FROM pg_class WHERE relname='blah'
*************************

********* QUERY *********
SELECT a.attname, format_type(a.atttypid, a.atttypmod), a.attnotnull,
a.atthasdef, a.attnum
FROM pg_class c, pg_attribute a
WHERE c.relname = 'blah' AND a.attnum > 0 AND a.attrelid = c.oid
ORDER BY a.attnum
*************************

********* QUERY *********
SELECT substring(d.adsrc for 128) FROM pg_attrdef d, pg_class c
WHERE c.relname = 'blah' AND c.oid = d.adrelid AND d.adnum = 1
*************************
                    Table "blah"Attribute |           Type           |   Modifier    
-----------+--------------------------+---------------something | timestamp with time zone | default 'now'name      |
character(50)
-Roberto
-- 
+------------| Roberto Mello - http://www.brasileiro.net |------------+
Computer Science, Utah State University  -    http://www.usu.edu
USU Free Software & GNU/Linux Club       -    http://fslc.usu.edu
Space Dynamics Lab, Developer            -    http://www.sdl.usu.edu
OpenACS - Enterprise free web toolkit    -    http://openacs.org
Blood is thicker than water, and much tastier.

> Hi everyone.  In my old SQL Server days, I used a command such as "IF
> exists(select name from sys_objects where name = 'xyztable')"  to check if a

As far as I know, there is not direct support of this. However, I also
would appreciate a builtin qexec(text) procedure, for making queries.

Now I present a workaround for this probably missing functionality.
Developers, if this functionality is included, please let me know.

---------------------------------

1. First, suppose that we have a function, called qexec, which
runs the given text parameter as an sql query, and returns the
int, which it got from the backend.

In this situation, your problem can be solved this way:

SELECT CASE WHEN NOT yourtablename IN (your nice select from pg_blabla)  THEN qexec('CREATE TABLE (as you like it)')
END;

-----------------------------------

2. Now the only thing left is to define the qexec procedure.

2/a. If you are a C programmer:
    Then try the way presented in the documentation. I included    the relating section from my somewhat oldie
documentation,please    search the same in the current by grepping about a bit.
 

2/b. If you are not so brave:
    You can try for example pltcl. Issue these commands

As postgres superuser, the path replaced to yours:

create function pltcl_call_handler() returns opaque as  '/usr/local/pgsql/lib/pltcl.so' language 'C';

create trusted procedural language 'pltcl'
handler pltcl_call_handler
lancompiler 'Pl/pltcl';

As any user:

create function qexec(text) returns int as '
return [spi_exec [ quote $1 ]]
' language 'pltcl';

Now try, what you've done:

select qexec('select 1=1');

You should get 1.


--------------------------------------------

Here you are. If anybody knows a much simpler solution, please let
me know. If it helped or not, let me know also.

Regards,
Baldvin

Julester,

> Hi everyone.  In my old SQL Server days, I used a command such as "IF
> exists(select name from sys_objects where name = 'xyztable')"  to
> check if a
> table existed before creating it with a standard CREATE command.  I
> looked
> in the PostgreSQL documentation, but for the life of me, I can't find
> an
> equivalent.  I can view if the table exists by doing a select against
> the
> meta-data tables, but what about the IF statement ?  Any help would
> be
> greatly appreciated.  Thanks.

<grin> You've gotten a lot of complex answers to a simple question.
Confused yet?

If you're doing this in PL/pgSQL, you want a couple of functions:
(Hey Roberto, how about posting the 1st function on your site?)

CREATE FUNCTON table_exists(
    VARCHAR ) RETURNS BOOLEAN AS '
DECLARE
    t_name ALIAS for $1;
    t_result VARCHAR;
BEGIN
    --find table, case-insensitive
    SELECT relname INTO t_result
    FROM pg_class
    WHERE relname ~* (''^'' || t_name || ''$'')
        AND relkind = 'r';
    IF t_result IS NULL THEN
        RETURN FALSE;
    ELSE
        RETURN TRUE;
    END IF;
END;'
LANGUAGE 'plpgsql';


... then you build your function around this:

CREATE FUNCTION my_function ( ...

...
IF NOT table_exists(''my_table'') THEN
    CREATE TABLE ...
END IF;
...


Got the idea?

-Josh



______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

>
> CREATE FUNCTION my_function ( ...
>
> ....
> IF NOT table_exists(''my_table'') THEN
>     CREATE TABLE ...
> END IF;
> ....
>
>
> Got the idea?

Not bad... Well, I am not the person questioned... But let me
share a few ideas about it.

The solution what I gave was a "more SQL way" of doing it. So after
creating the function qexec, you could write a long file, x.sql, like

... sql blabla...
... sql blabla...

CASE WHEN ...the table not exests... THEN qexec('the creator sql command')
END;

... sql blabla...

My experience is this is a quite universal style, if a company
would like to create an sql-database structure for a certain job.

Your solution is also based on functions, so the realization is quite
similar. But you have either write a function for every table creation,
or write an intelligent function, which gets HOW to create WHAT table...

My first idea was something like this, but later I thougth it is
"politically more correct" if I use only a very universal function,
what I called qexec.

Moreover, I can imagine that I would see happily this sql-executor
function in the base distribution, not only int the documentation,
as an example. It is quite a good example...

However, your solution is naturally a good and usable solution, please
do not treat this letter as an offensive letter. I only tried to argue
a bit for my way of thinking...

Thanks for reading this, if you reached this pont, :-))

Regads,

Baldvin

>
> -Josh
>
>
>
> ______AGLIO DATABASE SOLUTIONS___________________________
>                                        Josh Berkus
>   Complete information technology      josh@agliodbs.com
>    and data management solutions       (415) 565-7293
>   for law firms, small businesses        fax 621-2533
>     and non-profit organizations.      San Francisco
>

Üdv,
Baldvin