PostgreSQL 11 – using format to assign to variable

I have been awake for well beyond my schedule and I have been stuck with this issue for a long time, I don’t even know what I am looking for to solve, but I wish to use format to insert values that I’ll be using for column names, and then executing it… but it keeps giving me errors no matter how much I try changing it :c

Heres the part that im trying to do something that doesnt work, but i think you get the idea what im trying to achieve

    ratelimit := EXECUTE format('(SELECT %I
                      FROM users.ratelimits 
                      WHERE user_id = $2)
                     ', $1);

and heres the full code for the brave

CREATE OR REPLACE FUNCTION users.consume_ratelimit(_name text,__user_id integer)
    RETURNS boolean
    LANGUAGE 'plpgsql'
    VOLATILE
    PARALLEL UNSAFE
    COST 100
AS $BODY$DECLARE
    ratelimit INTEGER;
    reset_timeout timestamptz;
    premium BOOLEAN;
BEGIN
    ratelimit := EXECUTE format('(SELECT %I
                      FROM users.ratelimits 
                      WHERE user_id = $2)
                     ', $1);

    reset_timeout := EXECUTE format('(SELECT %I_refresh 
                      FROM users.ratelimits 
                      WHERE user_id = $2)
                     ', $1);

    premium := (SELECT users.is_premium($2));


    IF premium THEN
        RETURN TRUE;

    ELSIF reset_timeout <= NOW() THEN
        UPDATE users.ratelimits
        SET image_refresh = NOW() + '1 hour'::interval,
            image = DEFAULT
            WHERE user_id = $2;
        RAISE NOTICE 'reset';
        RETURN TRUE;

    ELSE
        IF ratelimit > 0 THEN
            EXECUTE format('UPDATE users.ratelimits
            SET %I = %I - 1
            WHERE user_id = $2', $1, $1);
            RAISE NOTICE 'decrement';
            RETURN TRUE;

        ELSIF ratelimit <= 0 THEN
            RAISE NOTICE 'out of credits';
            RETURN FALSE;

        ELSE
            EXECUTE format('INSERT INTO users.ratelimits(user_id) VALUES ($2)
            ON CONFLICT DO UPDATE SET
                %I = excluded.%I,
                %I_refresh = excluded.%I_refresh', $1, $1, $1, $1);
            RAISE NOTICE 'create';
            RETURN TRUE;

        END IF;
    END IF;
END;$BODY$;

Answer

As documented in the manual you need to use into together with EXECUTE to store the result into a variable. This can handle multiple columns/variables as well, so you only need a single EXECUTE to get both values.

For clarity you should reference parameters by name, not by position.

EXECUTE format('SELECT %I, %I_refresh 
                FROM users.ratelimits WHERE user_id = $1'), 
                _name, _name)
   USING __user_id
   INTO ratelimit, reset_timeout;

Note the $1 inside the string for format() is a parameter placeholder used when the SQL statement is executed, and will be replaced with the value of the variable specified in the USING clause.

Variable assignment is also more efficient without a SELECT:

premium := users.is_premium(__user_id);