Prisma•14mo ago•

4 replies

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

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

queryRaw

function (or

executeRaw

executeRaw

if I try and use

executeRawUnsafe

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

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

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}

 UPDATE public.user SET chat_banned_time = NOW() + INTERVAL '${time} MINUTES' WHERE id = ${userId}

; PrismaClientKnownRequestEr...

Raw query failed. Code: `42601`. Message: `db error: ERROR: syntax ...

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 $$

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

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

queryRaw

function (or

executeRaw

executeRaw

if I try and use

executeRawUnsafe

executeRawUnsafe

unsafe

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

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}

 UPDATE public.user SET chat_banned_time = NOW() + INTERVAL '${time} MINUTES' WHERE id = ${userId}

; PrismaClientKnownRequestEr...

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

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

Continue the conversation

Prisma

Similar Threads

Continue the conversation

Prisma

Similar Threads