pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Kovacs Baldvin <kb136@hszk.bme.hu>
To: Julester <cubalibr@optonline.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Checking for table existence
Date: Tue, 18 Sep 2001 03:11:49 +0200 (MEST)
Message-ID: <Pine.GSO.4.33.0109180246250.16528-200000@ural2> (raw)
In-Reply-To: <prso7.50245$f7.6232115@news02.optonline.net>


> 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 >

view thread (7+ messages)  latest in thread

Message-ID: <Pine.GSO.4.33.0109180246250.16528-200000@ural2>
Permalink:  ../Pine.GSO.4.33.0109180246250.16528-200000@ural2/
Also on:    postgresql.org/message-id/Pine.GSO.4.33.0109180246250.16528-200000@ural2

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: kb136@hszk.bme.hu, cubalibr@optonline.net
  Subject: Re: Checking for table existence
  In-Reply-To: <Pine.GSO.4.33.0109180246250.16528-200000@ural2>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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