Is there a better way of using db.param with a plpgsql function expecting VARIADIC text[]?

[faenigma]

HI, just discovered this library and I'm really enoying it - I'm leveraging it in an effort I'm performing to modernize an express api (namely by moving it to typescrpit) that's being used on a a DB-first project with a number of plpgsql functions.

I have multiple instances of functions that need to pass in a variadic text array, e.g., mytable.myfunction(VARIADIC _my_input text[]). I'd like to avoid using db.raw, but I'm not seeing how else to pass in something like an array of strings as a parameter.

Currently I'm using the following methodology (plus some pre-validation with zod):

    const mystrings = validQuery.mystrings.map(mystring => `'${mystring}'`).join(',');
    const query = db.sql`
    select * from mytable.myfunction(${db.raw(mystrings)})
    `;

as i need the final format as

   select * from mytable.myfunc('mystring1','mystring2','mystring3')

[jawj]

Interesting question, thanks. Could you give a minimal example of what SQL you're trying to run, and I'll take a look?

[faenigma]

Sure - the functions more or less all follow a format like this -

CREATE OR REPLACE FUNCTION mytable.myfunction(VARIADIC _input_string_array text[])

And that _input_val array is used in a few different contexts, most commonly for an ANY somewhere deep inside of the function -

AND table.column = ANY(_input_string_array)

with pg-promose, we followed the :list syntax , e.g., select * from mytable.myfunc($/input_string_array:list/)

where an array of strings could be provided as the input_string_array

[jawj]

The way I handle this sort of thing within the library is with a utility function mapWithSeparator, which looks like this:

const mapWithSeparator = <TIn, TSep, TOut>(
  arr: readonly TIn[],
  separator: TSep,
  cb: (x: TIn, i: number, a: readonly TIn[]) => TOut
): (TOut | TSep)[] => {

  const result: (TOut | TSep)[] = [];
  for (let i = 0, len = arr.length; i < len; i++) {
    if (i > 0) result.push(separator);
    result.push(cb(arr[i], i, arr));
  }
  return result;
};

You could adapt that to your needs as:

const paramsWithComma = (arr: any[]): (db.Parameter | db.SQLFragment)[] =>
  mapWithSeparator(arr, db.sql`, `, x => db.param(x));

And then use it like:

const
  args = ['a', 'b', 'c'],
  result = await db.sql`SELECT variadicfunction(${paramsWithComma(args)})`.run(pool);

I appreciate the library isn't giving you a lot of help here, but does that work? I guess one thing we could do would be to expose the mapWithSeparator utility function.

[faenigma]

Sorry - just now back to tackling this. That worked perfectly, thanks! And yeah I think that would be a good enough fix, though not sure how often people run into this problem haha.

[jawj]

OK — great. :)

[jawj]

The next version of Zapatos will export mapWithSeparator (https://github.com/jawj/zapatos/commit/609bc9490339f7a1790917b9403dba51d7645089).