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": 1,
"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
