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.1087670...@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.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
