Interpolated named parameters for function calls

[ferdv]

Postgres supports calling functions using a "named parameter" notation since (it looks like) version 13: 4.3. Calling Functions.

This is a feature request to support named parameters in interpolations when an object is passed as an argument, similarly to inserts and updates.

E.g.,

const params = { name: 'John', age: 34 }
sql`SELECT my_func(${sql(params)});`

would correspond to

SELECT my_func(name => 'John', age => 34);

[porsager]

Yeah, that's really neat :) Good idea!

[ferdv]

Yeah, that's really neat :) Good idea!

Glad you think so! I'm really happy using Postgres.js in a project (thank you!) and am finding myself abstracting more and more on the DB side in stored functions.

I might have a few cycles to have a go at it - unless it is particularly easy for some Javascript veteran familiar with the codebase :)

[oscar6echo]

Would it work "manually" like this ?
I'll have to test.

const params = { name: 'John', age: 34 };
const inputs = Object.entries(params);
const arr = inputs.map(([k, v], i)=> i < inputs.length-1 ? sql`${spq(k)} => ${v}, ` :  sql`${spq(k)} => ${v}`);
// named notation
await sql`SELECT my_func ( ${arr} );`;

I have the same issue and in my case the following does not work.
It is expected ? or I made a mistake somehow ?

const params =  ['John', 34 ];
// ordered args
await sql`SELECT my_func ( ${params} );`;

Any hint ?

[ferdv]

Would it work "manually" like this ? I'll have to test.

const params = { name: 'John', age: 34 };
const inputs = Object.entries(params);
const arr = inputs.map(([k, v], i)=> i < inputs.length-1 ? sql${spq(k)} => ${v}, : sql${spq(k)} => ${v});
// named notation
await sqlSELECT my_func ( ${arr} );;

I have the same issue and in my case the following does not work. It is expected ? or I made a mistake somehow ?

const params = ['John', 34 ];
// ordered args
await sqlSELECT my_func ( ${params} );;

Any hint ?

This will be, of course, best answered by the developers, but from looking into the implementation, my guess is that the raw string parser currently isn't set up to recognize that we're translating stored function arguments and just works in the default "SELECT mode". My approach would be to switch the context of the parser when we are translating arguments to functions and generate a list of arguments from an array or a sequence of named arguments from an object.

[oscar6echo]

Ok but in this issue #1026 it seems they succeed in passing args to an SQL function - after fixing a type problem. I'll continue testing...

[oscar6echo]

Good news !
After some more testing, I can confirm both options do work:

// ordered args
// func_params: (string | number | boolean)[]
const params = func_params.map((e, i) => i < func_params.length - 1 ? sql`${e}, ` : sql`${e}`);
const query = sql`SELECT * FROM my_func( ${params} )`;

// named params
// func_params: {[x: string]: string | number | boolean;}
const _params = Object.entries(func_params).map(([k, v]) => sql`${sql(k)} => ${v}`);
const params = _params.map((e, i) => i < _params.length - 1 ? sql`${e}, ` : sql`${e}`);
const query = sql`SELECT * FROM my_func( ${params} )`;

Now of course if a new helper is created to help build named params from a dictionary of key-values, all the better.
In which case I suspect it would look like the below - and seemingly easy to implement from the above (but I have no idea how the parser would detect this case and apply the above transformation):

// named params helper
// func_params: {[x: string]: string | number | boolean;}
const query = sql`SELECT * FROM my_func( ${sql(func_params)} )`;