Re: AW: UPDATE myTable SET (myTable.*) = json_populate_record(...) ?

On 8/3/26 8:17 AM, Färber, Franz-Josef (StMUK) wrote:
> Yes, the least grave of my items can be worked around by wrapping the function into a subselect, for whatever reason this might be necessary.
> (As far as I know there are other places in Postgres where I can substitute a subselect by an expression / a function call, and I am curious: Why not here?)

Please provide examples of the cases where you can use a
function/expression.

As to why it is necessary the example shown here:

https://www.postgresql.org/docs/current/functions-json.html

shows that to extract the columns you need to do the SELECT.

>
> But what about my other item... what about somehow preventing to write all column names, i. e. changing ...
>
> SET (col1, col2, ...)
>
> ... to something like ...
>
> SET (myTable.*)

Best bet is you are seeing the effect of, from here:

https://www.postgresql.org/docs/18/sql-update.html

"Do not include the table's name in the specification of a target column
— for example, UPDATE table_name SET table_name.col = 1 is invalid."

>
> ?
>
>
> My first intention on posting was: Is there some way to acheive that?
>
> If no, my second intention is a general critique on ... I think it is at least similar to what https://carlineng.com/?postid=sql-critique#blog calls " Lack of Orthogonality" in (Postgres-)SQL.
>
>
> -----Ursprüngliche Nachricht-----
> Von: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> Gesendet: Montag, 3. August 2026 17:00
> An: Färber, Franz-Josef (StMUK) <Franz-Josef(dot)Faerber(at)stmuk(dot)bayern(dot)de>; pgsql-general(at)lists(dot)postgresql(dot)org
> Betreff: Re: UPDATE myTable SET (myTable.*) = json_populate_record(...) ?
>
> On 8/3/26 12:09 AM, Färber, Franz-Josef (StMUK) wrote:
>> Dear Postgres Community,
>>
>> cf. https://www.postgresql.org/docs/current/sql-update.html :
>>
>> As far as I understand, I can
>>
>> * UPDATE myTable SET col1 = val1, col2 = val2, ...
>> * UPDATE myTable SET (col1, col2, ...) = <ROW-or-subselect>
>>
>> But I cannot, and I am missing that:
>> * UPDATE myTable SET (myTable.*) = <ROW-or-subselect>
>> * UPDATE myTable SET (col1, col2, ...) = json_populate_record(NULL::myTable, myJson) -- this is a function, not a ROW construct and not a subselect
>
> Make it a subselect? Not tested but:
>
> UPDATE myTable SET (col1, col2, ...) = (select * from json_populate_record(NULL::myTable, myJson))
>
>>
>> This would be useful for some of my trigger code.
>>
>>
>> Regards,
>> fjf2002
>>
>>
>>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com