1

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?

IFE
  • 21
  • 1
  • It looks to me like you just need to add in escaped single quotes: `SUBSTR(numprel, 1, 2) != ''' || ANNEE_AA ||''' RETURNING`. You could also use the built in function: `quote_literal(ANNEE_AA)`. And of course, you could always convert your string delimiters from `'` to `$$`, which would allow you to put in unescaped single quotes. – Chris Hep Feb 06 '20 at 16:20
  • I would also just mention that concatenating a parameter into a string isn't the safest option, as it opens you to injection attacks. I would recommend at least wrapping each table name with `quote_ident()`. – Chris Hep Feb 06 '20 at 16:22

1 Answers1

0

Use format to make things easier:

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 := format('CREATE TABLE %I AS TABLE %I WITH NO DATA', table_ori || '_' || annee_millesime, table_ori);
    cmd_drop        varchar := format('DROP TABLE IF EXISTS %I ',  table_ori || '_' || annee_purge);
    cmd_copie_suppr varchar := format(
        'WITH tmp AS ( 
            DELETE FROM %I 
            WHERE SUBSTR(numprel, 1, 2) <> %L 
            RETURNING *
         ) 
         INSERT INTO %I 
         SELECT * FROM tmp', table_ori, annee_aa, table_ori || '_' || annee_millesime);
BEGIN
    execute cmd_create;
    execute cmd_copie_suppr;            
    execute cmd_drop;
END;
$$ LANGUAGE plpgsql;

%I inserts an identifier (properly double-quoted if necessary) and %L inserts a character literal with single quotes (and escaped embedded single quotes if necessary).