pgsanity
pgsanity copied to clipboard
markdrago
False positive on JSON operator '#>' and '#>>'
First thanks for a great tool! Very useful ;)
The issue: Valid postgres sql statements that contains "'#>" and "#>>" fails validation.
Postgres documentation for these operations: https://www.postgresql.org/docs/11/functions-json.html
Example:
bash-4.4# echo "select '{\"a\":[1,2,3],\"b\":[4,5,6]}'::json#>>'{a,2}'" | pgsanity
line 2: ERROR: syntax error at or near "#>>'{a,2}'
"
Tobias - thanks so much for reporting this issue. It's a fun one. ;-)
So, the way pgsanity works is that it tweaks the raw SQL to look like a C program with embedded SQL and then feeds that in to ecpg, a tool provided by postgresql, to perform the actual syntax checking. So, the trick to get this to work is to figure out what ecpg needs to see for it to understand that #>> is a valid operator. I've been looking through documentation and haven't yet figured out what it is about the # sign that ecpg doesn't like and I was hoping you could help.
Basically, we need to figure out what we need to do with the # sign in this command to get ecpg to accept it:
echo "EXEC SQL select '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}';" | ecpg -o - -
That command cuts pgsanity out of the picture and just passes a very small embedded C file to ecpg. I've tried a bunch of things (escaping the # with , doubling it up with ##, doing some trickery with C macros on a whim, etc.) and I haven't been able to figure it out. It's possible that this is just a bug in ecpg. I'm willing to bet that there are not a lot of people using both embedded SQL in C and putting JSON in their database. It'd be great if you could try and figure it out and maybe we can come to a conclusion together. Thanks again.
