Re: update without SET ? - Mailing list pgsql-sql
json is really just key-value pairs..
why not take the same approach and store the data in a temp table as key-value then have a static sumif
with jsonrows as(
select 1::int as r,key,value from json_each_text('{"key":22,"header":44,"ident":66,"static01":"this","static02":"that"}')
union
select 2::int as r,key,value from json_each_text('{"key":22,"header":44,"ident":66,"static03":"thatsit"}')
)
select
r,
max(case when key='key' then value::int else null end) as key,
max(case when key='header' then value::int else null end) as header,
max(case when key='ident' then value::int else null end) as ident,
max(case when key='static01' then value::varchar(200) else null end) as static01,
max(case when key='static02' then value::varchar(200) else null end) as static02,
max(case when key='static03' then value::varchar(200) else null end) as static03
from jsonrows
group by r
;
alternative is I guess a simple function
Hector Vass
07773 352 559
01666 820 008
MetaMetrics, International House, 107 Gloucester Road, Malmesbury, Wiltshire, SN16 0AJ
This e-mail and any attachments are confidential and for the attention of the addressee only. If you are not the intended recipient, any use, disclosure or copying of this document is unauthorised. If you have received this document in error please immediately notify the sender and delete this e-mail from your system. Whilst all emails sent by MetaMetrics are scanned using up-to-date virus scanning software, MetaMetrics Ltd. accepts no liability for any loss or damage which may be caused by software viruses and recommend that you conduct your own virus checks on all attached materials. Please note that any attached materials remain the exclusive property of MetaMetrics Ltd. unless expressly stated otherwise. Metametrics Limited is a limited company registered in England & Wales. Registered number 05453613. Registered offices at 86 Shirehampton Road, Stoke Bishop, Bristol, BS9 2DR
From: pgsql-sql-owner@postgresql.org <pgsql-sql-owner@postgresql.org> on behalf of Michael Moore <michaeljmoore@gmail.com>
Sent: 03 February 2016 18:49
To: postgres list
Subject: [SQL] update without SET ?
Sent: 03 February 2016 18:49
To: postgres list
Subject: [SQL] update without SET ?
I want to UPDATE a table but I will not know until run time which columns will be updated. I would like to do something like this:
update mytest t (SELECT * FROM json_populate_record(null::mytest,'{"key":22,"header":44,"ident":66,"static01":"this","static02":"that"}'));
In other words, I will be receiving a json document on an input parameter. The columns named in the json document can be a sub set of those that exist in the table.
tia Mike