How do I join a string and an int in PostgreSQL?

I have a procedure with an int parameter.

CREATE OR REPLACE PROCEDURE update_retention_policy(id int, days int)
    language plpgsql
AS
$$
    BEGIN
        PERFORM add_retention_policy(('schema_' + id + '.my_hypertable'), days * INTERVAL '1 day', true);
    END
$$;

The syntax for the add_retention_policy function is add_retention_policy('hypertable', INTERVAL 'x days', true). I want to prefix the hypertable with the schema which is always ‘schema_’ and then followed by the id parameter, how do I do that?

Answer

You just need to rewrite the INTERVAL part in your function call as days * INTERVAL '1 day'.

Instead of concatenating strings, you multiply the ‘1 day’ interval by the days param.

EDIT: for the id part, you can just use the || operator, which is the string concatenation operator in Postgres, instead of +. You shouldn’t even need to explicitly cast id to character varying