Home
About Me

Dynamic Commands in PL/pgSQL

The execute keyword allows you to run a dynamic command - where perhaps a table or column name is not known ahead of time.

1
create or replace function get_row_count(table_name text)
2
  returns integer
3
  as $$
4
declare
5
  c int;
6
begin
7
  execute format('select count(*) from %I', table_name) into c;
8


9
  return c;
10
end;
11
$$ language plpgsql;
12


13
create table s (id serial primary key);
14
insert into s default values;
15


16
create table t (id serial primary key);
17
insert into t default values;
18
insert into t default values;
19


20
select get_row_count('s'); -- 1
21
select get_row_count('t'); -- 2

Some notes from the docs:

  • There is no plan caching for dynamic commands - commands executed via execute.
  • Be careful not to do something like key = null, where null is passed in dynamically. (Use is distinct from).
  • See the using clause for passing parameters to the dynamic command.

You can read more about this here.

Wow! You read the whole thing. People who make it this far sometimes want to receive emails when I post something new.

I also have an RSS feed.