Re: psql metaqueries with \gexec

On 2/22/16 5:13 PM, Alvaro Herrera wrote:
> Jim Nasby wrote:
>> On 2/22/16 11:47 AM, Alvaro Herrera wrote:
>>> Pavel Stehule wrote:
>>>
>>>> The design of the "format" function is not closed. Try to send prototype
>>>> and patch. The possibility to do PostgreSQL customization was strong reason
>>>> why we didn't implemented "sprintf" and we implemented "format".
>>>
>>> Probably not terribly useful here, but for the DDL-deparse patch I came
>>> up with a syntax to format JSON objects, which used %-escapes; each
>>> escaped element corresponds to a string literal, or to an object. So
>>> you'd have %{table}D where the "table" element in the JSON object could
>>> be a simple string which is expanded verbatim (plus quoting if
>>> necessary), or it could be a JSON object with something like { schema =>
>>> "public", name => "students" }, where each element is expanded and
>>> quoted as necessary; if the "schema" is null or it doesn't exist, it
>>> expands only the name, obviously omitting the dot.
>>
>> Where did the "D" in "%{table}D" come from?
>
> The I in %{foo}I was for "identifier" (of course) and I *think* the D
> was for "double identifiers" (that is, qualified). I expanded the idea
> afterwards to allow for a third name for things like
> catalog.schema.name, so I guess it's a misnomer already.
>
> It's not released code yet. You can see an example here
> https://www.postgresql.org/message-id/%3C20150224175152.GI5169%40alvh.no-ip.org%3E
> just scroll down a few hundred lines to about 7/16ths of the page (yes,
> really)
>
> (One thing I had to come up with was processing of arrays, which you
> also see in that example JSON -- it's the specifiers that have a colon
> inside the {}. The part after the colon is used as separator between
> the array elements, and each element is expanded separately.)

Ahh, very interesting.

Something that would probably be helpful for these kind of things is if
we had a set of complex types available that represented things like the
arguments to a function. Something like (parameter_mode enum(IN, OUT,
INOUT), parameter_name name, parameter_type regtype, parameter_default
text). A function might be represented by (function_schema name,
function_name name, function_parameters ..., function_language,
function_options, function_body).

In any case, having anything along these lines in core would be useful,
assuming that the individual facility was exposed as well (as opposed to
only being available inside an event trigger).
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com