PostgreSQL 11 - using format to assign to variable

Question

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$;

@SlavaRozhnev added the full code, but its soo ugly only its own mother could love it — Gofven
– Gofven, Commented Apr 18, 2020 at 18:11

user330315 · Accepted Answer · 2020-04-18 18:33:10Z

3

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);

answered Apr 18, 2020 at 18:33

user330315

Sign up to request clarification or add additional context in comments.

Comments

Avocado · Accepted Answer · 2020-04-18 18:37:35Z

It seems some_var := EXECUTE ... will not work. Additionally, params inside of an EXECUTE statement are not the same as those in the function - you need to supply them to the execute statement.

I have not tested this, but perhaps

-- q declared above as text

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

EXECUTE q INTO ratelimit;

will work? I also removed the parens from the query, those are unnecessary and may the problem themselves.

I have tested the function

CREATE OR REPLACE FUNCTION test_sum (a int, b int)
RETURNS int
AS $$
DECLARE
  q text; 
  result int;
BEGIN
  q := FORMAT ('SELECT %s + %s', $1, $2);
  EXECUTE q INTO result;
  RETURN result;
END
$$ LANGUAGE plpgsql

and that does work. If my suggestion above does not work, perhaps you can use the above as a starting point.

Collectives™ on Stack Overflow

PostgreSQL 11 - using format to assign to variable

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related