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 <[email protected]>
Gesendet: Montag, 3. August 2026 17:00
An: Färber, Franz-Josef (StMUK) <[email protected]>;
[email protected]
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
[email protected]
