CODEWITHSUNDEEP
node.jspostgresqlescapingpg-promisedollar-sign
Bertrand Marechal
Bertrand Marechal

Reputation: 53

pg-promise create function containing execute with $ parameter

I am trying to run database creation scripts through pg-promise. The bit that execute the command in nodeJS is as follow :

        this.db.any(sql, [])
            .then((data: any) => {
                resolve(data);
            })
            .catch((error: any) => {
                console.log(sql);
                reject(error);
            });

The functions code might contain one or several $variables used directly inside the functions themselves (that is the reason why I am passing an empty array in the any above). Parameters inside the execute statements are of several different types. an example is a follow :


    CREATE OR REPLACE FUNCTION ze_function(i_a_parameter TEXT, i_a_value INTEGER, i_another_value TEXT) 
    RETURNS integer
    AS $function$
    DECLARE
        v_rows_saved integer := 0;
    BEGIN
        EXECUTE format('UPDATE %I_table
            SET
            the_field = $1
            the_other_field = $2', i_a_parameter)
            USING i_a_value, i_another_value;
        GET DIAGNOSTICS v_rows_saved = ROW_COUNT;
        RETURN v_rows_saved;
    END;
    $function$
    LANGUAGE plpgsql;

and the error it generates is the following : RangeError: Variable $1 out of range. Parameters array length: 0

I tried to find ways escape the dollar sign, with no luck so far. Is it a complete no go, or are there ways of doing things I don't know yet ?

Upvotes: 2

Views: 1146

Answers (1)

vitaly-t
vitaly-t

Reputation: 25840

In pg-promise, in order to execute pre-formatted SQL like that, you need to make sure the query method does not try to format it.

That means passing in either undefined for values, or nothing at all:

this.db.any(sql)

Passing in [] results in method trying to format the SQL using $1, $2, ... format, and hence the error you are getting just as it finds those.

Upvotes: 2

Related Questions