> 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
Title: Examples
ExamplesThis example of SPI usage demonstrates the visibility rule. There are more complex examples in in src/test/regress/regress.c and in contrib/spi. 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: #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);
}Now, compile and create the function: create function execq (text, int4) returns int4 as '...path_to_so' language 'c'; 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 |
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
