How to use variables in "EXECUTE format ()" in plpgsql

Question

I want to update a column in the stats table by specifying a specific column as a parameter, and then return the updated value of this column [has only 1 row]:

 CREATE FUNCTION grow(col varchar) RETURNS integer AS $$ DECLARE tmp int; BEGIN tmp := (EXECUTE format( 'UPDATE stats SET %I = %I + 1 RETURNING %I', col, col, col ) ); RETURN tmp; END; 

In general, I'm not even sure that this is the best way to do what I want, any suggestion will be appreciated!

Answer 1

Can you do this. Use the INTO keyword in an EXECUTE statement .

 CREATE OR REPLACE FUNCTION grow(_col text, OUT tmp integer) AS $func$ BEGIN EXECUTE format( 'UPDATE stats SET %I = %I + 1 RETURNING %I' , _col) INTO tmp; END $func$ LANGUAGE plpgsql; 

Call:

 SELECT grow('counter'); 

Using the OUT parameter for simplification in general.
format() syntax described in the manual .

You can simply run UPDATE instead of calling the function:

 UPDATE stats SET counter = counter + 1 RETURNING counter; 

There are not many scenarios where a function with dynamic SQL is not just an unnecessary complication.

Alternative design

If possible, consider another table layout: rows instead of columns (as @Ruslan suggested). Allows any number of counters:

 CREATE TABLE stats ( tag text PRIMARY KEY , counter int NOT NULL DEFAULT 0 ); 

Call:

 UPDATE stats SET counter = counter + 1 WHERE tag = 'counter1' RETURNING counter; 

Or perhaps consider highlighted SEQUENCE for counting ...