https://supabase.com/ logo
#help
Title
# help
a

AirDiamond

10/31/2021, 6:40 PM
Thank you, Gary. That was actually the page I was looking at when I wrote the code. I've tried every combination that I can think of (including using NEW.location.region) but it always returns the same error about whatever not being a known variable.
g

garyaustin

10/31/2021, 6:43 PM
Well at a minimum I think you have to return New for it do what you want. 42.3 example. Your Raises are commented out so do nothing. Yeah I would think you need New. loc.... I assume you have that column in the table.
a

AirDiamond

10/31/2021, 6:48 PM
Yep, same error when using NEW.location.region. The column is in the table.
Also, I commented out the "exception" line when I realized it wasn't doing anything to help me. 🙂
g

garyaustin

10/31/2021, 6:52 PM
may have something to do with . in the column not sure how that might or not impact things. It seems to work for your if though but that could just be it failing the test.
a

AirDiamond

10/31/2021, 7:09 PM
Yeah, I'm at a loss. I updated the code to use: UPDATE location SET NEW.location.region = "Truckee"; This gets accepted, but returns the error: Error: missing FROM-clause entry for table "location"
g

garyaustin

10/31/2021, 7:12 PM
What exactly are the relevant column names and types? Not quite sure what you are up to with the name.name although I've not messed with all the data types.
location is not your table name is it? If so then it is just NEW.address, NEW.region if you have address and region columns. The table is default the one that triggered from.
NEW is referring to the record being inserted so you would not include the table name at all.
a

AirDiamond

10/31/2021, 7:27 PM
Thank you! That got me closer! Now I'm not getting any errors, but the "region" field isn't getting updated. Here's the current code:
Copy code
CREATE OR REPLACE FUNCTION setRegion() RETURNS trigger AS $$

BEGIN
  IF NEW.address LIKE '%Truckee%' 
    THEN NEW.region = 'Truckee';
      --UPDATE location SET NEW.location.region = "Truckee";
    ELSE NEW.region = 'Tahoe';
    -- UPDATE location SET NEW.location.region = "Tahoe";
  END IF;
  RETURN null;  
END;

$$ LANGUAGE plpgsql;
g

garyaustin

10/31/2021, 7:28 PM
return NEW not null
a

AirDiamond

10/31/2021, 7:34 PM
You're a genius! That was it! I also learned that I have to refresh the table editor to see the new value. 🙂