How can I access my public.users.primary_currency value correctly?

[Marigno]

I'm trying to create a trigger/function that runs an Edge Function. Everything is great but for the life of me I cannot get it to get the data from public.users.primary_currency

This is my code:

`CREATE OR REPLACE FUNCTION fetch_exchange_rate_on_insert()
RETURNS TRIGGER AS $$
DECLARE
user_primary_currency TEXT;
existing_rate RECORD;
BEGIN
-- Log the NEW.user_id to ensure it's being passed correctly
RAISE LOG 'NEW.user_id: %', NEW.user_id;

-- Fetch the primary_currency from the users table
SELECT users.primary_currency INTO user_primary_currency
FROM users
WHERE users.id = NEW.user_id;

-- Log the fetched primary_currency
RAISE LOG 'Fetched primary_currency: %', user_primary_currency;

-- If no primary_currency is found, log an error
IF user_primary_currency IS NULL THEN
RAISE EXCEPTION 'No primary_currency found for user_id: %', NEW.user_id;
END IF;

-- Check if the account currency is different from the user's primary_currency
IF NEW.currency != user_primary_currency THEN
-- Log the currency check
RAISE LOG 'Account currency % is different from primary_currency %', NEW.currency, user_primary_currency;

-- Check if an exchange rate already exists for the coin and primary_currency
SELECT * INTO existing_rate
FROM exchange_rates
WHERE coin = NEW.currency AND converted_to = user_primary_currency;

-- Log the existing rate check
IF FOUND THEN
  RAISE LOG 'Exchange rate already exists for % to %', NEW.currency, user_primary_currency;
ELSE
  -- If no existing rate is found, perform the HTTP POST request
  RAISE LOG 'No existing exchange rate found for % to %. Performing HTTP POST request.', NEW.currency, user_primary_currency;
  PERFORM
    http_post(
      'https://ierodjatqncoffemhlcg.functions.supabase.co/fetchExchangeRates',
      json_build_object('currency', NEW.currency, 'converted_to', user_primary_currency, 'type', NEW.type)::text
    );
END IF;

ELSE
-- Log if no exchange rate is needed
RAISE LOG 'No exchange rate needed as account currency % is the same as primary_currency %', NEW.currency, user_primary_currency;
END IF;

RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER fetch_exchange_rate_trigger
AFTER INSERT ON accounts
FOR EACH ROW
EXECUTE FUNCTION fetch_exchange_rate_on_insert();
`

If I do this:

SELECT public.users.primary_currency FROM public.users WHERE public.users.id = 'your_user_id';

I can get the correct value "usd" from my database. However, my code above always exits on the first RAISE EXCEPTION because there is no user_primary_currency, leading me to believe I'm doing this wrong:

-- Fetch the primary_currency from the users table SELECT users.primary_currency INTO user_primary_currency FROM users WHERE users.id = NEW.user_id;

However, the schema is correct, permission seems to be correct since I'm running as the postgre role. What's wrong? Some help would be appreciated

[Marigno]

I simply had to do

ALTER FUNCTION fetch_exchange_rate_on_insert() SECURITY DEFINER;