How do I use named Prepared Statements, with variables

[wernermorgenstern]

I am trying to figure out from the Documentation, in how to use Named Prepared Statements using placeholders, and then using/executing them with variables.

For example, in Supabase, we have Function Statements like this:

    get_rows: plv8.prepare(
      'select * from db.table where id = ANY($1) for update skip locked',['text[]']
    )

And then executing it: const rows = statements.get_rows.execute([ids]

Where ids is an array

[porsager]

Postgres.js implicitly creates prepared statements itself, so I'm not sure you even need to? Can you expand on what the purpose is?

[wernermorgenstern]

I did use the prepare: true, but then I got errors about transaction not found.

[porsager]

No, but why don't you just run the query like:

const xs = await sql`
  select
    *
  from db.table 
  where id = ANY(${ ids  }) 
  for update skip locked
`

[lnlife]

No, but why don't you just run the query like:

const xs = await sql`
  select
    *
  from db.table 
  where id = ANY(${ ids  }) 
  for update skip locked
`

Personally I use unsafe()instead of sql because sql is very hard to be wrapped in to a function and pass query parameters, something like my_custom_db_query(table_name,query,args). There is another issue with sql that if there are several variables (maybe 3+) inside sql query, the performance dropped significantly.

[jkomyno]

Postgres.js implicitly creates prepared statements itself, so I'm not sure you even need to? Can you expand on what the purpose is?

If one relies on query builders, they may be constrained to try to fix a text: string and a values: Array<string | number | bigint | boolean | Date | ...> variables into postgres' sql. So, having access to an explicit query parameter interpolator API would be neat :)

[porsager]

Search for sql.unsafe 😉

[tilemanrenovations]

That’s interesting.