Postgres: SQL Error [42883]: ERROR: operator does not exist: uuid = text

Question

Below I have a Postgres query that reverts data in the main table to a specific point in time with the data in the audit table based on INSERT or UPDATE audit_operation.

create or replace function fun(test_val1_input text, test_val3_input text)
returns void
as $functions$
declare test_id text ;
    test_val1 text ;  
    test_val2 text ; 
    test_val3 timestamp;
    test_val4 text ;
declare cur cursor 
for select id, val1 , val2 , val3, val4
    from test_table at 
    where val1 = UUID($1) and val3 > to_timestamp($2, 'YYYY-MM-DD HH24:MI:SS')
    order by val3 desc;
begin   
open cur;

fetch next from cur into test_id, test_val1 , test_val2 , test_val3 , test_val4;

while found
loop

    if (test_val4 = 'INSERT')
    then
        delete from main_table mt where id = test_val1;
    elsif (test_val4 = 'UPDATE')
    then
        delete from main_table mt where id = test_val1;
        with cte
        as 
        (
            select * 
            from test_table at
            where val1 = test_val1 and val3 < test_val3 
            order by val3 desc
            limit 1
        )
        update mt 
        set mt.id = cte.id,
            mt.val1 = cte.val1,
            mt.val2= cte.val2
        from main_table mt
             join cte on cte.val1 = brb.val1; 
    end if;

    fetch next from cur into test_id, test_val1, test_val2, test_val3, test_val4;
end loop;

close cur;
end;
$functions$ language plpgsql;

For reference-

Main table

     id       |         val1           |  val2
--------------+------------------------+---------
31cc5a4f-7a23 | 4d87-ad12-2f78c1c52b7a | data_1
12da6b6a-8b12 | 4d87-ad12-2f78c1c52b7a | data_2
82na1q1a-1b45 | 4d87-ad12-2f78c1c52b7a | data_3

Type of columns in the main_table

id: uuid

val1: uuid

val2: text

Audit table

     id       |           val1         |  val2   |    val3             | val4
--------------+------------------------+---------+---------------------+------------------
31cc5a4f-7a23 | 4d87-ad12-2f78c1c52b7a | data_1  | 2001-09-10 12:02:20 |      INSERT
12da6b6a-8b12 | 4d87-ad12-2f78c1c52b7a | data_2  | 2001-09-10 12:02:20 |      INSERT      
82na1q1a-1b45 | 4d87-ad12-2f78c1c52b7a | data_3  | 2001-09-12 15:12:54 |      INSERT      

Type of columns in the audit_table

id: uuid

val1: uuid

val2: text

val3: timestamp

val4: text

On executing the above SQL function for the following inputs-

select fun('4d87-ad12-2f78c1c52b7a', '2001-09-10 12:02:20')

I'm getting the following error:

SQL Error [42883]: ERROR: operator does not exist: uuid = text

Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

Where: PL/pgSQL function revert_business_rule_days(text,text) line 23 at SQL statement

Instead, I was expecting data in the main table to be reverted at the specified time:

Main table

     id       |         val1           |  val2  
--------------+------------------------+---------
31cc5a4f-7a23 | 4d87-ad12-2f78c1c52b7a | data_1
12da6b6a-8b12 | 4d87-ad12-2f78c1c52b7a | data_2

Kindly help me and let me know where I'm making mistake, I would be really thankful!

Also, do let me know if you need more understanding on this.

Answer 1

From Postgresql documentation:

A UUID is written as a sequence of lower-case hexadecimal digits, in several groups separated by hyphens, specifically a group of 8 digits followed by three groups of 4 digits followed by a group of 12 digits, for a total of 32 digits representing the 128 bits.

PostgreSQL also accepts the following alternative forms for input: use of upper-case digits, the standard format surrounded by braces, omitting some or all hyphens, adding a hyphen after any group of four digits.

You should correct:

• When you create the function its name is fun and not revert_business_rule_days.

• The type of variables v_id and v_pk_id should be UUID and not TEXT. The error occurs in WHERE clause of DELETE statement, because id column type is UUID and variable v_id type is text.

• Your UPDATE query should be something like (your syntax is not correct):

UPDATE main_table mt 
SET id = cte.id,
    pk_id = cte.pk_id,
    col_1= cte.col_1
FROM cte 
WHERE cte.pk_id = mt.pk_id;