Updating a row using a ROWTYPE variable in Postgres

Question

Let's say I'm writing a Postgres PSQL script in which I fetch a rowtype variable from the DB:

DECLARE    
  m_bal  Balances%ROWTYPE;

BEGIN
  SELECT INTO m_bal *
  FROM Balances
  WHERE Balances.id = m_acct.id AND Balances.currency = _currency;
...

and then I'd like to update one or more values depending on more complex logic and save it to the database:

UPDATE Balances
SET ROW = m_bal
WHERE id = m_bal.id;

only this doesn't work and I'm getting nowhere after an hour of googling, I'm getting a general idea that Postgres doesn't support this, but having a definitive answer "no" would also be nice.

Laurenz Albe · Accepted Answer · 2017-05-04 19:56:34Z

3

Sorry, but that won't work.

While you can use whole-row references in SELECT lists, UPDATE only allows column names or parenthized column lists in the SET clause.

answered May 4, 2017 at 19:56

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Oto Shavadze · Accepted Answer · 2017-05-04 20:00:36Z

1

Not whole row, though you can update each columns separately (as usually update, something like this):

create table test(
col1 text,
col2 int
);

insert into test
values
('asd4', 4),
('asd5', 5),
('asd6', 6) ;


do $$
declare
    m_bal  test%ROWTYPE;
begin
    SELECT INTO m_bal *
    FROM test
    WHERE test.col2 = 5;

    UPDATE test
    SET 
    col1 = m_bal.col1,
    col2 = m_bal.col2
    WHERE col2 = 6;
end;
$$ language plpgsql

answered May 4, 2017 at 20:00

Oto Shavadze

43.3k56 gold badges168 silver badges248 bronze badges

Collectives™ on Stack Overflow

Updating a row using a ROWTYPE variable in Postgres

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related