WIP: transformation hook modules and JSON support

Hello

I am sending samples of transformation hook modules. One module is
JSON support:.

From these modules only JSON support has general usage - so only JSON
should be integrated to core.

Regards
Pavel Stehule

=== README ===
JSON generating functions - this module contains functions, that allows
simply generation JSON objects. Inspiration of this module was Json library
http://www.mysqludf.org/lib_mysqludf_json/index.php .

note: an result isn't same as Roland library. Roland more respect javascript
rules. This library more respect JSON standard from json.org..

RAW parameters and labeled parameters
-----------------------------------
PostgreSQL parser ensure well typed parameters for any functions.
Types of parameters.
are described in pg_proc record. Exception from this rule are raw
parameters (of "any"
type). Raw parameters are passed without any conversion..

Sample of function with raw parameters is function json_array:

postgres=# select json.json_array(10,20,30,'Pavel',current_date);
json_array............
---------------------------------
[10,20,30,"Pavel","2009-03-28"]
(1 row)

postgres=# select
json.json_array(10,20,30,'Pavel',json.json_array(20,30), true, false);
json_array...............
---------------------------------------
[10,20,30,"Pavel",[20,30],true,false]
(1 row)

Smart parameter take some addition info from calling environment. One
sample of smart
parameter's function is SQL/XML function xmforest..

Json library use raw parameters for automatical double quotes wrapping
of string values.
json_object functions use labeled parameters. Aditional info use as
property name..

postgres=# select json_object(10,'akaka',29);
ERROR: invalid input syntax for integer: "akaka"
LINE 1: select json_object(10,'akaka',29);
^
postgres=# load 'json';
LOAD
Time: 1,677 ms
postgres=# select json_object(10,'akaka',29);
ERROR: unnamed JSON attribute must be a column reference
LINE 1: select json_object(10,'akaka',29);
^
postgres=# select json_object(10 as b,'akaka' as c,29 as x);
json_object......
-----------------------
{b:10,c:"akaka",x:29}
1 row)

Function json_members is very specific. PostgreSQL definition should be like
json_members(text, "any", text, "any", text, "any", ...). Parameters
are pairs of
property name and json value. First in pair should be text or any type
with implicit
cast to text.

postgres=# select json.json_members('a',2,'b',current_date);
json_members......
------------------------
"a":2,"b":"2009-03-29"
(1 row)

postgres=# select json.json_object(json.json_members('a',2,'b',current_date));
json_object........
--------------------------
{"a":2,"b":"2009-03-29"}
(1 row)

Pavel Stehule escribió:
> Hello
>
> I am sending samples of transformation hook modules. One module is
> JSON support:.
>
> From these modules only JSON support has general usage - so only JSON
> should be integrated to core.

I'm only seeing trivial examples below, where you form the JSON objects
by plastering literals together. Does this work on a scenario where the
values come from a table?

The question is not at all theoretical -- right now our
archives.postgresql.org site uses a JSON file that's just a dump of a
table in a database. This file contains a list of lists, and a number
of properties for each (name, group it belongs to, description).
Obviously each one needs its label too.

Right now we generate this with a JSON Perl module.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

2009/4/1 Alvaro Herrera <alvherre(at)commandprompt(dot)com>:
> Pavel Stehule escribió:
>> Hello
>>
>> I am sending samples of transformation hook modules. One module is
>> JSON support:.
>>
>> From these modules only JSON support has general usage - so only JSON
>> should be integrated to core.
>
> I'm only seeing trivial examples below, where you form the JSON objects
> by plastering literals together.  Does this work on a scenario where the
> values come from a table?

what do you thing?

My implementation works similar like SQL/XML functions - so of course,
you can read data from tables. But actually, these functions are not
100% optimised.

>
> The question is not at all theoretical -- right now our
> archives.postgresql.org site uses a JSON file that's just a dump of a
> table in a database.  This file contains a list of lists, and a number
> of properties for each (name, group it belongs to, description).
> Obviously each one needs its label too.

nested JSON structures are possible.

Pavel Stehule

>
> Right now we generate this with a JSON Perl module.
>
> --
> Alvaro Herrera                                http://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.
>