P
Prisma•2w ago
Kazaz

queryRaw plsql function failed Code: `42601`. Message: `ERROR: syntax error at or near "$1"`

Hi everyone, I'm trying to call a function in my Postgres DB with this signature: 
CREATE OR REPLACE FUNCTION main.create_brand_global_search_mv(brand_id TEXT) RETURNS TEXT AS $$
CREATE OR REPLACE FUNCTION main.create_brand_global_search_mv(brand_id TEXT) RETURNS TEXT AS $$
It gets the brand ID as a variable. Using Prisma I'm not able to call it no matter what I do: queryRaw executeRaw queryRawUnsafe executeRawUnsafe (this is in our tests so no fear of SQL injection) Here is an example: 
const sql = `SELECT main.create_brand_global_search_mv('${brand.id}');`;
await prismaClient.$queryRawUnsafe(sql);
const sql = `SELECT main.create_brand_global_search_mv('${brand.id}');`;
await prismaClient.$queryRawUnsafe(sql);
brand.id is a UUID string. I get this error all the time with all approaches: 
Invalid `prisma.$queryRaw()` invocation:

Raw query failed. Code: `42601`. Message: `ERROR: syntax error at or near "$1"`
Invalid `prisma.$queryRaw()` invocation:

Raw query failed. Code: `42601`. Message: `ERROR: syntax error at or near "$1"`
Even when I use the unsafe approach the error seems to happen in the queryRaw function (or executeRaw if I try and use executeRawUnsafe). I also tried using Prisma.raw as was suggested in some Github repo and it didn't work: https://github.com/prisma/prisma/issues/13162#issuecomment-1675434494 How can I make it work? I assumed that I could run everything I wanted with the unsafe approaches but seems like it's not working. It is worth mentioning that if I take the SQL string and run it directly on the DB it does work. Prisma version: 5.22.0
GitHub
Raw query failed. Code: 42601. Message: `db error: ERROR: syntax ...
Bug description const time = 5 await this.prisma.$executeRaw UPDATE public.user SET chat_banned_time = NOW() + INTERVAL '${time} MINUTES' WHERE id = ${userId} ; PrismaClientKnownRequestEr...
3 Replies
Prisma AI Help
Prisma AI Help•2w ago
You chose to debug with a human. They'll tinker with your query soon. If you get curious meanwhile, hop into #ask-ai for a quick spin!
Kazaz
KazazOP•2w ago
I also tried everything mentioned here by the AI: https://discord.com/channels/937751382725886062/1339147283022876673/1339147286353281034 Tried it all before it suggested it. Still getting the same issue :/ Event this fails with the same issue: 
await prismaClient.$queryRawUnsafe(
    `SELECT main.create_brand_global_search_mv('123-dsds-dsds')`
  );
await prismaClient.$queryRawUnsafe(
    `SELECT main.create_brand_global_search_mv('123-dsds-dsds')`
  );
And this with castings: 
await prismaClient.$queryRawUnsafe(
    `SELECT main.create_brand_global_search_mv('123-dsds-dsds'::text) as mv_name`
  );
await prismaClient.$queryRawUnsafe(
    `SELECT main.create_brand_global_search_mv('123-dsds-dsds'::text) as mv_name`
  );
RaphaelEtim
RaphaelEtim•2w ago
Hi @Kazaz Are you still facing issues with this?

Did you find this page helpful?