This is my stored procedure:

CREATE OR REPLACE PROCEDURE delete_events(job_id int, config jsonb)
LANGUAGE plpgsql
AS
$$
DECLARE
  l_rec record;
  policy_id integer;
  deactivate_compression_policy_sql text;
  decompress_sql text;
  delete_sql text;
  activate_compression_policy_sql text;
  run_compression_sql text;
BEGIN
  FOR l_rec IN SELECT table_schema, TABLE_NAME
               FROM information_schema.tables
               WHERE TABLE_NAME = 'log_win'
                 AND table_schema LIKE '%org_%'
  LOOP

    policy_id := format('SELECT job_id 
                                FROM timescaledb_information.jobs 
                                WHERE proc_name = ''policy_retention'' 
                                    AND hypertable_schema = ''%I''  
                                    AND hypertable_name = ''log_win''', l_rec.table_schema);
    EXECUTE policy_id;

    deactivate_compression_policy_sql := format('SELECT alter_job(%I, scheduled => false)', policy_id);
    EXECUTE deactivate_compression_policy_sql;

    decompress_sql := format('SELECT decompress_chunk(c, true) FROM show_chunks(''%I.log_win'') c', l_rec.table_schema);
    EXECUTE decompress_sql;

    delete_sql := format('WITH summary AS (
        SELECT time, device_id, ROW_NUMBER() OVER (PARTITION BY device_id
                                                ORDER BY time DESC) AS rank
        FROM %I.log_win
        JOIN %I.device USING (device_id)
    )
    DELETE FROM %I.log_win 
    USING summary
    WHERE summary.rank = 2000 AND log_win.time < summary.time AND summary.device_id = log_win.device_id', l_rec.table_schema, l_rec.table_schema, l_rec.table_schema);
    EXECUTE delete_sql;

    activate_compression_policy_sql := format('SELECT alter_job(%I, scheduled => true)', policy_id);
    EXECUTE activate_compression_policy_sql;

    run_compression_sql := format('CALL run_job(%I)', policy_id);
    EXECUTE run_compression_sql;
  END LOOP;
END
$$;

The issue I have is with the policy_id. The policy id is the id for the tables compression policy. I'm trying to decompress the table so that I can trim the data before compressing it back again. When I run this I get the following error:

ERROR:  invalid input syntax for type integer: "SELECT job_id 
                                                FROM timescaledb_information.jobs 
                                                WHERE proc_name = 'policy_retention' 
                                                AND hypertable_schema = 'org_3326'  
                                                AND hypertable_name = 'log_win'"
CONTEXT:  PL/pgSQL function delete_events(integer,jsonb) line 17 at assignment
SQL state: 22P02

When I run the select: SELECT job_id FROM timescaledb_information.jobs WHERE proc_name = 'policy_retention' AND hypertable_schema = 'org_3326' AND hypertable_name = 'log_win' I get 1092 and it even says that the value is an integer. I don't understand what the issue is here?

CodePudding user response：

format() does not execute an SQL statement, it just constructs a string. You cannot assign a string to an integer variable (unless it can be cast to a number). To execute the query you have to use EXECUTE.

So the proper sequence would be:

EXECUTE format('SELECT job_id 
                FROM timescaledb_information.jobs 
                WHERE proc_name = ''policy_retention'' 
                  AND hypertable_schema = %L  
                  AND hypertable_name = ''log_win''', l_rec.table_schema)
   INTO policy_id;

Note that you have to use the format %L to construct a string literal. Your code was vulnerable to SQL injection.