Using the polynomial equation for each segment in each channel for a list serial number and time stamps

Question

I am looking to loop over several days for list of serial numbers and start and end time stamps with splitting the channel provided to many segments (each segment is 1800 rows) and use the polynomial equation for each segment. I am looking to run the query below for each combination of serial numbers and time stamps. For example, at the first time, run the query for the 'SN1', '2023-01-01' and '2023-12-31'. I got the error below and I would like you to help me in solving this error.

syntax error line 25 at position 4 unexpected 'WITH'.

syntax error line 25 at position 12 unexpected 'AS'. (line 8)

DECLARE
  -- Define variables for serial number, start timestamp, and end timestamp
  v_serial_number VARCHAR(10);
  v_start_timestamp TIMESTAMP;
  v_end_timestamp TIMESTAMP;

  -- Define a cursor to fetch the serial numbers, start timestamps, and end timestamps
  CURSOR c_serials IS
    SELECT serial_number, start_timestamp, end_timestamp
    FROM table1 
    WHERE serial_number IN ('SN1', 'SN2', 'SN3') -- Specify the desired serial numbers
      AND start_timestamp IN ('2023-01-01', '2023-01-03', '2023-01-06') -- Specify the desired start timestamps
      AND end_timestamp IN ('2023-12-31', '2023-06-01', '2023-06-08'); -- Specify the desired end timestamps

BEGIN
  -- Open the cursor
  OPEN c_serials;

  -- Fetch the first row
  FETCH c_serials INTO v_serial_number, v_start_timestamp, v_end_timestamp;

  -- Loop through the rows
  LOOP
    -- Run your code for each combination of serial number, start timestamp, and end timestamp
    WITH c1 AS (
      SELECT DISTINCT
        ts AS time,
        Column:_engine_temperature_1::float AS engine_temperature_1,
        FLOOR((ROW_NUMBER() OVER (ORDER BY event_ts) - 1) / 1800) + 1 AS segment_id,
        serial_number
      FROM TABLE1 -- Replace with your actual table name
      WHERE serial_number = v_serial_number
        AND event_ts >= v_start_timestamp
        AND event_ts <= v_end_timestamp
    ), c2 AS (
      SELECT
        segment_id,
        engine_temperature_1 - AVG(engine_temperature_1) OVER (PARTITION BY segment_id) AS x,
        engine_temperature_1,
        EXTRACT(SECOND FROM time) AS time_sec,
        POWER(EXTRACT(SECOND FROM time), 2) AS time_sec_sq
      FROM c1
    ), c3 AS (
      SELECT
        segment_id,
        SUM(x) / COUNT(*) AS a,
        SUM(x * time_sec) / SUM(time_sec_sq) AS b,
        AVG(engine_temperature_1) - (SUM(x * time_sec_sq) / SUM(time_sec_sq)) - (SUM(x * time_sec) / SUM(time_sec_sq)) AS c
      FROM c2
      GROUP BY segment_id
    ), c4 AS (
      SELECT
        c1.time,
        c1.segment_id,
        c1.serial_number,
        AVG(c1.engine_temperature_1) AS avg_temp,
        c3.a,
        c3.b,
        c3.c,
        c2.time_sec
      FROM c1
      CROSS JOIN c3
      JOIN c2 ON c1.segment_id = c2.segment_id
      GROUP BY c1.time, c1.segment_id, c1.serial_number, c3.a, c3.b, c3.c, c2.time_sec
    )
    SELECT
      c4.time,
      c4.segment_id,
      c4.serial_number,
      c4.avg_temp,
      c4.avg_temp - (c4.a * (c4.time_sec * c4.time_sec) + c4.b * c4.time_sec + c4) AS residual
     FROM c4
     FETCH c_serials INTO v_serial_number, v_start_timestamp, v_end_timestamp;
     EXIT WHEN c_serials%NOTFOUND;

  END LOOP;
      

Answer 1

that's not how you declare cursors. If you are going to declare a cursor (rather than define it in the body of your code) then the cursor needs to be either declared for a resultset or you use the FOR keyword, not the IS keyword.

This is covered, with examples, in the documentation

FYI Your code is also missing an END to go with your BEGIN