0

Considering table1 which have the follow fields: id1 uuid, id2 uuid, value text.

I'm creating a mecanism which I would like to check if after a insert in table1, already exists the same record but with ids inverted. Example:

CREATE OR REPLACE FUNCTION func_chk_new_pair() 
RETURNS trigger AS $$
DECLARE

  P_viewd_profile_id uuid;

  found_uuids CURSOR  FOR
    SELECT id1 FROM table1 WHERE id1 = NEW.id2 and id2 = NEW.id1 and value = 'some_value';

    current_uuid UUID;

BEGIN
  
  OPEN found_uuids;

  -- this insert to log and see if the values are correct works properly
  insert into public.temp_log (log_text) values ('id1 : ' 
  || NEW.id1|| ' - id2: ' || NEW.id2);
  
  LOOP
    FETCH found_uuids INTO current_uuid;
    EXIT WHEN NOT FOUND;

    -- My problem is that my code never enter here
    insert into table2 (id1, value) values (NEW.id1, NEW.value);

  END LOOP;

  CLOSE found_uuids;

  RETURN NEW;

END;
$$ LANGUAGE plpgsql;

Trigger Creation Code

CREATE TRIGGER chk_insert_new_pair

  AFTER INSERT

  ON "table1 "

  FOR EACH ROW

  EXECUTE PROCEDURE func_chk_new_pair();

The above piece of code I developed to be fired within a trigger. Unfortunately I have developed years in Oracle, so I'm strugling with this mindset. I have tried a lot of approaches... but here I'm.

How can I solve this?

I have tried a lot of approachesss: if EXISTS (select 1 FROM table 1 WHERE id1 = new.id1 and id2 = new.id2 and value = new.value)

Using pk constraint exception forcing insert; loop with inner select;

mendesbr
  • 1
  • 4
  • Your indentation is horrible, and the code could be written more concisely, but it is fundamentally correct. If you don't reach the `INSERT`, that means that the query returns no results. – Laurenz Albe Jul 27 '23 at 05:00
  • If the ultimate goal is to prevent duplicate combinations of `id1` and `id2`, then consider creating a unique index to enforce the constraint; e.g., `CREATE UNIQUE INDEX table1_id1_id2_uc ON table1(LEAST(id1, id2), GREATEST(id1, id2));`. – JohnH Jul 27 '23 at 05:29
  • Laurenz is right, that means the query returns no results. Have you every tried to print found_uuids to see? – Umut TEKİN Jul 27 '23 at 08:21
  • Sorry for the identation. Even making a select * from table1 with no where clause, the fetch never worked. That's I'm here asking for helping, cause it's really weird. The purpouse is not to avoid duplicate combinations. It's a kind of mecanism which I'll have two inserts (in different moments) on the table making pairs. The point is on the second insert.. if already exists the first insert with the inverted values, I need to insert in another table. I have update with the code of the trigger creation. Maybe the issue is there. Thank you all in advance. – mendesbr Jul 27 '23 at 11:44
  • Umut TEKİN. Yes, I have tried the print and no value is showed. I'm using SUPABASE.. I don't know if is something related to cache.. But I don't think so. – mendesbr Jul 27 '23 at 12:32

1 Answers1

0

I found the problem.

The issue is not related to postgres, but yes to SUPABASE RLS in tables.

It seems that it's a bug regarding permissions.

My table already was with permission to perform all operations (update, insert, delete and read) for AUTHORIZED/LOGGED sessions.

But for any reason, after create a new RLS read for AUTHORIZED sessions, it works properly.

Thank you all!

mendesbr
  • 1
  • 4