On Wed, Feb 3, 2016 at 4:09 PM, Michael Moore <michaeljmoore@gmail.com> wrote:Lost me here David. I can't seem to bridge the gap between your pseudo-code and an actual example.setup:CREATE TABLE mytest(key bigint NOT NULL, header bigint NOT NULL, ident bigint NOT NULL, static01 character varying(100), static02 character varying(220) );INSERT into mytest SELECT * FROM json_populate_record(null::mytest,'{"key":22,"header":44,"ident":66,"static01":"this","static02":"that"}');And here is my failed interpretation of your example: (I used inline JSON string just for ease. In real life it will be a variable of type JSON.)UPDATE mytest SET header = (CASE WHEN src.header = 'header' THEN src.header ELSE mytest.header END)FROM ( SELECT * FROM json_populate_record(null::mytest, '{"key":22,"header":44,"ident":null,"static02":"that"}') CROSS JOIN (SELECT '{"key":22,"header":44,"ident":null,"static02":"that"}') AS source_json ) src WHERE mytest.key = src.key;UPDATE mytest SET header = CASE WHEN source_json ? 'header' THEN src.header ELSE mytest.header ENDFROM ( SELECT * FROM (VALUES ('{"key":22,"header":44,"ident":null,"static02":"that"}'::jsonb)) src_json (source_json), LATERAL jsonb_populate_record(null::mytest, source_json)) srcWHERE mytest.key = src.key;LATERAL makes writing this a bit cleaner but is not mandatory. The CROSS JOIN would have worked but didn't feel like playing with the syntax.I am not sure where you got the idea to try " WHEN src.header = 'header'" especially since the value of src.header is 44...Apparently the exists operator (no matching function so if you cannot use "?" as an operator you will have issues...) is jsonb only.
Lost me here David. I can't seem to bridge the gap between your pseudo-code and an actual example.setup:CREATE TABLE mytest(key bigint NOT NULL, header bigint NOT NULL, ident bigint NOT NULL, static01 character varying(100), static02 character varying(220) );INSERT into mytest SELECT * FROM json_populate_record(null::mytest,'{"key":22,"header":44,"ident":66,"static01":"this","static02":"that"}');And here is my failed interpretation of your example: (I used inline JSON string just for ease. In real life it will be a variable of type JSON.)UPDATE mytest SET header = (CASE WHEN src.header = 'header' THEN src.header ELSE mytest.header END)FROM ( SELECT * FROM json_populate_record(null::mytest, '{"key":22,"header":44,"ident":null,"static02":"that"}') CROSS JOIN (SELECT '{"key":22,"header":44,"ident":null,"static02":"that"}') AS source_json ) src WHERE mytest.key = src.key;
pgsql-sql by date:
Соглашаюсь с условиями обработки персональных данных