pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedChecking for table existence
7+ messages / 6 participants
[nested] [flat]
* Checking for table existence
@ 2001-09-14 18:58 Julester <cubalibr@optonline.net>
0 siblings, 5 replies; 7+ messages in thread
From: Julester @ 2001-09-14 18:58 UTC (permalink / raw)
To: 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
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Checking for table existence
@ 2001-09-15 08:48 Hans-Juergen Schoenig <hs@bachata.cybertec.at>
parent: Julester <cubalibr@optonline.net>
4 siblings, 0 replies; 7+ messages in thread
From: Hans-Juergen Schoenig @ 2001-09-15 08:48 UTC (permalink / raw)
To: pgsql-sql
Simply look it up in the system tables:
SELECT * FROM pg_class WHERE relname='mydata';
Hans
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Checking for table existence
@ 2001-09-17 14:57 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Julester <cubalibr@optonline.net>
4 siblings, 0 replies; 7+ messages in thread
From: Stephan Szabo @ 2001-09-17 14:57 UTC (permalink / raw)
To: Julester <cubalibr@optonline.net>; +Cc: pgsql-sql
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...
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Checking for table existence
@ 2001-09-18 00:12 Roberto Mello <rmello@cc.usu.edu>
parent: Julester <cubalibr@optonline.net>
4 siblings, 0 replies; 7+ messages in thread
From: Roberto Mello @ 2001-09-18 00:12 UTC (permalink / raw)
To: Julester <cubalibr@optonline.net>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Checking for table existence
@ 2001-09-18 01:11 Kovacs Baldvin <kb136@hszk.bme.hu>
parent: Julester <cubalibr@optonline.net>
4 siblings, 0 replies; 7+ messages in thread
From: Kovacs Baldvin @ 2001-09-18 01:11 UTC (permalink / raw)
To: Julester <cubalibr@optonline.net>; +Cc: 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
Attachments:
spi21548.htm (text/html, 5.0K) download
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML
><HEAD
><TITLE
>Examples</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet version 1.18"><LINK
REL="HOME"
TITLE="PostgreSQL"
HREF="postgres.htm"><LINK
REL="UP"
TITLE="Server Programming Interface"
HREF="spi.htm"><LINK
REL="PREVIOUS"
TITLE="Visibility of Data Changes"
HREF="spi21543.htm"><LINK
REL="NEXT"
TITLE="Procedural Languages"
HREF="xplang.htm"></HEAD
><BODY
BGCOLOR="#FFFFFF"
TEXT="#000000"
><DIV
CLASS="NAVHEADER"
><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="3"
ALIGN="center"
>PostgreSQL</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="bottom"
><A
HREF="spi21543.htm"
>Prev</A
></TD
><TD
WIDTH="80%"
ALIGN="center"
VALIGN="bottom"
>Chapter 48. Server Programming Interface</TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="bottom"
><A
HREF="xplang.htm"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><H1
CLASS="SECT1"
><A
NAME="AEN21548"
>Examples</A
></H1
><P
> This example of SPI usage demonstrates the visibility rule.
There are more complex examples in in src/test/regress/regress.c and
in contrib/spi.</P
><P
> This is a very simple example of SPI usage. The procedure execq accepts
an SQL-query in its first argument and tcount in its second, executes the
query using SPI_exec and returns the number of tuples for which the query
executed:
<PRE
CLASS="PROGRAMLISTING"
>#include "executor/spi.h" /* this is what you need to work with SPI */
int execq(text *sql, int cnt);
int
execq(text *sql, int cnt)
{
int ret;
int proc = 0;
SPI_connect();
ret = SPI_exec(textout(sql), cnt);
proc = SPI_processed;
/*
* If this is SELECT and some tuple(s) fetched -
* returns tuples to the caller via elog (NOTICE).
*/
if ( ret == SPI_OK_SELECT && SPI_processed > 0 )
{
TupleDesc tupdesc = SPI_tuptable->tupdesc;
SPITupleTable *tuptable = SPI_tuptable;
char buf[8192];
int i;
for (ret = 0; ret < proc; ret++)
{
HeapTuple tuple = tuptable->vals[ret];
for (i = 1, buf[0] = 0; i <= tupdesc->natts; i++)
sprintf(buf + strlen (buf), " %s%s",
SPI_getvalue(tuple, tupdesc, i),
(i == tupdesc->natts) ? " " : " |");
elog (NOTICE, "EXECQ: %s", buf);
}
}
SPI_finish();
return (proc);
}</PRE
></P
><P
> Now, compile and create the function:
<PRE
CLASS="PROGRAMLISTING"
>create function execq (text, int4) returns int4 as '...path_to_so' language 'c';</PRE
>
<PRE
CLASS="PROGRAMLISTING"
>vac=> select execq('create table a (x int4)', 0);
execq
-----
0
(1 row)
vac=> insert into a values (execq('insert into a values (0)',0));
INSERT 167631 1
vac=> select execq('select * from a',0);
NOTICE:EXECQ: 0 <<< inserted by execq
NOTICE:EXECQ: 1 <<< value returned by execq and inserted by upper INSERT
execq
-----
2
(1 row)
vac=> select execq('insert into a select x + 2 from a',1);
execq
-----
1
(1 row)
vac=> select execq('select * from a', 10);
NOTICE:EXECQ: 0
NOTICE:EXECQ: 1
NOTICE:EXECQ: 2 <<< 0 + 2, only one tuple inserted - as specified
execq
-----
3 <<< 10 is max value only, 3 is real # of tuples
(1 row)
vac=> delete from a;
DELETE 3
vac=> insert into a values (execq('select * from a', 0) + 1);
INSERT 167712 1
vac=> select * from a;
x
-
1 <<< no tuples in a (0) + 1
(1 row)
vac=> insert into a values (execq('select * from a', 0) + 1);
NOTICE:EXECQ: 0
INSERT 167713 1
vac=> select * from a;
x
-
1
2 <<< there was single tuple in a + 1
(2 rows)
-- This demonstrates data changes visibility rule:
vac=> insert into a select execq('select * from a', 0) * x from a;
NOTICE:EXECQ: 1
NOTICE:EXECQ: 2
NOTICE:EXECQ: 1
NOTICE:EXECQ: 2
NOTICE:EXECQ: 2
INSERT 0 2
vac=> select * from a;
x
-
1
2
2 <<< 2 tuples * 1 (x in first tuple)
6 <<< 3 tuples (2 + 1 just inserted) * 2 (x in second tuple)
(4 rows) ^^^^^^^^
tuples visible to execq() in different invocations</PRE
></P
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="spi21543.htm"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="postgres.htm"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="xplang.htm"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Visibility of Data Changes</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="spi.htm"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Procedural Languages</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Checking for table existence
@ 2001-09-18 03:54 Josh Berkus <josh@agliodbs.com>
parent: Julester <cubalibr@optonline.net>
4 siblings, 1 reply; 7+ messages in thread
From: Josh Berkus @ 2001-09-18 03:54 UTC (permalink / raw)
To: Julester <cubalibr@optonline.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Checking for table existence
@ 2001-09-18 14:41 Kovacs Baldvin <kb136@hszk.bme.hu>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 7+ messages in thread
From: Kovacs Baldvin @ 2001-09-18 14:41 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Julester <cubalibr@optonline.net>; pgsql-sql
>
> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2001-09-18 14:41 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-09-14 18:58 Checking for table existence Julester <cubalibr@optonline.net>
2001-09-15 08:48 ` Hans-Juergen Schoenig <hs@bachata.cybertec.at>
2001-09-17 14:57 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-09-18 00:12 ` Roberto Mello <rmello@cc.usu.edu>
2001-09-18 01:11 ` Kovacs Baldvin <kb136@hszk.bme.hu>
2001-09-18 03:54 ` Josh Berkus <josh@agliodbs.com>
2001-09-18 14:41 ` Kovacs Baldvin <kb136@hszk.bme.hu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox