Re: [SQL] SQL request change when upgrade from 7.0.2 to 7.1.3
Josh Berkus a écrit : Richard, I'm curious now. What happens if you remove the table qualifications, e.g.: ORDER BY type, nom; -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco Hello Josh, Yes, it works! I don't know exactly why, but it works! Thanks a lot. -- Richard NAGY Presenceweb
Re: [SQL] SQL request change when upgrade from 7.0.2 to 7.1.3
Andre Schnabel a écrit : Hello Richard, I did some testing and after all your query should be ordered right. The test's I have done: Test=# select t.foo1 from testtable t Test-# union Test-# select t.foo2 from testtable t Test-# order by t.foo1; ERROR: Relation 't' does not exist Same error as you get Test=# select t.foo1 from testtable t Test-# union Test-# select t.foo2 from testtable t Test-# order by foo1; foo1 -- abc cdef (2 rows) Ordered Ascending (maybe by chance?) --- Test=# select t.foo1 from testtable t Test-# union Test-# select t.foo2 from testtable t Test-# order by foo1 DESC; foo1 -- cdef abc (2 rows) Ordered descending (ordering works!) --- I guess, the table-alias is not known to the order-clause. Maybe the execution (or parsing) order of the UNION changed from 7.0 to 7.1. Hello Andre, Thanks very much for having tested. Yes, it works well. -- Richard NAGY Presenceweb
Re: [SQL] SQL request change when upgrade from 7.0.2 to 7.1.3
Tom Lane a écrit : Richard NAGY <[EMAIL PROTECTED]> writes: > SELECT aes.ent_id, e.type, e.nom, aes.sect_id as voulu, > cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes, > entreprise e WHERE e.id = aes.ent_id and aes.sect_id <> 3 and > aes.sect_id <> 9 > and aes.sect_id <> 1 and aes.sect_id <> 13 and aes.sect_id = 56 > UNION > SELECT distinct on (aes.ent_id) aes.ent_id, e.type, e.nom, aes.sect_id, > cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes, > entreprise e WHERE e.id = aes.ent_id and aes.sect_id <> 3 and > aes.sect_id <> 9 > and aes.sect_id <> 1 and aes.sect_id <> 13 and aes.sect_id <> 56 and > aes.ent_id > not in (SELECT ent_id FROM ass_entrep_sectact WHERE sect_id = 56 and > sect_id <> 3 > and sect_id <> 9 and sect_id <> 1 and sect_id <> 13) ORDER BY e.type, > e.nom ORDER BY applied to the result of a UNION has to be on the output columns of the UNION. "e.type" etc are names of input-table columns. Try just "type" and "nom", which will refer to the second and third output columns of the UNION. Pre-7.1 got this wrong (and would sometimes produce wrong output ordering or even a backend crash, if the arms of the UNION didn't all yield the same datatype). regards, tom lane Hello Tom, Yes, thanks a lot. It works! Regards. -- Richard NAGY Presenceweb
Re: [SQL] Part 2 of "Intentional, or a bug"....
> > After these I use ~= if I need lines with NULLs in both to be selected. > > Perhaps you should reconsider your data model. You seem to be treating The given column is a date type column, with some records where "date is not given". This "not given" is represented by NULL. I saw it more elegant than assigning a date in the very past for "not given", or using a boolean column called "date given". After all, I consider using the additional boolean column. Bye, Baldvin ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Number the lines
Am I right in assuming you were asking for something like SELECT Rownumbering(), ... FROM ORDER BY ; and expecting a result like 1| ... 2| ... 3| ... ... where Rownumbering() is a PostgreSQL function If this is what you wanted, this is something I want for a long time too. Maybe we should stick together and tell the PostgreSQL hackers about our wish. Or maybe something like this already exists, but we haven't noticed yet. Regards, Christoph ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] functions returning
Hi
I had to write big sql code returning max 10 rows (3 selects 7,10 and 13
joins,
mostly equi-joins - too long to include). I tried do solve this in
different ways:
1. create view
Look at simple test.sql in attachement. Just make psql -f test.sql. I
have PostgreSQL 7.1.2.
I'm used to C,C++, not to SQL and I can't understand why selecting 1 row
from K2 doesn't use index scan for only 1 row.
-> Index Scan using k_pkey on k k1 (cost=0.00..2.02 rows=1
width=8)
-> Index Scan using k_pkey on k k2 (cost=0.00..8.14 rows=10 width=8)
Second view - bla2 works much worse...
Does it correspond to my question about dynamic date which should be
static (pgsql-bugs)?
Views would be nice but only if we could send them params...
2. sql function
I tried to return _int4 (array of int) - enough for me, but...
how to make array of non-static int?
'{0,1,2}' works fine, but '{id_t,id_k}' not
3. pl/pgsql function
first step - create temp table (once per session)
second step - pl/pgsql clears and inserts into that table.
I think it's the best solution. It works really fast. I can split sql
into smaller pieces and work on them separately.
Does anyone know how to make function returning rows another way?
Regards,
Tomek
create table T(
id_t integer not null PRIMARY KEY,
nazwa varchar (80)
);
create table K(
id_k integer not null,
id_t integer not null references T,
data_kdate not null,
primary key(id_t,data_k)
);
create table TP(
id_t integer not null references T,
nrsmallint not null,
date_diff smallint not null default 0
);
create index ind_tp_t on TP(id_t);
insert into T values (1,'aa');
insert into T values (2,'bb');
insert into K values (1,1,'2001-09-12');
insert into K values (2,1,'2001-09-13');
insert into K values (3,1,'2001-09-14');
insert into K values (4,1,'2001-09-15');
insert into K values (5,2,'2001-09-12');
insert into K values (6,2,'2001-09-13');
insert into K values (7,2,'2001-09-14');
insert into K values (8,2,'2001-09-15');
insert into K values (9,2,'2001-09-16');
insert into TP values (1,1,0);
insert into TP values (2,3,0);
create view bla as select
T1.id_t,
K1.data_k+TP.date_diff as data1,
TP.nr
from
(select * from T limit 1) T1
inner join TP on (TP.id_t=T1.id_t)
inner join K K1 on (TP.id_t=K1.id_t and K1.data_k='2001-09-23')
inner join K K2 on (TP.id_t=K2.id_t and K2.data_k=K1.data_k+TP.date_diff);
create view bla2 as select
T1.id_t,
K1.data_k+TP.date_diff as data1,
TP.nr
from
(select * from T limit 1) T1
inner join TP on (TP.id_t=T1.id_t)
inner join K K1 on (TP.id_t=K1.id_t) -- and K1.data_k='2001-09-23')
inner join K K2 on (TP.id_t=K2.id_t and K2.data_k=K1.data_k+TP.date_diff);
select * from pg_indexes where tablename in ('t','k','tp');
explain select * from bla where id_t=1;
explain select * from bla2 where id_t=1 and data1='2001-09-24';
drop view bla2;
drop view bla;
drop table TP;
drop table K;
drop table T;
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] group by weirdness
Would this give you what you want? SELECT j.id, j.created, COUNT(mj.mid), SUM(CASE WHEN ml.state <> 11 THEN 1 ELSE 0 END) AS tally_1, SUM (CASE WHEN ml.state IN(2,5) THEN 1 ELSE 0 END)AS tally_2 FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id; ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Number the lines
Hi!
I am in the university now, not be able to test what I
say, so please forgive me if it is buggy or wrong.
So does it helps you?:
CREATE SEQUENCE ordering;
SELECT NEXTVAL('ordering') as rownumber, *
INTO TABLE anewname FROM whatyouwant;
Probably CREATE SEQUENCE 'ordering';, do try it...
Bye,
Baldvin
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] Checking for table existence
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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] How to see the definition of an existing table?
Is there any command or query to see the definition of an existing table, like which is the primary key, which is not null etc. Thanks! --Ke ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] How to Get Timestamp From SECONDS?
Try it with something like that: mydata=# SELECT '1-1-2001'::timestamp + '200 seconds'::interval; ?column? 2001-01-01 00:03:20+01 (1 row) epoch + seconds = desired timestamp. Hans ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] group by weirdness
Joseph, you might want to try: CREATE VIEW mj1 (jid, cnt) AS SELECT jid, COUNT(mid) cnt FROM mj GROUP BY jid; CREATE VIEW ml1 (jid, cnt) AS SELECT jid, COUNT(*) cnt FROM ml WHERE state <> 11 GROUP BY jid; CREATE VIEW ml2 (jid, cnt) AS SELECT jid, COUNT(*) cnt FROM ml WHERE state IN (2,5) GROUP BY jid; SELECT j.id, j.created, mj1.cnt, ml1.cnt, ml2.cnt FROM j LEFT JOIN mj1 ON (j.id = mj1.jid) LEFT JOIN ml1 ON (j.id = ml1.jid) LEFT JOIN ml2 ON (j.id = ml2.jid) WHERE j.fkey = 1; I did not test this with PostgreSQL, but you get the idea. Probably PG is even smart enough to handle it all in one: SELECT j.id, j.created, mj1.cnt, ml1.cnt, ml2.cnt FROM j LEFT JOIN (SELECT jid, COUNT(mid) cnt FROM mj GROUP BY jid) mj1 ON (j.id = mj1.jid) LEFT JOIN (SELECT jid, COUNT(*) cnt FROM ml WHERE state <> 11 GROUP BY jid) ml1 ON (j.id = ml1.jid) LEFT JOIN (SELECT jid, COUNT(*) cnt FROM ml WHERE state IN (2, 5) GROUP BY jid) ml2 ON (j.id = ml2.jid) WHERE j.fkey = 1; HTH, Carl van Tast On Tue, 11 Sep 2001 02:26:32 + (UTC), [EMAIL PROTECTED] (Joseph Shraibman) wrote: >Could someome explain these error messages to me? Why am I being asked to group by >j.id? > And why is the subquery worried about ml.oid if ml.oid is used in an aggregate? > >Follows: script, then output. > > >select version(); >create table j (id int, created timestamp default current_timestamp, fkey int); >create table mj (jid int, mid int); >create table ml (jid int, created timestamp default current_timestamp, state int); > >insert into j (id, fkey) values (1, 1); >insert into j (id, fkey) values (2, 1); > >insert into mj values(1, 1); >insert into mj values(1, 2); >insert into mj values(2, 3); >insert into mj values(2, 4); >insert into mj values(2, 5); > >insert into ml(jid, state) values (1, 2); >insert into ml(jid, state) values (1, 2); >insert into ml(jid, state) values (1, 2); >insert into ml(jid, state) values (1, 2); >insert into ml(jid, state) values (1, 11); >insert into ml(jid, state) values (2, 2); >insert into ml(jid, state) values (2, 2); >insert into ml(jid, state) values (2, 11); > >select j.id, j.created, count(mj.mid), > (select count(ml.oid) where ml.state <> 11), > (select count(ml.oid) where ml.state IN(2,5) ) >FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id ; > >select j.id, j.created, count(mj.mid), > (select count(ml.oid) where ml.state <> 11), > (select count(ml.oid) where ml.state IN(2,5) ) >FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id >group by j.id, j.created; > >drop table j; >drop table mj ; >drop table ml; > >=== > >playpen=# select version(); >version >- > PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66 >(1 row) > >playpen=# create table j (id int, created timestamp default current_timestamp, fkey >int); >CREATE >playpen=# create table mj (jid int, mid int); >CREATE >playpen=# create table ml (jid int, created timestamp default current_timestamp, >state int); >CREATE >playpen=# >playpen=# insert into j (id, fkey) values (1, 1); > >playpen=# insert into ml(jid, state) values (2, 11); >INSERT 329676 1 >playpen=# >playpen=# select j.id, j.created, count(mj.mid), >playpen-# (select count(ml.oid) where ml.state <> 11), >playpen-# (select count(ml.oid) where ml.state IN(2,5) ) >playpen-# FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id ; >ERROR: Attribute j.id must be GROUPed or used in an aggregate function >playpen=# >playpen=# select j.id, j.created, count(mj.mid), >playpen-# (select count(ml.oid) where ml.state <> 11), >playpen-# (select count(ml.oid) where ml.state IN(2,5) ) >playpen-# FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id >playpen-# group by j.id, j.created; >ERROR: Sub-SELECT uses un-GROUPed attribute ml.oid from outer query >playpen=# >playpen=# drop table j; >DROP >playpen=# drop table mj ; >DROP >playpen=# drop table ml; >DROP ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Checking for table existence
On Fri, Sep 14, 2001 at 06:58:29PM +, 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. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Checking for table existence
> 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
PostgreSQLPrevChapter 48. Server Programming InterfaceNextExamples 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. 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 invocationsPrevHomeNextVisibility of Data ChangesUpProcedur
[SQL] I could do with some guidance
Hi All; Trying to set up a database with postgres. below is what I have so far... In the example I want board to be filled with; Secretary|Goble|David|11/05/1970|280198R How do I do this? Also is what I have so far the best way for this database? Later I will want to write an inferface for it, in C with libpq. Where can I get docs for dummies? /* ### kipistol.sql ### ## Create and fill sarpa details */ create table sarpa ( surname varchar(15) not null, firstname varchar(15) not null, inits varchar(5), paddress varchar(29) not null, pcity varchar(15) not null, pcode smallint not null, haddress varchar(29), hcity varchar(15), hcode smallint, dob date, sex varchar(01) not null, jnr varchar(01) not null, joined date ); create unique index sarpa_idx on sarpa(surname, firstname); copy sarpa from '//home//degoble//code//scripts//sarpa.dat' using delimiters '|'; /* ## Create and fill local details */ create table kiplocal ( surname varchar(15) not null, firstname varchar(15) not null, aka varchar(15), phmain varchar(15) not null, phsecondary varchar(15), phmobile_fax varchar(15), licence_no varchar(07) ); create unique index kiplocal_idx on kiplocal(surname, firstname, aka); copy kiplocal from '//home//degoble//code//scripts//local.dat' using delimiters '|'; /* ## Create and fill expiries details */ create table expiries ( licence_no varchar(07) not null, lic_day smallint, lic_month smallint ); create unique index expiries_idx on expiries(licence_no); copy expiries from '//home//degoble//code//scripts//expiries.dat' using delimiters '|'; /* ## Create a view from sarpa and kiplocal */ create view members as select s.surname, s.firstname, s.dob, l.licence_no fromsarpa s, kiplocal l where s.surname=l.surname and s.firstname=l.firstname; /* ## Create and fill board */ create table board ( title varchar(15) not null ) inherits(members); create unique index board_idx on board(title); commit; /* ### the data files ### */ /* ## sarpa.dat ## */ Goble|David|DEG|Po Box 648|Kingscote|5223|9 Murray street|Kingscote|5223|11/05/1970|M|N|01/12/1997 /* ## local.dat ## */ Goble|David|David|8553 2829|8553 2829|mobile|280198R /* ## expiries.dat ### */ 280198R|30|6 --Regards David. E. Goble goble [AT] kin.net.au http://www.kin.net.au/goble Po Box 648 Kingscote, Kangaroo Island, SA 5223 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Checking for table existence
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.
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 [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small businessesfax 621-2533
and non-profit organizations. San Francisco
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
