sql - Simpify PL/pgSQL function with lots of if-else statements and JSONification -

i have pl/pgsql function performs quite similar queries (the thing have different column names) , converts output json object.

create or replace function get_observations(kind varchar, site_id integer, var varchar) returns table (fc json) $func$ begin     if kind = 'raw'         if var = 'o2_abs'             return query select array_to_json(array_agg(row_to_json(obs))) (                 select observation_date date, o2_abs value oxygen new_id = site_id) obs;         elsif var = 'o2_rel'             return query select array_to_json(array_agg(row_to_json(obs))) (                 select observation_date date, o2_rel value oxygen new_id = site_id) obs;         elsif var = 'temp'             return query select array_to_json(array_agg(row_to_json(obs))) (                 select observation_date date, t value oxygen new_id = site_id) obs;         end if;     elsif kind = 'averaged'         if var = 'o2_abs'             return query select array_to_json(array_agg(row_to_json(obs))) (                 select month month, o2_abs value oxygen_month_average new_id = site_id) obs;         elsif var = 'o2_rel'             return query select array_to_json(array_agg(row_to_json(obs))) (                 select month month, o2_rel value oxygen_month_average new_id = site_id) obs;         elsif var = 'temp'             return query select array_to_json(array_agg(row_to_json(obs))) (                 select month month, t value oxygen_month_average new_id = site_id) obs;         end if;     end if; end; $func$ language plpgsql; 

the body of function contains lots of repeated code. see several methods improve it, don't know if pl/pgsql allows these tricks:

1. store obs query result in intermediate variable , convert json array_to_json(array_agg(row_to_json(obs))) in end of function.
2. cast var varchar column name definition in query in order avoid of if/else statements;

postgresql server version 9.3.6.

table schemas:

oxygen=# \d+ oxygen table "public.oxygen"          column          |  type   |                      modifiers       (...) -------------------------+---------+----------------------------------------------------  old_id                  | text    | not null  observation_date_string | text    | not null  t                       | real    | not null  o2_abs                  | real    | not null  o2_sat                  | real    |  o2_rel                  | real    |  observation_date        | date    |  new_id                  | integer |  id                      | bigint  | not null default nextval('oxygen_id_seq'::regclass) indexes:     "oxygen_pkey" primary key, btree (id) foreign-key constraints:     "oxygen_new_id_fkey" foreign key (new_id) references unique_geoms(new_id)     "oxygen_old_id_fkey" foreign key (old_id) references location(old_id)   oxygen=# \d+ oxygen_month_average table "public.oxygen_month_average"  column |       type       |                             modifiers        (...) --------+------------------+-------------------------------------------------------------------  new_id | integer          |  month  | integer          |  t      | double precision |  o2_abs | double precision |  o2_rel | double precision |  id     | bigint           | not null default nextval('oxygen_month_average_id_seq'::regclass) indexes:     "oxygen_month_average_pkey" primary key, btree (id) 

there another, efficient way simplify of it: use case statement:

create or replace function get_observations(      _kind    text    , _site_id int    , _var     text)   returns table (fc json) $func$ begin    case _kind     when 'raw'       return query       select json_agg(obs) (          select observation_date date               , case _var                  when 'o2_abs' o2_abs                  when 'o2_rel' o2_rel                  when 'temp'   t                 end value            oxygen           new_id = _site_id          ) obs;     when 'averaged'       return query       select json_agg(obs) (          select month month               , case _var                  when 'o2_abs' o2_abs                  when 'o2_rel' o2_rel                  when 'temp'   t                 end value            oxygen_month_average           new_id = _site_id          ) obs;    end case; end $func$  language plpgsql; 

sql fiddle.

• two distinct queries remain, because based on different tables. fold too, need dynamic sql execute, wouldn't shorter , typically less efficient ...

• a "simple" case in case (as opposed "switched" case). note use 2 independent implementations: outer case plpgsql control structure, inner case sql command. related:

  • simplify nested case when statement

• you can simplify further json_agg() @igor suggested: array_to_json(array_agg(row_to_json(obs))) -> json_agg(obs)