From: | Nico Williams <nico(at)cryptonector(dot)com> |
---|---|
To: | Eric Hanson <eric(at)aquameta(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Aquameta 0.1 - Request for reviews, contributors |
Date: | 2017-09-08 23:26:43 |
Message-ID: | 20170908232641.GC3831@localhost |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Here's a review comment. Just one for now.
Looking at the meta module, I see things like this:
execute 'select (count(*) = 1) from ' || quote_ident((row_id::meta.schema_id).name) || '.' || quote_ident((row_id::meta.relation_id).name) ||
' where ' || quote_ident((row_id.pk_column_id).name) || ' = ' || quote_literal(row_id.pk_value)
into answer;
I recently learned what I find to be a better idiom:
execute format(
$q$
select exists (select *
from %1$I.%2$I
where %3$I = %4$L);
$q$,
-- interpolated arguments here
(row_id::meta.schema_id).name, (row_id::meta.relation_id).name,
(row_id.pk_column_id).name, row_id.pk_value
into answer;
That is, PostgreSQL has extended string literal syntax where you can use
$stuff$ instead of single-quotes, and that makes it much easier to write
dynamic (generated for EXECUTE) SQL. In particular, because your
$EDITOR [generally] won't recognize this, syntax highlighting for the
$quoted$ code will work as expected!
This is better not only because it's more concise, easier to line-wrap,
and easier on the eyes, but also because you get to use format(). I
suspect using format() makes it harder to forget to quote something
appropriately -- harder to accidentally create a SQL injection
vulnerability. I usually use argument numbering (%<n>$I) instead of
referring to the positionally (%I, %L, %s) because it helps a lot
whenever I need to refer to one of them multiple times.
Of course, this is just a matter of style, but I strongly feel that this
is the superior style (at least I find or stumble into a better style),
especially when you have several layers of trigger functions creating
more trigger functions, as you can easily nest $foo$-quoted string
literals by having different quote forms for each level.
Also, I used exists() instead of count(*) = 1 -- that's just my personal
preference, and a less defensible style matter (it is more verbose...).
Nico
--
From | Date | Subject | |
---|---|---|---|
Next Message | Dave Peticolas | 2017-09-09 22:26:10 | Analyzing performance regression from 9.2 to 9.6 |
Previous Message | John Turner | 2017-09-08 22:37:06 | Re: B-tree index on a VARCHAR(4000) column |