Re: [GENERAL] Aquameta 0.1 - Request for reviews, contributors

[Eric Hanson]

Thanks Nico.  I definitely like this syntax better.

--
Eric Hanson
CEO, Aquameta Labs
503-929-1073
www.aquameta.com


On Fri, Sep 8, 2017 at 4:26 PM, Nico Williams  wrote:

>
> 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 (%$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
> --
>

Re: [GENERAL] Aquameta 0.1 - Request for reviews, contributors

[Nico Williams]

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 (%$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
-- 


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general