Dynamically create Postgresql queries

We can use the format and execute functions for this.

Create your sql string with format.

format($__$
  create table %I (id serial primary key, name %I not null);
$__$, 'cats', 'name');

%1$I allows you to specify the position of the argument. If I wanted to use an sql literal I'd use %L or %s for a string.

Then put it through execute(sql_string);. In full:

do $$
declare
  sql_str text;
begin
  sql_str := format($__$
    create table %I (id serial primary key, %I text not null);
  $__$, 'cats', 'name');
  execute(sql_str);
end
$$;
postgresql

Edit on github
comments powered by Disqus
Click me