어떠냐? 취미로 보기엔 약간 빡쎄냐?



CREATE OR REPLACE FUNCTION immutable_put(uid INTEGER, json_obj JSON, force BOOLEAN DEFAULT false) RETURNS VOID AS $$

  DECLARE


  deactivated BOOLEAN;


  BEGIN


  IF NOT force THEN

    SELECT true INTO deactivated FROM

           (SELECT * FROM Immutable_JSON_Record

                 WHERE id = uid AND data ? 'active?'

                 ORDER BY ctime DESC LIMIT 1) AS active_record

    WHERE '{"active?": false}'::JSONB <@ active_record.data;

  END IF;


  IF (NOT deactivated OR deactivated IS NULL) THEN

     INSERT INTO Immutable_JSON_Record (id, data) VALUES (uid, json_obj::JSONB);

  END IF;


  END;

$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION immutable_delete(uid INTEGER) RETURNS VOID AS $$

  DECLARE


  deactivated BOOLEAN;


  BEGIN


  SELECT true INTO deactivated FROM

         (SELECT * FROM Immutable_JSON_Record

                 WHERE id = uid AND data ? 'active?'

                 ORDER BY ctime DESC LIMIT 1) AS active_record

  WHERE '{"active?": false}'::JSONB <@ active_record.data;


  IF (NOT deactivated OR deactivated IS NULL) THEN

     INSERT INTO Immutable_JSON_Record (id, data) VALUES (uid, '{"active?": false}'::JSONB);

  END IF;


  END;

$$ LANGUAGE 'plpgsql';

 

CREATE OR REPLACE FUNCTION immutable_get(table_name TEXT, uid INTEGER) RETURNS JSON AS $$

  DECLARE


  col TEXT;

  subselects TEXT = 'SELECT row_to_json(t) from (SELECT id, ctime';

  keys TEXT[];

  result_json JSON;

  

  BEGIN 


  EXECUTE 'SELECT immutable_keys FROM ' || table_name || ' where id = ' || uid

  INTO keys;


  FOREACH col IN ARRAY keys

  LOOP


      subselects = subselects || ', (SELECT data->>''' || col || ''' FROM Immutable_Json_Record WHERE data ? ''' || col || ''' order by ctime desc limit 1) as "' || col || '"';

  END LOOP;

  

  subselects = subselects || ' FROM ' || table_name || ') t';

--  RAISE NOTICE '*** %', subselects;

  EXECUTE subselects into result_json;

  

  RETURN result_json;


  END;

$$ LANGUAGE 'plpgsql';