How to solve code duplication in the following PostgreSQL query?

Question

I have a table Inputs and a derived table Parameters

CREATE TABLE Configurables
(
  id SERIAL PRIMARY KEY
);

CREATE TABLE Inputs
(
  configurable integer REFERENCES Configurables( id ),
  name text,
  time timestamp,
  PRIMARY KEY( configurable, name, time )
);

CREATE TABLE Parameters
(
  configurable integer,
  name text,
  time timestamp,
  value text,
  FOREIGN KEY( configurable, name, time ) REFERENCES Inputs( configurable, name, time )
);

The following query checks whether a parameter has been changed, or is not present yet, and inserts the parameter with a new value.

QString PostgreSQLQueryEngine::saveParameter( int configurable, const QString& name, const QString& value )
{
  return QString( "\
    INSERT INTO Inputs( configurable, name, time ) \
      WITH MyParameter AS \
      ( \
        SELECT configurable, name, time, value \
        FROM \
        ( \
          SELECT configurable, name, time, value \
          FROM Parameters \
          WHERE (configurable = %1) AND (name = '%2') AND time =  \
          ( \
            SELECT max( time ) \
            FROM Parameters \
            WHERE (configurable = %1) AND (name = '%2') \
          ) \
          UNION \
          SELECT %1 AS configurable, '%2' AS name, '-infinity' AS time, NULL AS value \
        )AS foo \
      ) \
      SELECT %1 AS configurable, '%2' AS name, 'now' AS time FROM MyParameter \
      WHERE time = (SELECT max(time) FROM MyParameter) AND (value <> '%3' OR value IS NULL); \
      \
    INSERT INTO Parameters( configurable, name, time, value ) \
      WITH MyParameter AS \
      ( \
        SELECT configurable, name, time, value \
        FROM \
        ( \
          SELECT configurable, name, time, value \
          FROM Parameters \
          WHERE (configurable = %1) AND (name = '%2') AND time =  \
          ( \
            SELECT max( time ) \
            FROM Parameters \
            WHERE (configurable = %1) AND (name = '%2') \
          ) \
          UNION \
          SELECT %1 AS configurable, '%2' AS name, '-infinity' AS time, NULL AS value \
        )AS foo \
      ) \
      SELECT %1 AS configurable, '%2' AS name, 'now' AS time, '%3' AS value  FROM MyParameter \
      WHERE time = (SELECT max(time) FROM MyParameter) AND (value <> '%3' OR value IS NULL); \
    " ).arg( configurable ).arg( name ).arg( value );
}

How should I best solve the duplication of 2 the MyParameter subqueries?

Any other tips on cleaning up a query like this

Answer 1

You should avoid de-normalized tables. You should use a view for easy overview of Parameter table. It would be much, much easier.

You should only use de-normalized summary table if your view isn't fast enough. But any de-normalized tables should be maintained using triggers, as otherwise you risk that this tables go out of sync.

For this you can create a trigger on Parameters that will upsert into Inputs on insert. If you ever delete or update this columns on Parameters then maintaining Inputs would be complicated. You'd have to delete rows when there's no corresponding row in Parameters - you'd need to maintain counts in Inputs, to know when there's no corresponding row in Parameters. Concurrent insert/update/delete performance will suck, as any change in Parameters will have to block a row in Inputs. This is all ugly and bad - a view is much better solution.