Use JSONB instead of hstore?

[coreyhuinker]

Then your extension would have no dependencies outside of core.

[melanieplageman]

I am currently working on accepting RECORD type as a parameter. I may release this as a separate extension (with no hstore dependency) and keep the original extension which requires hstore. After accepting RECORD types, I can work on adding support for JSONB.

[coreyhuinker]

I don't think a function can accept an un-typed record as a parameter. I'll be very pleasantly surprised if it can. Perhaps you meant a polymorphic function? Either way, excited that somebody's working on it. If you can get something working with no contrib dependencies, it's worthy of submitting to the core for 10.0.

[melanieplageman]

Actually, PostgreSQL allows you to take composite types as arguments (see section 35.9.7). So, I'm able to accept a RECORD type in the PostgreSQL function and work with it as a HeapTupleHeader in the C function. I can then access the fields with a custom version of the GetAttributeByName() function.
Right now, I have a format_record() function working with no hstore dependency, however, my current implementation only works with TEXT type values.
As my next step, I have to make a design decision as to how to accept other types as values.

[coreyatmoat]

GetAttributeByName() looks up attributes in the typecache. I'm not super familiar with that area, but as far as I know the only things in the typecache are defined objects (tables, views, types, etc), so I don't think you're using on-the-fly records. I'm confused as to what that function signature would look like.

So if that's what you're using, then it's a polymorphic function (input type anyelement), and that's something I'd like to see added to core.

As for the text-only problem, if you have the type oid of an attribute, then you can fetch the text output function of that type, invoke that function, and then everything is formatted in a not-very-surprising way. I have more experience in that area, so I could help if you share a branch, or I could point you to some code I've written that does that step. The docs are only helpful in hindsight, IMO.

[ktchen14]

@coreyatmoat As far as I can tell PostgreSQL doesn't have a syntax to support on-the-fly records with named attributes. Is this just undocumented?

[coreyatmoat]

@ktchen14 - That's what I was thinking, but maybe Mel found something neat.

(btw, @chuinker is my personal account, this is a client account - I'm not good at paying attention to which one is logged in).

[melanieplageman]

The function signature is

format_record(TEXT, RECORD) RETURNS TEXT

[coreyatmoat]

What's a function invocation look like?

[melanieplageman]

SELECT format_record('%(name)s is %(age)s years old', person) FROM person;

[coreyatmoat]

Yup. That's a polymorphic function. Normally that function signature would be format_record(text,anyelement) but apparently the parser allows record instead.

For truly untyped things, the JSONB may be the best option, something like

select format_jsonb(' x is %(x)s, y is %(y)s ', jsonb_build_object('x','4','y','5'))

will have to do.

The magic for getting a reasonable print representation of any attribute type is OutputFunctionCall(). If you need an example of how it's used, there's one here, but maybe not the best example.. Look for things that reference the array text_output_functions.

Now that I know what's happening, I really-really-really want to see this added to core. Happy to assist in any way in making that happen.

[ktchen14]

With a C function the prototype could look like format(TEXT, VARIADIC "any"). I believe this is how the format() function is currently defined. Then it would be possible to support positional arguments as well as JSON, JSONB, RECORD, ARRAY, and HSTORE making for a very powerful format() replacement.

[coreyhuinker]

@ktchen14 - The format function wouldn't be able to tell from a 2-arg format() call whether you wanted the second arg interpreted as a a) single value to be text-coerced or b) one of the json/jsonb/record/array/hstore types. And there are cases where you want to just print the whole JSON object, and many composite types do have text output functions.

So it'll have to be a separately named function, or one with no possibility of ambiguous signatures.

[ktchen14]

@chuinker I believe it's possible to lookup the OID of each argument's type with get_fn_expr_argtype(). So it should be possible to differentiate between single valued types and composite like types such as JSONB and RECORD. For reference I believe there's a good reference function called something like jsonb_categorize_type() in the PostgreSQL source.

I can imagine that some kind of syntax could be used to differentiate between a key-value lookup vs a coercion to TEXT.

The tricky part to this though is that HSTORE being an extension doesn't have an OID known at compile time. I have no idea if there's a good way around that.

[coreyhuinker]

It is possible, but there are cases where you want to print the whole object, and cases where you want the object to be viewed as a composite. It only takes one ambiguous case to reject the whole patch.

[melanieplageman]

@chuinker I believe this can be addressed with syntax

[coreyatmoat]

I hope you're right. If not, separate functions aren't too terrible.