PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – When to use `EXECUTE` in a PL/pgSQL function?

I created person table, then inserted 2 rows into it as shown below:

CREATE TABLE person (
  id INT,
  name VARCHAR(20),
  age INT
);

INSERT INTO person (id, name, age) 
VALUES (1, 'John', 27), (2, 'David', 32);

Then with an EXECUTE statement, I created my_func() with my_age and my_id parameter as shown below:

CREATE FUNCTION my_func(my_age INT, my_id INT) RETURNS VOID AS $$
BEGIN                -- ↑↑↑↑↑↑      ↑↑↑↑↑
  EXECUTE 'UPDATE person SET age = $1 WHERE id = $2' USING my_age, my_id;
END;
$$ LANGUAGE plpgsql;

Or:

CREATE FUNCTION my_func(my_age INT, my_id INT) RETURNS VOID AS $$
BEGIN
  EXECUTE 'UPDATE person SET age = $1 WHERE id = $2' USING $1, $2;
END;
$$ LANGUAGE plpgsql;

Then, calling my_func() could update age of David to 56 as shown below:

postgres=# SELECT my_func(56, 2);
 my_func
---------

(1 row)

postgres=# SELECT * FROM person;
 id | name  | age
----+-------+-----
  1 | John  |  27
  2 | David |  56
(2 rows)

Next without an EXECUTE statement, I created my_func() with my_age and my_id parameter as shown below:

CREATE FUNCTION my_func(my_age INT, my_id INT) RETURNS VOID AS $$
BEGIN                -- ↑↑↑↑↑↑      ↑↑↑↑↑
  UPDATE person SET age = my_age WHERE id = my_id;
END;
$$ LANGUAGE plpgsql;

Or:

CREATE FUNCTION my_func(my_age INT, my_id INT) RETURNS VOID AS $$
BEGIN
  UPDATE person SET age = $1 WHERE id = $2;
END;
$$ LANGUAGE plpgsql;

Then, calling my_func() could update age of David to 56 as shown below:

postgres=# SELECT my_func(56, 2);
 my_func
---------

(1 row)

postgres=# SELECT * FROM person;
 id | name  | age
----+-------+-----
  1 | John  |  27
  2 | David |  56
(2 rows)

So, what is the difference between the function with and without an EXECUTE statement?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    With an EXECUTE statement, you can create my_func() with age and id parameter which are the same names as age and id column as shown below:

    CREATE FUNCTION my_func(age INT, id INT) RETURNS VOID AS $$
BEGIN                -- ↑↑↑      ↑↑
  EXECUTE 'UPDATE person SET age = $1 WHERE id = $2' USING age, id;
END;                      -- ↑↑↑            ↑↑
$$ LANGUAGE plpgsql;

    Or:

    CREATE FUNCTION my_func(age INT, id INT) RETURNS VOID AS $$
BEGIN                -- ↑↑↑      ↑↑
  EXECUTE 'UPDATE person SET age = $1 WHERE id = $2' USING $1, $2;
END;                      -- ↑↑↑            ↑↑
$$ LANGUAGE plpgsql;

    Then, calling my_func() could update age of David to 56 as shown below:

    postgres=# SELECT my_func(56, 2);
 my_func
---------

(1 row)

postgres=# SELECT * FROM person;
 id | name  | age
----+-------+-----
  1 | John  |  27
  2 | David |  56
(2 rows)

    And without an EXECUTE statement, you can still create my_func() with age and id parameter which are the same names as age and id column as shown below:

    CREATE FUNCTION my_func(age INT, id INT) RETURNS VOID AS $$
BEGIN                -- ↑↑↑      ↑↑
  UPDATE person SET age = age WHERE id = id;
END;                   -- ↑↑↑       ↑↑
$$ LANGUAGE plpgsql;

    Or:

    CREATE FUNCTION my_func(age INT, id INT) RETURNS VOID AS $$
BEGIN                -- ↑↑↑      ↑↑
  UPDATE person SET age = $1 WHERE id = $2;
END;             -- ↑↑↑            ↑↑
$$ LANGUAGE plpgsql;

    But, calling my_func() above gets the error below:

    ERROR: column reference "id" is ambiguous


  2. 0

    What is the difference between the function with and without an EXECUTE statement?

    EXECUTE in PL/pgSQL is used for dynamic SQL. For building and executing SQL statements on the fly. Mainly to involve input from users or system catalogs in other roles than just values – i.e. identifiers or SQL code elements. You don’t normally use EXECUTE unless you need that.

    One exception: While PL/pgSQL can save and reuse a generic query plan for SQL DML statements (much like prepared statements, but only after a couple of executions, there some sophistication to this), EXECUTE prevents that from happening and forces a new query plan for every execution. And that can be (ab)used for highly irregular data distributions, where the query plan is better optimized for the given input (and Postgres does not realize this all by itself).

    It’s hard to imagine this could apply to your simple example. If person.id is the PK, it certainly doesn’t. Basically, the version with EXECUTE is (more expensive, complicated, and error-prone) nonsense.

    Related:

    One other (unimportant) side effect: The query string executes in a separate scope, so variables and parameters of the surrounding code block are not visible there. You pass in values with the USING clause. But you certainly do not need this side effect to hide naming collisions. Just table-qualify column names instead. See:

    Always concatenate dynamic SQL carefully to defend against possible SQL injection! See:

    Aside, age as table column is typically nonsense. Store a birthday instead.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search