There is switch-like sql case: 39.6.2.4. Simple CASE CASE search-expression WHEN expression [, expression [ ... ]] THEN statements [ WHEN expression [, expression [ ... ]] THEN statements ... ] [ ELSE statements ] END CASE;
It should work like C switch statement. Also, for bulk insert, have you tried "for each statement" triggers instead of "for each row"? This would look like a lot of inserts and would not be fast in single-row-insert case, but can give you benefit for huge inserts. It should look like insert into quotes_2012_09_10 select * from new where cast(new.received_time as date) = '2012-09-10' ; insert into quotes_2012_09_11 select * from new where cast(new.received_time as date) = '2012-09-11' ; ... 2012/12/27 Stephen Frost <sfr...@snowman.net> > * Jeff Janes (jeff.ja...@gmail.com) wrote: > > If the main goal is to make it faster, I'd rather see all of plpgsql get > > faster, rather than just a special case of partitioning triggers. For > > example, right now a CASE <expression> statement with 100 branches is > about > > the same speed as an equivalent list of 100 elsif. So it seems to be > doing > > a linear search, when it could be doing a hash that should be a lot > faster. > > That's a nice thought, but I'm not sure that it'd really be practical. > CASE statements in plpgsql are completely general and really behave more > like an if/elsif tree than a C-style switch() statement or similar. For > one thing, the expression need not use the same variables, could be > complex multi-variable conditionals, etc. > > Figuring out that you could build a dispatch table for a given CASE > statement and then building it, storing it, and remembering to use it, > wouldn't be cheap. > > On the other hand, I've actually *wanted* a simpler syntax on occation. > I have no idea if there'd be a way to make it work, but this would be > kind of nice: > > CASE OF x -- or whatever > WHEN 1 THEN blah blah > WHEN 2 THEN blah blah > WHEN 3 THEN blah blah > END > > which would be possible to build into a dispatch table by looking at the > type of x and the literals used in the overall CASE statement. Even so, > there would likely be some number of WHEN conditions required before > it'd actually be more efficient to use, though perhaps getting rid of > the expression evaluation (if that'd be possible) would make up for it. > > Thanks, > > Stephen > -- Best regards, Vitalii Tymchyshyn