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.klaver@aklaver.com