m@pplcast.com writes:
> Not sure if bug or feature, but the query below without the "offset 0" fails
> with "ERROR: cannot extract field from a non-object", while the one with
> succeeds as intended. Please excuse the messiness.
Yeah, this is not surprising, since as you can see from the query plans,
by default the planner will flatten the sub-select, and it ends up putting
your json_isarray() function after the filter conditions it's meant to
protect. That it does so is a feature, not a bug, because PL functions
are by default assigned a higher cost than built-in C functions; so
they'll be put at the end of any list of conditions to be checked at the
same plan node.
If you want something less ugly than the OFFSET 0 hack as a workaround,
you could try assigning your function a small cost (less than 1).
Another possible approach is to make the "select where not json_isarray"
bit into an explicitly declared view, which you could mark as a SECURITY
view to prevent the planner from changing the qual order. That might
turn out badly from an optimization standpoint though.
regards, tom lane