Re: Bypassing NULL elements in row_to_json function

[Michael Nolan <htfoot@xxxxxxxxx>]

Here's what I did:

 \d gold1604_test
Table "uscf.gold1604_test"
 Column | Type | Modifiers
--------+------+-----------
 data   | json |

Some sample data:
 {"id":"10000001","name":"MISNER, J NATHAN","st":"NY","exp":"2012-05-31","sts":
"A"} +

  {"id":"10000002","name":"MISNER, JUDY","st":"TN","exp":"2007-07-31","sts":"I"}
     +

  {"id":"10000003","name":"MISNER, J AMSCHEL","st":"NY","exp":"2007-05-31","sts"
:"A"}+

uscf-> \d goldmast_test
         Table "uscf.goldmast_test"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | character varying(8)  |
 name   | character varying(40) |
 st     | character varying(2)  |
 exp    | date                  |
 sts    | character(1)          |
 supp   | date                  |
 rrtg   | character varying(8)  |
 qrtg   | character varying(8)  |
 brtg   | character varying(8)  |
 oqrtg  | character varying(8)  |
 obrtg  | character varying(8)  |
 fid    | character varying(12) |

insert into goldmast_test select * from json_populate_record(NULL::"goldmast_test", (select * from gold1604_test limit 1) )

 produces:
uscf=> select * from goldmast_test;
    id    |       name       | st |    exp     | sts | supp | rrtg | qrtg | brtg
 | oqrtg | obrtg | fid
----------+------------------+----+------------+-----+------+------+------+-----
-+-------+-------+-----
 10000001 | MISNER, J NATHAN | NY | 2012-05-31 | A   |      |      |      |
 |       |       |
(1 row)

The fact that the null values were stripped out is not an issue here.

But,
uscf=> insert into goldmast_test select * from json_populate_record(NULL::"goldmast_test", (select * from gold1604_test limit 2) )
uscf-> \g
ERROR:  more than one row returned by a subquery used as an _expression_

Is there a way to get around the one row per subquery issue?
--

Mike Nolan