Re: update without SET ?

On Wed, Feb 3, 2016 at 4:09 PM, Michael Moore <michaeljmoore(at)gmail(dot)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 END
FROM (
SELECT *
FROM (VALUES
('{"key":22,"header":44,"ident":null,"static02":"that"}'::jsonb)) src_json
(source_json),
LATERAL jsonb_populate_record(null::mytest, source_json)
) src
WHERE 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.
​