plpgsql update row from record variable

From: Seamus Abshere <seamus(at)abshere(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: plpgsql update row from record variable
Date: 2016-04-03 01:07:13
Message-ID: 1459645633.3419203.567021586.72834A9C@webmail.messagingengine.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

hi,

I want to write a function that updates arbitrary columns and here's my
pseudocode:

CREATE OR REPLACE FUNCTION update_pet(id int, raw_data jsonb) RETURNS
VOID AS $$
DECLARE
data record;
BEGIN
SELECT jsonb_populate_record(null::pets, raw_data) INTO data;
UPDATE pets [... from data ...] WHERE id = id; -- probably impossible
END;
$$ LANGUAGE plpgsql;

e.g.

SELECT update_pets(1, '{ "name" : "Jerry", "age": 9 }'::jsonb);

Back in 2004, Tom showed how to insert from a plpgsql record:

http://www.postgresql.org/message-id/17840.1087670348@sss.pgh.pa.us

Is there any way to "update *" from a record?

Thanks!
Seamus

PS. Whether I **should** do this is another matter, I just want to know
if it's possible.

--
Seamus Abshere, SCEA
https://github.com/seamusabshere
http://linkedin.com/in/seamusabshere

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Soni M 2016-04-03 03:38:57 CORRUPTION on TOAST table
Previous Message Adrian Klaver 2016-04-01 18:42:04 Re: how to regenerate pg_control file ?