Re: Performance on Bulk Insert to Partitioned Table

Jeff Janes <jeff.janes@xxxxxxxxx> · Fri, 28 Dec 2012 06:05:17 -0800

On Friday, December 28, 2012, Vitalii Tymchyshyn  wrote:
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.

I had thought that too, but the catch is that the target expressions do not need to be constants when the function is created.  Indeed, they can even be volatile.

CREATE OR REPLACE FUNCTION foo(x integer)
RETURNS integer AS $$
BEGIN
case x
when 0 then return -5; 
when (random()*10)::integer then return 1; 
when (random()*10)::integer then return 2; 
when (random()*10)::integer then return 3; 
when (random()*10)::integer then return 4; 
when (random()*10)::integer then return 5; 

when (random()*10)::integer then return 6; 
when (random()*10)::integer then return 7; 
when (random()*10)::integer then return 8; 
when (random()*10)::integer then return 9; 
when (random()*10)::integer then return 10; 
else return -6;

Cheers,

Jeff