MagicStack / asyncpg

A fast PostgreSQL Database Client Library for Python/asyncio.
Apache License 2.0
6.99k stars 404 forks source link

Error in responses with functions (do $$) #1027

Closed wrld-builder closed 1 year ago

wrld-builder commented 1 year ago

Why in the responses that format type be-like:

await connection.execute('''
   DO $$
   BEGIN
    IF NOT EXISTS (SELECT chat_id FROM users WHERE chat_id = $1) THEN
            INSERT INTO users (user_id, chat_id, messages_count, balance) VALUES ($2, $3, 0, 0);
          END IF;
   END;
   $$''', user_id, user_id, user_id)

I have an error: (traceback)

asyncpg.exceptions._base.InterfaceError: the server expects 0 arguments for this query, 3 were passed

But in simple responses (non-function with do $$ statements) all code working correctly. How can I solve my issue?

elprans commented 1 year ago

Not all PostgreSQL queries support arguments. The DO block is one of those that doesn't. The (full) error message explicitly points this out.

wrld-builder commented 1 year ago

Yes, I guess. All PostgreSQL queries by this type I can change on constructions format by CONFLICT .. ON ... It solves my issue. Thank you!