Re: [SQL] conversi ms-sql7 vs postgresql 7.3
Greg, > > command in ms-sql 7 can use calculate field (column) balance from id=1 > > to id=4: "update xx set bal=balance=bal+debet-credit" > > You cannot do such a thing in SQL alone: you must use a procedural > language. One way is with plpgsql: You're mistaken, I think (I can't find the original e-mail in this thread) UPDATE totals SET balance = total_bal FROM (SELECT acct_id, (sum(credit) - sum(debit)) as total_bal FROM accounts GROUP BY acct_id) tb WHERE tb.acct_id = totals.acct_id AND totals.acct_id = $selection ... would update a ficticious "totals" table with the sum of credits and debits for a particular account. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] problems with date and interval queries.
Stephan Szabo <[EMAIL PROTECTED]> writes: > interval is the form for an interval literal. If the column is > already an interval, you probably don't need it at all. If you need to > cast it you should do a cast CAST (num_min AS INTERVAL) I don't believe there is a cast from any numeric type to interval. I'd recommend the interval-times-float operator. It should work to write num_min * interval '1 min' This approach has the advantage that it trivially adapts to whatever unit you happen to have the column stated in (seconds, minutes, days, ...) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] order by date desc but NULLs last
Why not try the obvious first? order by gradedtime is null, gradedtime desc; "Ross J. Reedstrom" wrote: > > On Sun, Feb 09, 2003 at 05:29:29PM -0500, A.M. wrote: > > I have a simple query that sorts by descending date but the NULL dates > > show up first. Is there a way I can sort so they come last without > > sorting ascending? > > > > SELECT submittime,score,gradedtime FROM student_gradedmaterial WHERE > > gradedmaterialid=3 and studentid=102 order by gradedtime desc; > > > > submittime | score | gradedtime > > -+---+ > > 2003-01-30 22:56:38 | | > > 2003-01-31 03:42:29 |99 | 2003-02-06 14:21:43.043587 > > > > but what I want is all the graded items first in gradedtime desc and > > NULL afterwards. I do need to keep the NULL score rows. (So I get the > > latest submitted grade for the assignment but also any ungraded > > submission information.) > > You need to ORDER BY a _function_ of the gradedtime column, substituting > an extreme value for NULL. Try this: > > SELECT submittime,score,gradedtime FROM student_gradedmaterial > WHERE gradedmaterialid=3 and studentid=102 order by > coalesce(gradedtime,'-infinity') desc; > > Ross > > ---(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])
[SQL] PL/Pgsql trigger function problem.
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 'DECLAREtable_cols RECORD;attribs VARCHAR;A VARCHAR;BEGINIF 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';
