On Wed, Jun 12, 2013 at 11:38 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
no argument against json_typeof, but why can you just peek the first non-whitespace character? json is famously easy to parse (see here: http://www.json.org/)
create or replace function json_typeof(_json json) returns text as $$ select case substring(ltrim($1::text), 1, 1) when '[' then 'array' when '{' then 'object' end; $$ language sql immutable;
you could expand this mechanic fairly easy to cover all json types. note exception handlers are very heavy for this type of operation.
Thanks for the suggestion -- hadn't thought of just looking at the first non-whitespace character. It should be trivial to make this into an efficient C function that could live in jsonfuncs.c as a built-in. I might do that and submit a patch for 9.4 CF2.