Re: plpgsql update row from record variable

On 04/02/2016 06:07 PM, Seamus Abshere wrote:
> 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?

What version of Postgres?

In 9.5 you have the following syntax available:

http://www.postgresql.org/docs/9.5/interactive/sql-update.html

UPDATE accounts SET (contact_first_name, contact_last_name) =
(SELECT first_name, last_name FROM salesmen
WHERE salesmen.id = accounts.sales_id);

or its almost(see docs for notes) pre-9.5 equivalent:

UPDATE accounts SET contact_first_name = first_name,
contact_last_name = last_name
FROM salesmen WHERE salesmen.id = accounts.sales_id;

So on a 9.4 instance here:

test=# \d company
Table "public.company"
Column | Type | Modifiers
---------+---------------+-----------
id | integer | not null
name | text | not null
age | integer | not null
address | character(50) |
salary | real |
Indexes:
"company_pkey" PRIMARY KEY, btree (id)

UPDATE
company
SET
salary = jb.salary
FROM
(
SELECT
id,
salary
FROM
jsonb_populate_record ( NULL::company,
'{"id": 1, "age": 32, "name": "Paul", "salary": 10000,
"address": "California"}' ::jsonb ) )
AS
jb
WHERE
company.id = jb.id;

>
> 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
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com