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

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(v_pk_id_input text, v_audit_dtime_input text)
returns void
as $functions$
declare v_id text ;
    v_pk_id text ;  
    v_col_1 text ; 
    v_audit_dtime timestamp;
    v_audit_operation text ;
declare cur cursor 
for select id, pk_id , col_1 , audit_dtime , audit_operation 
    from audit_table at 
    where pk_id = UUID($1) and audit_dtime > to_timestamp($2, 'YYYY-MM-DD HH24:MI:SS')
    order by audit_dtime desc;
begin   
open cur;

fetch next from cur into v_id, v_pk_id , v_col_1 , v_audit_dtime , v_audit_operation;

while found
loop

    if (v_audit_operation = 'INSERT')
    then
        delete from main_table mt where id = v_id;
    elsif (v_audit_operation = 'UPDATE')
    then
        delete from main_table mt where id = v_id;
        with cte
        as 
        (
            select * 
            from audit_table at
            where pk_id = v_pk_id and audit_dtime < v_audit_dtime 
            order by audit_dtime desc
            limit 1
        )
        update mt 
        set mt.id = cte.id,
            mt.pk_id = cte.pk_id,
            mt.col_1= cte.col_1
        from main_table mt
             join cte on cte.pk_id = brb.pk_id; 
    end if;

    fetch next from cur into v_id, v_pk_id , v_col_1, v_audit_dtime , v_audit_operation;
end loop;

close cur;
end;
$functions$ language plpgsql;

For reference-

Main table

     id       |         pk_id          |  col_1  
-------------- ------------------------ ---------
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

pk_id: uuid

col_1: text

Audit table

     id       |           pk_id        |  col_1  |    audit_dtime      | audit_operation
-------------- ------------------------ --------- --------------------- ------------------
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

pk_id: uuid

col_1: text

audit_dtime: timestamp

audit_operation: 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       |         pk_id          |  col_1  
-------------- ------------------------ ---------
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.

CodePudding user response：

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;