Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE oxygen (
  new_id int
, observation_date date
, o2_abs int
, o2_rel int
, temp int
);
INSERT INTO oxygen VALUES (1, '2015-1-1', 1,2,3);

CREATE TABLE oxygen_month_average (
  new_id int
, month text
, o2_abs int
, o2_rel int
, temp int
);
INSERT INTO oxygen_month_average VALUES (1, 'April', 6,7,8);

CREATE TABLE



INSERT 0 1



CREATE TABLE



INSERT 0 1


CREATE OR REPLACE FUNCTION get_observations(_kind    text
                                          , _site_id int
                                          , _var     text)
  RETURNS TABLE (fc json)
  LANGUAGE plpgsql AS
$func$
BEGIN
   CASE _kind
   WHEN 'raw' THEN
      RETURN QUERY
      SELECT json_agg(obs) FROM (
         SELECT observation_date AS date
              , CASE _var
                 WHEN 'o2_abs' THEN o2_abs
                 WHEN 'o2_rel' THEN o2_rel
                 WHEN 'temp'   THEN temp   -- typo?
                END AS value
         FROM   oxygen
         WHERE  new_id = _site_id
         ) AS obs;

   WHEN 'averaged' THEN
      RETURN QUERY
      SELECT json_agg(obs) FROM (
         SELECT month AS month
              , CASE _var
                 WHEN 'o2_abs' THEN o2_abs
                 WHEN 'o2_rel' THEN o2_rel
                 WHEN 'temp'   THEN temp
                END AS value
         FROM   oxygen_month_average
         WHERE  new_id = _site_id
         ) AS obs;
   END CASE;
END
$func$;

CREATE FUNCTION


SELECT * FROM get_observations('raw', 1, 'temp');


fc


[{"date":"2015-01-01","value":3}]



SELECT 1


SELECT * FROM get_observations('averaged', 1, 'o2_rel');


fc


[{"month":"April","value":7}]



SELECT 1