Re: plpgsql update row from record variable

[Adrian Klaver <adrian.klaver@xxxxxxxxxxx>]

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@xxxxxxxxxxxxx
>
> 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@xxxxxxxxxxx


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general