Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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!

like image 573
House3272 Avatar asked Dec 04 '14 03:12

House3272


People also ask

How do I use variables in PostgreSQL?

The general syntax of a variable declaration is: name [ CONSTANT ] type [ COLLATE collation_name ] [ NOT NULL ] [ { DEFAULT | := | = } expression ]; The DEFAULT clause, if given, specifies the initial value assigned to the variable when the block is entered.

How do you assign a selected value to a variable in PostgreSQL?

In PostgreSQL, the select into statement to select data from the database and assign it to a variable. Syntax: select select_list into variable_name from table_expression; In this syntax, one can place the variable after the into keyword.

Which symbol is used for variable assignment in PostgreSQL?

An assignment of a value to a PL/pgSQL variable is written as: variable { := | = } expression ; As explained previously, the expression in such a statement is evaluated by means of an SQL SELECT command sent to the main database engine.


1 Answers

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

like image 101
Erwin Brandstetter Avatar answered Sep 16 '22 21:09

Erwin Brandstetter