Skip to content
Advertisement

How to use variables in “EXECUTE format()” in plpgsql

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]:

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.

Call:

Using an OUT parameter to simplify overall.
format() syntax explained in the manual.

You could just run the UPDATE instead of a function call:

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:

Call:

Or maybe consider a dedicated SEQUENCE for counting …

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement