Re: Checking for table existence - Mailing list pgsql-sql

From Kovacs Baldvin
Subject Re: Checking for table existence
Date
Msg-id Pine.GSO.4.33.0109180246250.16528-200000@ural2
Whole thread Raw
In response to Checking for table existence  ("Julester" <cubalibr@optonline.net>)
List pgsql-sql
> 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


pgsql-sql by date:

Previous
From: Roberto Mello
Date:
Subject: Re: Checking for table existence
Next
From: "Josh Berkus"
Date:
Subject: Re: Checking for table existence