I (still) ask for your help. I have a PLPGSQL script whose skeleton is good. I have 6 scripts on this basis that do the job properly. However, I have a slightly more complex 7th script.
CREATE or replace FUNCTION maFonction(table_ori varchar, annee_millesime varchar, annee_purge varchar) RETURNS void AS $$
DECLARE
MOIS_MM varchar := to_char(current_timestamp, 'MM');
ANNEE_AAAA varchar := to_char(current_timestamp, 'YYYY');
ANNEE_AA varchar := to_char(current_timestamp, 'YY');
cmd_create varchar := 'CREATE TABLE ' || table_ori || '_' || annee_millesime || ' AS TABLE ' || table_ori || ' WITH NO DATA;';
cmd_drop varchar := 'DROP TABLE IF EXISTS ' || table_ori || '_' || annee_purge ||';';
cmd_copie_suppr varchar := 'WITH tmp AS ( DELETE FROM ' || table_ori || ' WHERE SUBSTR(numprel, 1, 2) != ' || ANNEE_AA ||' RETURNING *) INSERT INTO ' || table_ori || '_' || annee_millesime || ' SELECT * FROM tmp ;';
BEGIN
execute cmd_create;
execute cmd_copie_suppr;
execute cmd_drop;
END;
$$ LANGUAGE plpgsql;
As you can see, in my SQL query, I use a WHERE condition. However, the latter necessarily requires ' (quote) to take into account the value. But this value is for me a variable. Here is the error I get with the above code
psql:./myscript.sql:66: ERROR: operator does not exist: text <> integer
LINE 1: ...schema.table WHERE SUBSTR(numprel, 1, 2) != 20 RETU...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY: WITH tmp AS ( DELETE FROM schema.table WHERE SUBSTR(numprel, 1, 2) != 20 RETURNING *) INSERT INTO dschema.table_2019 SELECT * FROM tmp ;
CONTEXT: PL/pgSQL function mafonction(character varying,character varying,character varying) line 20 at EXECUTE statement
How to make so that this variable ANNEE_AA is well valued AND between ' (quote) to have a correct request?