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

Question

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!

can't you use EXECUTE IMMEDIATE ? Have a look at section 33.5.3 — cha
– cha, Commented Dec 4, 2014 at 4:35
I'm just curious about your overall design... Based on the clues in your question, this is a table that holds stats and the stored procedure is meant to "grow" a specific stat that is passed in? Is there a reason you decided to go with columns, instead of a lookup table (i.e. just two columns: stat_name, stat_value)? That is much more flexible and conventional than using columns... Plus, no need for dynamic SQL then. — Ruslan
– Ruslan, Commented Dec 4, 2014 at 8:01
@cha: There is no EXECUTE IMMEDIATE in plpgsql. What you link to is part of ECPG (Embedded SQL in C)! — Erwin Brandstetter
– Erwin Brandstetter, Commented Dec 4, 2014 at 19:49

Erwin Brandstetter · Accepted Answer · 2021-01-11 13:09:36Z

14

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 ...

edited Jan 11, 2021 at 13:09

answered Dec 4, 2014 at 9:32

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

2 Comments

House3272 Over a year ago

Thanks! I was not aware of a few things you used. Had no luck trying to look them up, still one thing I'm lost at, the "%1$I" expression. Is there a place I can go to read more about them? Thanks again!

Erwin Brandstetter Over a year ago

@House3272: The 1$ in the middle references the first parameter provided. I added a link to the manual for details on format().

Collectives™ on Stack Overflow

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

1 Answer 1

Alternative design

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Alternative design

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related