How to access Hasura session_variables in PostgreSQL triggers

Question

I want to limit inserts into a PostgreSQL table department for individual users(business) using a trigger function. Each business should be able to create a maximum of 5 departments. I am trying to access Hasura session_variables also, which contains identifier x-hasura-business-id for identifying the business.

The console shows an error about a non-existing postgres function to, which I don't think I'm using.

The code for the SQL function I am trying to create is

--- Create function to check business's department count
CREATE FUNCTION check_dpt_quota()
RETURNS trigger AS $$
    DECLARE department_count INTEGER;
    DECLARE session_variables JSON;
    
    BEGIN
        session_variables := current_setting('hasura.user', 't');
        
        SELECT count(*) INTO department_count FROM "public"."department"
            WHERE business_id = (session_variables ->> 'x-hasura-business-id')::uuid;
        
        IF department_count > 4 THEN
            RAISE EXCEPTION 'Error: Department Quota Exceeded';
        END IF;
        
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

--- Create trigger to run function before INSERT
CREATE TRIGGER insert_department BEFORE INSERT ON "public"."department" FOR EACH ROW EXECUTE PROCEDURE check_dpt_quota();

And the error I'm getting while creating the SQL function through Hasura console is

[
    {
        "definition": {
            "schema": "public",
            "name": "to"
        },
        "reason": "in function \"to\": no such function exists in postgres : \"to\"",
        "type": "function"
    }
]

I don't know what I'm doing wrong and the error message is not descriptive enough for me to understand. Can someone help me with this code?

Iodine

The table department has a field business_id which is used to signify to which business the department belongs – Iodine

Answer 1

Finally figured it out!

There were two mistakes I made.

• I checked out the Track this option while running the SQL query

  Trigger functions are not to be tracked

• I didn't cast the value current_setting('hasura.user', 't') to type json before assigning it to session_variables

The corrected version of the code will then be (just the modified part)

...
BEGIN
    session_variables := current_setting('hasura.user', 't')::JSON;
...

Iodine