I want to update a column in table stats
with the specific column being a parameter, then return the updated value of that column [only has 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;
As a whole, I’m not even sure if this is best way to do what I want, any suggestion would be appreciated!
Advertisement
Answer
You can do that. Use the INTO
keyword of the EXECUTE
statement.
CREATE OR REPLACE FUNCTION grow(_col text, OUT tmp integer) LANGUAGE plpgsql AS $func$ BEGIN EXECUTE format( 'UPDATE stats SET %1$I = %1$I + 1 RETURNING %1$I' , _col) INTO tmp; END $func$;
Call:
SELECT grow('counter');
Using an OUT
parameter to simplify overall.
format()
syntax explained in the manual.
You could just run the UPDATE
instead of a function call:
UPDATE stats SET counter = counter + 1 RETURNING counter;
There are not many scenarios where the function with dynamic SQL isn’t just needless complication.
Alternative design
If at all possible consider a different table layout: rows instead of columns (as suggested by @Ruslan). 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 maybe consider a dedicated SEQUENCE
for counting …