[SQL] Re: how to use record type
I have encountered this problem (in a different context), and could not find a way to insert entire rows/records in the way that you appear to want. But it worked fine if I INSERTed explicitly, like: INSERT INTO table VALUES (OLD.field1, OLD.field2, ..., OLD.fieldn); That should work fine inside a plpgsql function. - Original Message - From: "Horst Herb" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, August 16, 2001 5:24 PM Subject: Fwd: how to use record type > I have difficulties understanding how to use variable of "record" or "row" > type. How do I actually insert the variables OLD or NEW or a record type into > a table from within a trigger? > > Like doing the following: > > drop table th1; > create table th1( > id serial, > text text ); > > drop table th_audit; > create table th1_audit( > ts timestamp default now() > ) inherits(th1); > > drop function thaudit(); > create function thaudit() returns opaque as ' > begin > -- I want to insert OLD into th_audit - how do I do this??? > return NEW; > end; ' language 'plpgsql'; > > drop trigger ta on th1; > create trigger ta before delete or update on th1 > for each row execute procedure thaudit(); > > Reagrds, > Horst > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Query Approach and performance
Hey everyone, On average, are multiple simple queries better performance-wise than joins? i.e. select A.col1 from table1 A select B.col2 from table2 B where B.col1 = A.col1 etc vs select A.col1, B.col2 from table1 A, table2 B where B.col1 = A.col1 Are joins better for small/large numbers of tables? Is there a diff? My approach to date has been to keep queries as simple as possible, and when I see a need for complicated joins, I create a view and then do simple queries against that. Does pg cache queries like Oracle does so that repeated queries don't need to go through the compile phase and run faster? Is this configurable? Thanks, Morgan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Nested JOINs - upgrade to 7.1.2
I think Tom was right and the problem with nested joins is caused by the outdated installation of my PostgreSQL. So, I am trying to upgrade to 7.1.2 and when I use: pg_dumpall -o > file.bac I get an error saying: dumpRules(): SELECT failed for table setmedias. Explanation from backend: 'ERROR: cache lookup of attribute 5 in relation 23945 failed" As I understand setmedias table corresponds to relation 23945, but I dropped it a long time ago. How should remove this relation pointer? thanks, Oleg Tom Lane wrote: > "Josh Berkus" <[EMAIL PROTECTED]> writes: > > Second, you can't alias a JOINed set of tables; > > Actually you can, according to my reading of SQL92: > > ::= > [ [ AS ] > [] ] > | [ AS ] > [] > | > > ::= > > ::= > > ::= > > ::= > > | > > ::= > > | > | > > ::= >[ NATURAL ] [ ] JOIN > [ ] > > So a parenthesized is a and may > be followed by [ AS ] . > > 7.1 gets this right. 7.0's very preliminary implementation of JOIN > syntax did not. (It had a number of other bugs too, IIRC.) > > The spec's grammar is horribly badly written: it's ambiguous whether the > parentheses should be parsed as part of a second-level > which would imply that a correlation name would NOT be accepted. > It took a fair amount of work to derive a grammar that was unambiguous > and still accepted everything... > > regards, tom lane ---(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] Nested set model
Hi, I'm trying the "nested set model" to handle a tree structure in a database (reference: http://www.dbmsmag.com/9603d06.html). It has many advantages if you want to select all nodes above or below a specific node (recursive calls aren't necessary), but when you want to select only the first generation under a node, the query I found was a lot more complex than it would be if using the traditional adjacency model. Considering this example: CREATE TABLE skill ( keyINTEGER NOT NULL, name VARCHAR(50) NOT NULL, left_n INTEGER NOT NULL, right_nINTEGER NOT NULL, PRIMARY KEY (key), CHECK (left_n > 0 AND right_n > left_n) ); insert into skill values (1 , 'Skills' , 1, 30); insert into skill values (2 , 'Computing' , 2, 29); insert into skill values (3 , 'Programming', 3, 10); insert into skill values (4 , 'C++', 4, 5); insert into skill values (5 , 'Java' , 6, 7); insert into skill values (6 , 'Prolog' , 8, 9); insert into skill values (7 , 'Database' , 11, 18); insert into skill values (8 , 'Oracle' , 12, 13); insert into skill values (9 , 'PostgreSQL' , 14, 15); insert into skill values (10, 'Solid' , 16, 17); insert into skill values (11, 'Design' , 19, 28); insert into skill values (12, 'CorelDraw' , 20, 21); insert into skill values (13, 'Illustrator', 22, 23); insert into skill values (14, 'Photoshop' , 24, 25); insert into skill values (15, 'The Gimp' , 26, 27); How could we select, for example, only the nodes immediately under "computing"? The only way I could do it was using: select son.key, son.name, son.left_n from skill son, skill parent where parent.key = 2 and son.left_n between parent.left_n and parent.right_n and son.key <> parent.key and son.key not in (select son_descendents.key from skill parent, skill son, skill son_descendents where parent.key = 2 and son.left_n between parent.left_n and parent.right_n and son.key <> parent.key and son_descendents.left_n between son.left_n and son.right_n and son.key <> son_descendents.key) order by son.left_n ; Isn't there an easier way to achieve this?? With the usual adjacency model the query would look trivial! Something like: select son.key, son.name from skill son where son.parent = 2 ; Well, thanks in advance! -- Renato Sao Paulo - SP - Brasil [EMAIL PROTECTED] ---(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] Function define question
Hello all, Is it possible to define a function to pass parameter like this? function( parameter ) but not function( 'parameter' ) Regards Bill ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
