Kysely•2y ago

How to select from a function with parameters?

I understand that kysely will not natively support stored procedures / functions, and was wondering if there's a pattern for this:

select * from function_name(<parameters>)

select * from function_name(<parameters>)

I created a helper:

export function similaritySearch(
  text: string,
  model: string,
) {
  return sql`similarity_search(${sql.lit(text)}, ${sql.lit(model)})`;
}

export function similaritySearch(
  text: string,
  model: string,
) {
  return sql`similarity_search(${sql.lit(text)}, ${sql.lit(model)})`;
}

But, I'm not exactly sure how I can use that since db.selectFrom() expects a table name.

2 Replies

ohmi•2y ago

https://kysely-org.github.io/kysely/interfaces/Sql.html is this what you're looking for?

const result = await sql<Person[]>`select * from person`.execute(db)

const result = await sql<Person[]>`select * from person`.execute(db)

Sql | kysely

Documentation for kysely

Theo GravityOP•2y ago

Thanks. I think this might work:

export function similaritySearch(text: string, model: string) {
  return sql<SimilaritySearchTable>`similarity_search(${sql.lit(text)}, ${sql.lit(model)})`;
}

export function similaritySearch(text: string, model: string) {
  return sql<SimilaritySearchTable>`similarity_search(${sql.lit(text)}, ${sql.lit(model)})`;
}

await sql<SimilaritySearchRow[]>`SELECT * FROM ${similaritySearch(text, model)}`.execute(db);

await sql<SimilaritySearchRow[]>`SELECT * FROM ${similaritySearch(text, model)}`.execute(db);

Gaming

Programming

How to select from a function with parameters?