Empty arrays get serialised as empty string

[LinusU]

This causes a parse error in my sql query. Consider the following example:

CREATE TABLE thing(
  tags string[] NOT NULL
)

const format = require('pg-format')

format('INSERT INTO %I (%I) VALUES(%L)', 'thing', 'tags', [])

Now this get's formatted into the following query:

INSERT INTO thing (tags) VALUES()

Which gives the following error:

error: syntax error at or near ")"

I believe that the correct query output would have been:

INSERT INTO thing (tags) VALUES('{}')

A workaround is to send in the string '{}' to the format function, but that is very ugly and breaks my validation of data since it expects an array of strings...

[LinusU]

Actually, it seems like I might have misunderstood how array handling is handled at all :(

Would it make sense to have some flag that worked as the %L flag, but that also handles array with the postgres array syntax? Actually, it feels like this is how %L should work, no?

Care to enlighten me?

[LinusU]

Okay, after reading up a bit it seems like this should be the correct way to serialise an array as a literal. The quotes are from the postgres manual: http://www.postgresql.org/docs/9.5/static/arrays.html

To write an array value as a literal constant, enclose the element values within curly braces and separate them by commas.

Okay so, empty array is '{}', the javascript array [1, 2, 3] is '{1,2,3}'.

You can put double quotes around any element value, and must do so if it contains commas or curly braces.

This is still pretty easy, the javascript array ['a,b', 'c,d'] is '{"a,b","c,d"}'.

We could easily implement this by serialising the array first, and then passing that string again as the literal which would then get the enclosing single-quotes.

I realise that this might be a breaking change, but I really think that this is the way to go, and I would be happy to help out and contribute the code.

[rpedela]

There are actually two use cases.

1. Formatting an array as a Postgres array literal which you want to do and is currently not possible.
2. Formatting an array as a list. This is supported and generally more flexible such as the following examples.

// SELECT a, b, c FROM my_table
format('SELECT %I FROM my_table', [ 'a', 'b', 'c' ]);

// SELECT * FROM my_table WHERE a IN (1, 2, 3)
format('SELECT * FROM my_table WHERE a IN (%L)', [ 1, 2, 3 ]);

Your example can be modified slightly to also work with the current implementation.

// INSERT INTO thing (tags) VALUES(ARRAY[]::text[])
format('INSERT INTO %I (%I) VALUES(ARRAY[%L]::text[])', 'thing', 'tags', [])

I do think it would be great to also support Postgres array literal syntax. I am open to ideas but it cannot break the current functionality.

[LinusU]

I am open to ideas but it cannot break the current functionality.

Not even in a major version?

As you say I want array literal, and %L is for literals. It seems like it would be more "correct" to let that behaviour be under %L and introduce a new symbol for lists. What do you think?

[rpedela]

How about a new symbol for array literals instead like %A? That achieves the same thing and doesn't break anything.

[rpedela]

On second thought, we could do this.

%s = same behavior
%As = format array as PG array literal, error if input not array

%L = same behavior
%AL = format array as an escaped PG array literal, error if input not array

%I = same behavior
%AI = doesn't make sense, not supported

[LinusU]

The problem is that I don't know beforehand if it's going to be an array or not, I'm writing a generic wrapper which basically constructs strings like these:

const sql = 'INSERT INTO %I (%I, %I, %I) VALUES(%L, %L, %L)'

[rpedela]

I see. How about this? We add an A flag to %L and %s like I suggested previously, but we don't error if the input is not an array. Rather the flag simply means that you want arrays to be formatted as Postgres array literals, but everything else gets treated normally. So your example would look like this:

const sql = 'INSERT INTO %I (%I, %I, %I) VALUES(%AL, %AL, %AL)'

You would get the behavior you desire, we support array literals, and nothing breaks. What do you think?

[LinusU]

Hmm, I really really really would prefer using %L, I really think that it's the best semantics.

But, how about %V for "values", it would support the arrays as I want them, and instead of using JSON.stringify on objects, it would support Postgres' user defined types which I know that I'll need very soon as well.

What do you think about that?

It seems cleaner to have %V since it says something ("value"), and since it's only one character which otherwise might introduce some quirks in the parser...

[LinusU]

Wouldn't this be quite cool? 😎

CREATE TYPE person AS (
  firstName text,
  lastName text
);

CREATE TABLE company(
  name text,
  leaders person[]
);

const tim = { firstName: 'Tim', lastName: 'Cooks' }
const steve = { firstName: 'Steve', lastName: 'Jobs' }
const apple = { name: 'Apple', users: [steve, tim] }
const sql = format('INSERT INTO %I (%I, %I) VALUES(%V, %V)', 'company', 'name', 'leaders', apple.name, apple.leaders)

[rpedela]

Yes that would be cool. :)

I think it is a great goal to somehow change the default formatting behavior for specific cases, and I think we should support it. The next question is how?

I think adding a new formatting type such as %V is moving in the wrong direction. format() supports various data types as strings, identifiers, and literals. If we want different behavior when formatting a string, identifier, or literal than the default, a flag makes much more sense to me than adding a new formatting type. In fact, Postgres format() supports flags. I just haven't implemented them because no one has asked. Anyway, adding support for flags is probably inevitable and it is not too difficult to modify the parser.

In order to support PG array literals, composite types, and potentially other data types, I would rather support flags for each formatting type. By doing it this way, we can also support PG array literals, composite types, etc as strings or identifiers (if it makes sense) too.

[rpedela]

I am not too sure about supporting user-defined types since they can literally be anything, but composite types (like in your example) are probably safe to support.

[LinusU]

Hmm, I did not know that there was an official function which we are modeling ourselves after, cool! Then I agree, we shouldn't add %V.

I still would like it to be the default behaviour of %L though. Especially since that is exactly how the postgresql format function works.

postgres=# SELECT format('Hello %L', ARRAY[1, 2, 3]);
    format     
---------------
 Hello '{1,2,3}'

[LinusU]

My original example:

postgres=# SELECT format('INSERT INTO %I (%I) VALUES(%L)', 'thing', 'tags', ARRAY['a', 'b']);
                  format                  
------------------------------------------
 INSERT INTO thing (tags) VALUES('{a,b}')
(1 row)

[rpedela]

I am aware of how PG format() works for arrays. Just because this library is modeled after that function does not mean it must adhere exactly to the same API. I should document the differences though.

The original motivation for this library was that I was getting extremely tired, annoyed, frustrated trying to escape rather complex SQL in my JS code. In particular, expanding JS arrays into WHERE and VALUES clauses required a lot of boilerplate code at least for my use cases. So I built this library to handle all the messy escaping details including expanding JS arrays.

It turns out that expanding JS arrays into a SQL statement is a common problem, and I have seen this functionality used by other libraries which depend on pg-format. Postgres arrays, composite types, user-defined types are all a bit esoteric. It is more likely someone will need to expand a JS array into a WHERE clause than they are to need a Postgres array literal, and that is why it is the default. However you can still expand the JS array into a Postgres array literal but you must use the more verbose syntax: ARRAY[%L]. The default array escaping handles ALL cases except one: the concise Postgres array literal syntax.

Given we cannot currently support the concise Postgres array literal syntax, and you need it I am happy to implement a way to get it. However I am not happy about changing the current default behavior of arrays to conform to PG format() just because that is how that function works.

Ultimately it is about having good defaults and making less common use cases possible even if that means not adhering to how PG format() works.