[SQL] foreign key, on delete cascade...

2002-10-08 Thread Mathieu Arnold
Hi I found in an old pgsql dump something like : UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" ~* 'TABLE NAME'; inserts BEGIN TRANSACTION; CREATE TEMP TABLE "tr" ("tmp_relname" name, "tmp_reltriggers" smallint); INSERT INTO "tr" SELECT C."relname", count(T."oid") FROM "pg_class"

Re: [SQL] Problems Formulating a SELECT

2002-10-08 Thread Charles Hauser
Richard, Thanks again. > My bad, I didn't test it - for the last one you'll want > > SELECT contig_x_vw.contig_id FROM contig_x_vw ... > > It doesn't matter which id you use (since you want the value in each) but you > will need to tell PG which one you want. No problem, after I sent the e

[SQL] Temporary tables and indexes

2002-10-08 Thread Ludwig Lim
Hi : Are the indices of a temporary table automatically "dropped" together its corresponding temporary table after a database session? ludwig. __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---

Re: [SQL] Problems Formulating a SELECT

2002-10-08 Thread Richard Huxton
On Monday 07 Oct 2002 8:24 pm, Charles Hauser wrote: > I am trying to formulate a SELECT and could use some suggestions. > > From the TABLES below I would like to find ALL contigs which contain the > same clones except that one (or more) has read='y' and the other(s) have > read='x'. > Or stated a

Re: [SQL] Probs "compiling" a function

2002-10-08 Thread Richard Huxton
On Tuesday 08 Oct 2002 3:14 am, [EMAIL PROTECTED] wrote: > Greetings, Having probs just "compiling" this fn and it comes back with a > very stark error msg which is below. I'm using version 7.1.3 > > I run the script as: > \i procedures/zff > > and get the error msg: > psql:procedures/zff:51: ERRO

Re: [SQL] foreign key, on delete cascade...

2002-10-08 Thread Stephan Szabo
On Tue, 8 Oct 2002, Mathieu Arnold wrote: > I found in an old pgsql dump something like : > > UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" ~* 'TABLE NAME'; > > inserts > > BEGIN TRANSACTION; > CREATE TEMP TABLE "tr" ("tmp_relname" name, "tmp_reltriggers" smallint); > INSERT INTO "

Re: [SQL] Problems Formulating a SELECT

2002-10-08 Thread Charles Hauser
Richard, Thanks, a followup. I believe this will not work (novice, so take w/ grain of salt). I tried the following: chlamy_est=> CREATE VIEW contig_x_vw AS SELECT contig_id FROM clone_contig, clone WHERE chlamy_est-> clone_contig.clone_id = clone.clone_id AND read='x'; CREATE chlamy_est=> C

Re: [SQL] Temporary tables and indexes

2002-10-08 Thread Josh Berkus
Ludwig, > Are the indices of a temporary table automatically > "dropped" together its corresponding temporary table > after a database session? I'm kind of surprised that it's possible to index a temporary table. There's not much point in doing so. Yes, the indexes would be dropped as well.

Re: [SQL] [NOVICE] update question

2002-10-08 Thread Josh Berkus
David, I'm sorry, you just don't seem to be at the "self-help" stage. I strongly reccommend that you hire a database consultant to help you. -Josh Berkus DAVID KUCHARSKI <[EMAIL PROTECTED]> wrote: > the result set of the view is 3628 lines. It would be > nice if postgres could do this updat

Re: [SQL] How slow is distinct - 2nd

2002-10-08 Thread Michael Contzen
Bruno Wolff III schrieb: > > On Tue, Oct 01, 2002 at 14:18:50 +0200, > Michael Contzen <[EMAIL PROTECTED]> wrote: > > Here the table: > > > > mc=# \d egal > > Table "public.egal" > > Column | Type | Modifiers > > +-+--- > > i | integer | > > > > mc=# selec

Re: [SQL] Temporary tables and indexes

2002-10-08 Thread Ian Harding
Sure there is! There are queries that benefit from having a temporary table created for a subquery and the temporary table indexed before the join. Since we can't easily return result sets from functions yet, it's not probably used that much, but from within a function, I can see why you mig

Re: [SQL] Problems Formulating a SELECT

2002-10-08 Thread Richard Huxton
On Tuesday 08 Oct 2002 4:04 pm, Charles Hauser wrote: > Richard, > > Thanks, a followup. > > I believe this will not work (novice, so take w/ grain of salt). > > I tried the following: > > chlamy_est=> CREATE VIEW contig_x_vw AS SELECT contig_id FROM clone_contig, > clone WHERE chlamy_est-> clone_

[SQL] Table Rule

2002-10-08 Thread Rudi Starcevic
Hi, I have a Postgresql Rule question I'd love some help with thanks. I have a table, sysmessages, I'd like to keep a journal of. So I create a rule that says on insert or update to this table do insert or update into my sysmessges_log table. My problem is this: sysmessages table has it's own

Re: [SQL] IN, EXISTS or ANY?

2002-10-08 Thread Keith Gray
Josh Berkus wrote: >>But EXISTS is an entirely different animal which is often faster >>... isn't that in the FAQ? There is no reference to EXISTS in the SELECT documentation?? Is this explained somewhere else? -- Keith Gray Technical Services Manager Heart Consulting Services -

Re: [SQL] IN, EXISTS or ANY?

2002-10-08 Thread Josh Berkus
Keith, > >>But EXISTS is an entirely different animal which is often faster > >>... isn't that in the FAQ? > > > There is no reference to EXISTS in the SELECT documentation?? > > Is this explained somewhere else? Hmmm found this using the Index for the online docs: http://www.us.postgr

Re: [SQL] Temporary tables and indexes

2002-10-08 Thread Tom Lane
"Josh Berkus" <[EMAIL PROTECTED]> writes: > I'm kind of surprised that it's possible to index a temporary table. > There's not much point in doing so. Why not? You seem to be equating "temporary" with "small", but I don't see why that must be so. regards, tom lane -

Re: [SQL] Temporary tables and indexes

2002-10-08 Thread Josh Berkus
Tom, > > I'm kind of surprised that it's possible to index a temporary > table. > > There's not much point in doing so. > > Why not? You seem to be equating "temporary" with "small", but I > don't > see why that must be so. Nah. I'm equating "temporary" with "query twice and throw away", whic