Re: [SQL] "record" datatype - plpgsql
Brian Knox <[EMAIL PROTECTED]> writes: > What I was asking is if there's anyway to use > the NEW record to get a list of the columnnames in it without knowing them > beforehand. Not in plpgsql ... and if you did, you couldn't do anything useful with the names (like access the fields) anyway. I believe you can do it in pltcl though. regards, tom lane ---(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] "record" datatype - plpgsql
I attempted the same thing in pl/pgsql but was unable to find a satisfactory
solution using it. I eventually started using tcl as the procedural language
to get this type of effect. Tcl casts NEW and OLD into arrays in a manner
that makes it possible.
Original post:
Subject: PL/Pgsql Trigger fcuntion issue..
This is suppose to pull all the columns of the table that initiated the
trigger func from the sys catalogs, loop through them and put everything
that has changed between OLD and NEW into a comma delimited string for
input into a log like table for future analysis via middleware (php,perl..,etc).
Here is the problem, OLD.A results in 'old does not have field A', which
is true. I cant get the OLD and NEW record objects to realize that I want
OLD. for the column name instead of an explicit
A as the column name. The only way I can find to make this work is by using
TCL for the procedural language because of the way it casts the OLD and
NEW into an associative array instead of a RECORD object, but by using TCL
I will lose functionallity in the "complete" version of the following function
which has been stripped to show my specific problem so using TCL is currently
not in my list of options. Any insight will be greatly appreciated.
create or replace function hmm() returns TRIGGER as '
DECLARE
table_cols RECORD;
attribs VARCHAR;
A VARCHAR;
BEGIN
IF TG_OP = ''UPDATE'' THEN
FOR table_cols IN select attname from pg_attribute where attrelid =
TG_RELID and attnum > -1 LOOP
A := table_cols.attname;
IF OLD.A != NEW.A THEN --Begin problem
IF attribs != THEN
attribs := attribs || '','' || table_cols.attname || ''='' || OLD.A
|| ''->'' || NEW.A;
ELSE
attribs := table_cols.attname || ''='' || OLD.A || ''->'' || NEW.A;
END IF;
END IF;
END LOOP;
END IF;
RAISE EXCEPTION ''%'', attribs;
RETURN NULL;
END;
' Language 'plpgsql';
1 of my TCL functions (note: a person with good TCL experience may be able
to make this shorter or faster, first thing I ever really did in TCL but it
works fast and reliable on a large database):
create function touch_loggerINSUPD() returns OPAQUE as '
spi_exec "select current_user as tguser"
spi_exec "select relname as tgname from pg_class where relfilenode = $TG_relid"
if {[string equal -nocase $tguser audit] } {return OK }
if {[string equal -nocase $TG_op INSERT] } {
set forins ""
foreach i $TG_relatts {
set forins "$forins^[array get NEW $i]"
}
set themid [lindex [split [array get NEW acid] " "] 1]
spi_exec -array C "INSERT INTO TOUCHLOG (username,tablename,recid,modtime,action,attributes)
values (''$tguser'',''$tgname'',''$themid'',''now'',''$TG_op'',''$forins'')"
}
if {[string equal -nocase $TG_op UPDATE] } {
set forins ""
set toadd ""
foreach i $TG_relatts {
if {[string equal -nocase [array get NEW $i] [array get OLD $i]] == 0}
{
set toadd "[array get OLD $i]-[array get NEW $i]"
set forins "$forins^[string trim $toadd \']"
}
}
set themid [lindex [split [array get NEW acid] " "] 1]
spi_exec -array C "INSERT INTO TOUCHLOG (username,tablename,recid,modtime,action,attributes)
values (''$tguser'',''$tgname'',''$themid'',''now'',''$TG_op'',''$forins'')"
}
return OK
' LANGUAGE 'pltcl';
Brian Knox wrote:
That's not what I was asking. I know I can access the values of each
column using NEW.columnname. What I was asking is if there's anyway to use
the NEW record to get a list of the columnnames in it without knowing them
beforehand.
Brian Knox
On Thu, 29 May 2003, George Weaver wrote:
Hi Brian;
Assuming "NEW" has been declared as foo%rowtype, you can access the
columns thus
NEW.xxx where xxx is the column name
HTH.
George
- Original Message -
From: "Brian Knox" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, May 29, 2003 11:11 AM
Subject: [SQL] "record" datatype - plpgsql
Given a variable of the "record" data type in pl/pgsql, is it possible to
get the names of the columns ( attributes ) of that record?
eg, given record "NEW" for table "foo", is there a way to get information
concerning the columns that make up that record?
Brian Knox
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] SQL Help
Hello, I already tried this same basic question with no response maybe I was too wordy. So here it is simplified what's the best way to write this query? I'm open to using stored procedures, but even then I don't know how I would conditionally populate a resultset (refcursor). Notice that in the first three cases, the _expression_ is the exact same, only the return value is different. This seems inefficient select (case when column1 = column2 then column3 end) as alias1, (case when column1 = column2 then column4 end) as alias2, (case when column1 = column2 then column5 end) as alias3, (case when column6 = column7 then column8 end) as alias4from mytable; Any ideas? Thanks! Do you Yahoo!? Free online calendar with sync to Outlook(TM).
[SQL] SELECT statement within libpq
Hi I'm knew to postgres and I got my first C postgres connection working from the examples in the documentation. In that it does a BEGIN and a DECLARE CURSOR FOR SELECT et.c. This seems a long way to go about getting back data each time. Is it not possible to just do a straight select and not a transaction and a cursor ? Also does anyone have any generic routines for handling results of all types of select. Many thanks David _ STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] SQL Help
On Fri, May 30, 2003 at 08:47:03 -0700, C F <[EMAIL PROTECTED]> wrote: > Hello, > I already tried this same basic question with no response maybe I was too > wordy. So here it is simplified what's the best way to write this query? I'm > open to using stored procedures, but even then I don't know how I would > conditionally populate a resultset (refcursor). Notice that in the first three > cases, the expression is the exact same, only the return value is different. This > seems inefficient > > > select > (case when column1 = column2 then column3 end) as alias1, > (case when column1 = column2 then column4 end) as alias2, > (case when column1 = column2 then column5 end) as alias3, > (case when column6 = column7 then column8 end) as alias4 > from > mytable > ; That seems pretty efficient. I wouldn't expect the repeated column1 = column2 tests to add much overhead. Unless there is more to the story this looks to be what you want to do. ---(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] SQL Help
CF, > select > (case when column1 = column2 then column3 end) as alias1, > (case when column1 = column2 then column4 end) as alias2, > (case when column1 = column2 then column5 end) as alias3, > (case when column6 = column7 then column8 end) as alias4 > from > mytable Given the information you've given us (including your first e-mail), I can't think of a better way to get the output you want than the above. You could work through subselects, etc, but I think that would be even less efficient. One thing you might want to think about is your whole approach to designing this application; it may be that you can simplify your queries by changing your table design. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] CASE returning multiple values (was SQL Help)
On Friday 30 May 2003 4:47 pm, C F wrote: > Hello, > I already tried this same basic question with no response maybe I was > too wordy. I think it's more a case of nobody seeing a better way. > select > (case when column1 = column2 then column3 end) as alias1, > (case when column1 = column2 then column4 end) as alias2, > (case when column1 = column2 then column5 end) as alias3, > (case when column6 = column7 then column8 end) as alias4 You could write a set returning function, but you'd just end up doing the same thing. Can you explain what it is you're trying to acheive - real fields/schemas etc? -- Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] "record" datatype - plpgsql
> > What I was asking is if there's anyway to use the NEW record to > > get a list of the columnnames in it without knowing them > > beforehand. > > Not in plpgsql ... and if you did, you couldn't do anything useful > with the names (like access the fields) anyway. I believe you can > do it in pltcl though. If you're shopping around for pl languages, pl/ruby fits the bill too. Reflective language with dynamic execution. -sc http://moulon.inra.fr/ruby/plruby.html -- Sean Chittenden ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] SQL Help
If your concern is speed, the thing here is that you will have as many records as there are in "mytable", most of them (I think) with NULLs for alias1, alias2, alias3 and alias4. This way, there is no condition to filter any record, so postgreSQL will do a sequential scan over the whole table. If you are ok fetching the records that match and assumming that the all the others don't match, then the following will work: --column1=column2 SELECT column3 AS alias1, column4 AS alias2, column5 AS alias3, NULL AS alias4 FROM myTable WHERE column1=column2 UNION --column6=column7 SELECT NULL AS alias1, NULL AS alias2, NULL AS alias3, column8 AS alias4 FROM myTable WHERE column6=column7 Of course, you will need the necesary indexes. If this didn't give you a hint, please post a message with a link to your original message, so I can get a better idea of what you need. On Friday 30 May 2003 12:47, C F wrote: > Hello, > I already tried this same basic question with no response maybe I was > too wordy. So here it is simplified what's the best way to write this > query? I'm open to using stored procedures, but even then I don't know how > I would conditionally populate a resultset (refcursor). Notice that in the > first three cases, the expression is the exact same, only the return value > is different. This seems inefficient > > > select > (case when column1 = column2 then column3 end) as alias1, > (case when column1 = column2 then column4 end) as alias2, > (case when column1 = column2 then column5 end) as alias3, > (case when column6 = column7 then column8 end) as alias4 > from > mytable > ; > > Any ideas? > Thanks! > > > - > Do you Yahoo!? > Free online calendar with sync to Outlook(TM). ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] CASE returning multiple values (was SQL Help)
Richard Huxton <[EMAIL PROTECTED]> writes: > On Friday 30 May 2003 4:47 pm, C F wrote: >> select >> (case when column1 = column2 then column3 end) as alias1, >> (case when column1 = column2 then column4 end) as alias2, >> (case when column1 = column2 then column5 end) as alias3, >> (case when column6 = column7 then column8 end) as alias4 > Can you explain what it is you're trying to acheive - real > fields/schemas etc? Yeah. I can't help feeling that this problem really means you've chosen a bad database schema. Given the problem as posed, I can only offer one suggestion: you could avoid writing out the case conditions N times by using a sub-select to factor out common subexpressions: select (case when cond1 then column3 end) as alias1, (case when cond1 then column4 end) as alias2, (case when cond1 then column5 end) as alias3, (case when cond2 then column8 end) as alias4 from (select column1 = column2 as cond1, column6 = column7 as cond2, column3, column4, ... from ... rest of query as in original ... ) ss; One should not mistake this for an efficiency improvement, since more than likely the planner will flatten it into the same querytree as the original form. But it might be more readable or easier to code this way. But give us some details about your table setup and why you find yourself needing to do this in the first place. Maybe there's a better design. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] CASE returning multiple values (was SQL Help)
I was afraid someone was going to ask that :) Okay, I'll do my best at explaining where I'm coming from I'm working on a mapping application it is user-configurable. What this means (as it pertains to this disucssion) is that the through a configuration file, the user is able to define the rules that the application will use to determine which geometries *and* attributes to pull from PG at various scales. These 'rules' that the user defines also contain other specifics such as how to symbolize the geometry, how to label the geometry etc. All of these parameters can either be hard coded by the user into the configuration file, or they can define an _expression_ that will be used to dynamically pull it from the database. On top of all of this, we have two more levels of queries. So... at the very top level, the user can define an _expression_ that will determine that everything queried from this table will match these criteria... this is my WHERE clause. Then below this level, various rules can be defined... each rule can have another definition that evaluates into a SQL _expression_. Now, I could make each rule an entirely separate query, but for one, they all share the exact same top level WHERE clause, and two, there could potentially be many many rules which I would think would cause severe performance issues. Let me give you an example... Let's say we're mapping cities of the United States based on population... In other words, I want to symbolize the cities on the map based on population (larger symbol for larger populations, smaller symbol for smaller populations, etc). I also want to show the city names of the larger cities *only*. So, what the client application (client to PostgreSQL) needs is; the city location, which rules evaluate to true, and the city names of those larger cities (defined by a rule). We have a table of cities of the world. So the top level filter (that all rules will share) is, "COUNTRY = 'USA'". Rule 1 says that cities with a population over 1,000,000 will have a large symbol and be labeled with the city name. So the sql could look like this... select longitude, latitude, city_name from city where country = 'USA' and population > 100; ... seems easy enough, but remember we can have an infinite number of rules (not really inifinite, but you get the point). So Rule2 says that cities with a population under 1,000,000 will have a small symbol (note, we do not care about the city name here). So, by itself, the SQL could look like this... select longitude, latitude from city where country = 'USA' and population < 100; Okay, for this simple example, I would have no problem doing two different queries (this example is extremely simplified compared to what is possible/likely). But what if the user wanted to give a different symbol for every population in 100,000 increments? If our range of populations was 100,000 to 5,000,000 that would be 50 queries! Not only would it be 50 queries, but it would be 50 queries using a nearly identical WHERE clause. So I thought it would be more efficient to combine the queries into something like the following... select longitude, latitutde, (case when population > 100 then true else false end) as rule1, (case when population > 100 then city_name end) as label1, (case when population < 100 then true else false end) as rule2 where country = 'USA' ; I could just only concern the SQL with the boolean values for the rules, and return all city names, and let the application simply discard them, but that seems like not a good thing to do for very large resultsets (and again, this is overly simplified, we could have many such columns full of uncessary data being returned). And by the way, that query cannot be written as something like... (case when population > 100 then 'rule1' when population < 100 then 'rule2' end) as rules ... because the rules are NOT mutually exclusive, there can many positives. Anyway, hopefully I didn't leave anything important out. It sounds like there's no obvious solution to avoiding multiple evaluations of the test expressions. The rules are relatively static once the config files are read in, so I could conceivably create stored procedures with a bunch of IF statements at that time. However, I'm not sure if in PG there is a way to dynamically populate the resulting recordset on the fly. I can think of 10 different ways accomplish what I'm trying to do, but hopefully someone has some ideas on what would be the best performing. Sorry if it's information overload, but you tried to answer my questions, so I thought I should at least try to answer yours :) Any thoughts much appreciated. You could write a set returning function, but you'd just end up doing the same thing. Can you explain what it is you're trying to acheive - real fields/schemas etc?-- Richard Huxton Do you Yahoo!? Free online calendar with sync to Outlook(TM).
[SQL] Calculating with the time
Hello All, I am looking for an advice how to do calculation with the time. I do have a column with datatype 'timestamp with time zone'. The value is '2003-03-22 07:53:56-07' for instance. I would like to select it from the table with result of '07:59:59' '07:53:56', so the query should return '00:06:03'. Thank you for your time. DanielD _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Index scan never executed?
> > I guess it's a little unclear what to print for the first number when no > rows are output at all. The code evidently is using the total time spent > in the plan node, but I think it would be at least as justifiable to > print a zero instead. Would you have found that less confusing? Anyone > else have an opinion about whether to change that detail? > No, that makes perfect sense now that it has been explained. > Perhaps an index on project_id would be more helpful, or a two-column > index on (project_id, start_time). As usual, you are in fine form. A two-column index brought the query from 115 sec to 6. Thanks! Chad ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Dynamic views/permissions
I'm trying to permit users access to their own records in a database. A sample would be: create table logins ( login char(8), name char(32), primary key (login) ); When a login is added an SQL user is created simultaneously. Now I want the user to be able to view her own data: create function userid() returns text as 'select current_user::text;' language sql; create view loginview as select * from logins where logins.login=userid(); grant select on loginview to public; [OR grant select on loginview to ...] However, a select * from loginview; doesn't return any rows. Is what I'm trying not possible at all, or am I just trying to implement it wrong? Regards, -- Raju -- Raj Mathur[EMAIL PROTECTED] http://kandalaya.org/ GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F It is the mind that moves ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Dynamic views/permissions
On Friday 30 May 2003 21:31, Raj Mathur wrote: > I'm trying to permit users access to their own records in a database. > A sample would be: > > create table logins > ( > login char(8), > name char(32), > primary key (login) > ); > > When a login is added an SQL user is created simultaneously. Now I > want the user to be able to view her own data: > > create function userid() returns text as 'select current_user::text;' > language sql; > > create view loginview as select * from logins where logins.login=userid(); > > grant select on loginview to public; > [OR grant select on loginview to ...] > > However, a select * from loginview; doesn't return any rows. Is what > I'm trying not possible at all, or am I just trying to implement it > wrong? What happens if you define logins.login as VARCHAR instead of CHAR? See also: http://www.postgresql.org/docs/faqs/FAQ.html#4.14 Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Calculating with the time
Is this what you are looking for? test=# select 'now'::time as test,'2003-05-30 14:51:38-06'::timestamptz as stamp into temp cruft; SELECT test=# select test,stamp,test - stamp::time as diff from cruft; test | stamp | diff -++- 15:09:28.862728 | 2003-05-30 14:51:38-06 | 00:17:50.862728 (1 row) test=# select test,stamp,reltime(test - stamp::time) as diff from cruft; test | stamp | diff -++-- 15:09:28.862728 | 2003-05-30 14:51:38-06 | 00:17:50 (1 row) test=# drop table cruft; DROP TABLE Guy Katka a Daniel Dunajsky wrote: Hello All, I am looking for an advice how to do calculation with the time. I do have a column with datatype 'timestamp with time zone'. The value is '2003-03-22 07:53:56-07' for instance. I would like to select it from the table with result of '07:59:59' – '07:53:56', so the query should return '00:06:03'. Thank you for your time. DanielD ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] join/case
Hi everyone, I'm trying to do a left join on two tables, mainly because data from table 'b' (lists) may or may not exist, and if it doesn't I want results. However, if data from table lists DOES exist, I want to run a conditional on it, and then return data based on whether the conditional is true or false. Basically, I have something like this: Select o.id,o.num_purch,o.program from orders o left join lists l on l.order_id=o.id where o.uid=1 and o.status!='closed' This query would return something like: id | num_purch | program +---+- 1 | 100 | 1 2 | 150 | 2 However, I want to throw an extra conditional in there that says if l.status='processing', then don't return anything. So, I tried: Select o.id,o.num_purch,o.program from orders o left join lists l on l.order_id=o.id and l.status!='processing' where o.uid=1 and o.status!='closed'. Well, that doesn't work, it returns all the data anyway. I'm guessing it's because l.status!='processing' is part of the left join. The trick here is that, like I said, there may be NO data from the lists table, and if not, return everything. If there is data in lists that has the order id in it, check to make sure l.status!='processing'. If it does, don't return it, if it doesn't, return. Thanks for your help, and sorry if I don't make much sense I tend to ramble :) ---(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] join/case
I think, something like this should work: select o.id,o.num_purch,o.program from orders o left join lists l on (l.order_id=o.id) where (l.status is null or l.status!='processing') and o.uid=1 and o.status!='closed'. (l.status is null should take care about the case when there is no matching row for the join)... I hope, it helps... Dima jtx wrote: Hi everyone, I'm trying to do a left join on two tables, mainly because data from table 'b' (lists) may or may not exist, and if it doesn't I want results. However, if data from table lists DOES exist, I want to run a conditional on it, and then return data based on whether the conditional is true or false. Basically, I have something like this: Select o.id,o.num_purch,o.program from orders o left join lists l on l.order_id=o.id where o.uid=1 and o.status!='closed' This query would return something like: id | num_purch | program +---+- 1 | 100 | 1 2 | 150 | 2 However, I want to throw an extra conditional in there that says if l.status='processing', then don't return anything. So, I tried: Select o.id,o.num_purch,o.program from orders o left join lists l on l.order_id=o.id and l.status!='processing' where o.uid=1 and o.status!='closed'. Well, that doesn't work, it returns all the data anyway. I'm guessing it's because l.status!='processing' is part of the left join. The trick here is that, like I said, there may be NO data from the lists table, and if not, return everything. If there is data in lists that has the order id in it, check to make sure l.status!='processing'. If it does, don't return it, if it doesn't, return. Thanks for your help, and sorry if I don't make much sense I tend to ramble :) ---(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 ---(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] join/case
On Fri, 30 May 2003, jtx wrote: > Hi everyone, I'm trying to do a left join on two tables, mainly because > data from table 'b' (lists) may or may not exist, and if it doesn't I > want results. However, if data from table lists DOES exist, I want to > run a conditional on it, and then return data based on whether the > conditional is true or false. > > Basically, I have something like this: > > Select o.id,o.num_purch,o.program from orders o left join lists l on > l.order_id=o.id where o.uid=1 and o.status!='closed' > > This query would return something like: > > id | num_purch | program > +---+- > 1 | 100 | 1 > 2 | 150 | 2 > > > However, I want to throw an extra conditional in there that says if > l.status='processing', then don't return anything. So, I tried: > > Select o.id,o.num_purch,o.program from orders o left join lists l on > l.order_id=o.id and l.status!='processing' where o.uid=1 and > o.status!='closed'. > > Well, that doesn't work, it returns all the data anyway. I'm guessing > it's because l.status!='processing' is part of the left join. > > The trick here is that, like I said, there may be NO data from the lists > table, and if not, return everything. If there is data in lists that > has the order id in it, check to make sure l.status!='processing'. If it > does, don't return it, if it doesn't, return. My first thought was something like: Select o.id,o.num_purch,o.program,from orders o left join lists l on l.order_id=o.id where o.uid=1 and o.status!='closed' and (l.status!='processing' or l.status is null); But that's probably not what you want if there can be multiple rows in lists that refer to the same row in orders. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [PERFORM] [GENERAL] Yet Another (Simple) Case of Index not used
Added to TODO: * Consider using MVCC to cache count(*) queries with no WHERE clause --- Bruce Momjian wrote: > Dennis Gearon wrote: > > from mysql manual: > > - > > "COUNT(*) is optimized to return very quickly if the SELECT retrieves from one > > table, no other columns are retrieved, and there is no WHERE clause. For example: > > > > mysql> select COUNT(*) from student;" > > - > > > > A nice little optimization, maybe not possible in a MVCC system. > > I think the only thing you can do with MVCC is to cache the value and > tranaction id for "SELECT AGG(*) FROM tab" and make the cached value > visible to transaction id's greater than the one that executed the > query, and invalidate the cache every time the table is modified. > > In fact, don't clear the cache, just record the transaction id of the > table modification command so we can use standard visibility routines to > make the cache usable as long as possiible. > > The cleanest way would probably be to create an aggregate cache system > table, and to insert into it when someone does an unqualified aggregate, > and to delete from it when someone modifies the table --- the MVCC tuple > visibility rules are handled automatically. Queries can look in there > to see if a visible cached value already exists. Of course, the big > question is whether this would be a big win, and whether the cost of > upkeep would justify it. > > -- > Bruce Momjian| http://candle.pha.pa.us > [EMAIL PROTECTED] | (610) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup.| Newtown Square, Pennsylvania 19073 > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] join/case
On Fri, May 30, 2003 at 16:03:44 -0700, jtx <[EMAIL PROTECTED]> wrote: > Basically, I have something like this: > > Select o.id,o.num_purch,o.program from orders o left join lists l on > l.order_id=o.id where o.uid=1 and o.status!='closed' > > However, I want to throw an extra conditional in there that says if > l.status='processing', then don't return anything. So, I tried: The straight forward way to do this is do just do what you described. Make the join a subselect (keeping l.status) and then select from that where status <> 'processing. The result looks like: select id, num_purch, program from (select o.id,o.num_purch,o.program,l.status from orders o left join lists l on l.order_id=o.id where o.uid=1 and o.status!='closed') as j where status <> 'processing'; ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
