pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Checking for table existence
7+ messages / 6 participants
[nested] [flat]

* Checking for table existence
@ 2001-09-14 18:58 Julester <cubalibr@optonline.net>
  2001-09-15 08:48 ` Re: Checking for table existence Hans-Juergen Schoenig <hs@bachata.cybertec.at>
  2001-09-17 14:57 ` Re: Checking for table existence Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-09-18 00:12 ` Re: Checking for table existence Roberto Mello <rmello@cc.usu.edu>
  2001-09-18 01:11 ` Re: Checking for table existence Kovacs Baldvin <kb136@hszk.bme.hu>
  2001-09-18 03:54 ` Re: Checking for table existence Josh Berkus <josh@agliodbs.com>
  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-14 18:58 Checking for table existence Julester <cubalibr@optonline.net>
@ 2001-09-15 08:48 ` Hans-Juergen Schoenig <hs@bachata.cybertec.at>
  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-14 18:58 Checking for table existence Julester <cubalibr@optonline.net>
@ 2001-09-17 14:57 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  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-14 18:58 Checking for table existence Julester <cubalibr@optonline.net>
@ 2001-09-18 00:12 ` Roberto Mello <rmello@cc.usu.edu>
  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-14 18:58 Checking for table existence Julester <cubalibr@optonline.net>
@ 2001-09-18 01:11 ` Kovacs Baldvin <kb136@hszk.bme.hu>
  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 &#38;&#38; SPI_processed &#62; 0 ) { TupleDesc tupdesc = SPI_tuptable-&#62;tupdesc; SPITupleTable *tuptable = SPI_tuptable; char buf[8192]; int i; for (ret = 0; ret &#60; proc; ret++) { HeapTuple tuple = tuptable-&#62;vals[ret]; for (i = 1, buf[0] = 0; i &#60;= tupdesc-&#62;natts; i++) sprintf(buf + strlen (buf), " %s%s", SPI_getvalue(tuple, tupdesc, i), (i == tupdesc-&#62;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=&#62; select execq('create table a (x int4)', 0); execq ----- 0 (1 row) vac=&#62; insert into a values (execq('insert into a values (0)',0)); INSERT 167631 1 vac=&#62; select execq('select * from a',0); NOTICE:EXECQ: 0 &#60;&#60;&#60; inserted by execq NOTICE:EXECQ: 1 &#60;&#60;&#60; value returned by execq and inserted by upper INSERT execq ----- 2 (1 row) vac=&#62; select execq('insert into a select x + 2 from a',1); execq ----- 1 (1 row) vac=&#62; select execq('select * from a', 10); NOTICE:EXECQ: 0 NOTICE:EXECQ: 1 NOTICE:EXECQ: 2 &#60;&#60;&#60; 0 + 2, only one tuple inserted - as specified execq ----- 3 &#60;&#60;&#60; 10 is max value only, 3 is real # of tuples (1 row) vac=&#62; delete from a; DELETE 3 vac=&#62; insert into a values (execq('select * from a', 0) + 1); INSERT 167712 1 vac=&#62; select * from a; x - 1 &#60;&#60;&#60; no tuples in a (0) + 1 (1 row) vac=&#62; insert into a values (execq('select * from a', 0) + 1); NOTICE:EXECQ: 0 INSERT 167713 1 vac=&#62; select * from a; x - 1 2 &#60;&#60;&#60; there was single tuple in a + 1 (2 rows) -- This demonstrates data changes visibility rule: vac=&#62; 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=&#62; select * from a; x - 1 2 2 &#60;&#60;&#60; 2 tuples * 1 (x in first tuple) 6 &#60;&#60;&#60; 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-14 18:58 Checking for table existence Julester <cubalibr@optonline.net>
@ 2001-09-18 03:54 ` Josh Berkus <josh@agliodbs.com>
  2001-09-18 14:41   ` Re: Checking for table existence Kovacs Baldvin <kb136@hszk.bme.hu>
  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-14 18:58 Checking for table existence Julester <cubalibr@optonline.net>
  2001-09-18 03:54 ` Re: Checking for table existence Josh Berkus <josh@agliodbs.com>
@ 2001-09-18 14:41   ` Kovacs Baldvin <kb136@hszk.bme.hu>
  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