I have a issue when I try to insert into users table.

DO $$
 DECLARE newId bigint;
 BEGIN 
              INSERT INTO users("email","bio","status","created_by","updated_by")
              VALUES ('[email protected]','❄❄Haocute$$','accepted','1','1')
                RETURNING id INTO newId;

                UPDATE synces SET "version" = "version"   1, updated_at = now(),
                new_id = newId WHERE "old_id" = 98525 and table_name = 'users'; 
                IF NOT FOUND THEN 
                INSERT INTO synces
                (created_at, updated_at, deleted_at, table_name, old_id, new_id, "version")
                VALUES(now(), now(), null, 'users', 98525, newId, 0);
                END IF;
            
 END $$

Thank you for reading my post.

CodePudding user response：

There are two possibilities:

1. use different custom string separator:

   do $my_custom_string_separator$
   begin
     raise notice '❄❄Haocute$$';
   end
   $my_custom_string_separator$;
   NOTICE:  ❄❄Haocute$$
   DO
   

2. You can use extended strings with escaped values:

   do $$
   begin
     raise notice e'❄❄Haocute\$\$';
   end
   $$;
   NOTICE:  ❄❄Haocute$$
   DO
   

CodePudding user response：

I found a solution

DO $do$
 DECLARE newId bigint;
 BEGIN 
              INSERT INTO users("email","bio","status","created_by","updated_by")
              VALUES ('[email protected]','❄❄Haocute$$','accepted','1','1')
                RETURNING id INTO newId;

                UPDATE synces SET "version" = "version"   1, updated_at = now(),
                new_id = newId WHERE "old_id" = 98525 and table_name = 'users'; 
                IF NOT FOUND THEN 
                INSERT INTO synces
                (created_at, updated_at, deleted_at, table_name, old_id, new_id, "version")
                VALUES(now(), now(), null, 'users', 98525, newId, 0);
                END IF;
            
 END $do$