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