SQL Macros a.k.a. Dynamic SQL

dynamic-sql.md

SQL Macros a.k.a. Dynamic SQL

Strings in Postgres

A little background on string literals that will be pertinent.

https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS

select 'this is a string';

-- if you prefix with E, you can include C-style escape sequences
select E'this string has a newline\nin it';

select $$this is a dollar-quoted string$$;

select $$dollar-quoted strings can contain a single-quote ' without causing problems$$;

select $foo$this is also a dollar-quoted string$foo$;

select $foo$this style can contain $$ without causing problems$foo$;

select $$
dollar-quoted
strings
can
be
multi-line
$$;

Anonymous Blocks

https://www.postgresql.org/docs/current/sql-do.html

The do command takes a string containing code and executes it. It's essentially an unnamed function/procedure. I think of it as an anonymous block.

do can be handy, but it has limitations. You cannot pass any arguments in, and it cannot return anything.

This anonymous block emits a log message at the notice level.

do language plpgsql $block$
declare
  _the_answer int = 42;
begin
  raise notice '%', _the_answer;
end;
$block$;

do can use any procedural language you have installed. This one is using plpython3u. If you omit the language, it defaults to plpgsql. What does this block do?

do language plpython3u $block$
import os

for k, v in os.environ.items():
  plpy.notice(f"{k}={v}")
$block$;

Functions are very similar. (Most of the time) they take a sting to define the body of the function.

create function bob(i int) returns text as $func$
begin
  return i::text;
end
$func$ language plpgsql;

You could use single-quoted strings to define the body, but please don't. No one should be subjected to this torture:

create function bob(i int) returns text as E'begin\nreturn i::text;\nend' language plpgsql;

SQL Macros a.k.a. Dynamic SQL

https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

plpgsql has an execute command. It takes a string containing a SQL statement and executes it.

do $block$
begin
  execute 'create table bob (id int)';
end
$block$;

For fixed SQL statements that are known at "development" time, this is pretty useless. It becomes useful when we cannot specify the whole SQL statement until runtime. We need code that writes code.

Some languages have compile-time macros. This is a sort of runtime macro.

Some dynamic languages have eval(). This is eval() for Postgres.

create or replace function how_many_rows(tbl text) returns bigint
as $func$
declare
  _sql text;
  _count bigint;
begin
  _sql = format('select count(*) from %s', tbl);
  execute _sql into _count;
  return _count;
end
$func$ language plpgsql;

create table bob as select generate_series(1, 1000) as id;

select how_many_rows('bob');

Little Bobby Tables a.k.a. SQL Injection

https://xkcd.com/327/

\du

select how_many_rows($$bob; create user fred with superuser password '123'; select 12$$);

\du

Oh no! What did we do?!?! A malicious user just exploited our use of dynamic sql an powned our db.

We ALWAYS have to keep SQL injection in mind when we use dynamic SQL.

format to the rescue.

-- %s does plain substitution. beware
select format('hello %s', 'little bobby tables');

-- %I will escape for a SQL identifier (like a table/column name)
select format('hello %I', 'little bobby tables');

-- %L will escape for a SQL literal
select format('hello %L', 'little bobby tables');

When you can, use parameterized queries. $1, $2, $3 etc. serve as placeholders for parameters to the query.

You can combine format, query parameters, and execute.

create or replace function rows_divisible_by(tbl text, col text, divisor int) returns bigint
as $func$
declare
  _sql text;
  _count bigint;
begin
  _sql = format('select count(*) from %I where mod(%I, $1) = 0', tbl, col);
  raise notice '%', _sql;
  execute _sql using divisor into _count;
  return _count;
end
$func$ language plpgsql;

create table if not exists bob as select generate_series(1, 1000) as id;

select rows_divisible_by('bob', 'id', 2);