Trigger functions behave just like other functions as far as privileges are concerned. With a minor exception:
To create a trigger on a table, the user must have the TRIGGER
privilege on the table. The user must also have EXECUTE
privilege on the trigger function.
UPDATE
After feedback in the comments I did some research. There is an open TODO item in the Postgres Wiki:
Tighten trigger permission checks
Linked to this thread on Postgres hackers. Currently, EXECUTE
privileges on a trigger function are only checked at trigger create time, but not at runtime. So revoking EXECUTE on the trigger function has no effect on a trigger once created. Your observation seems to be correct.
This does not grant any additional privileges to manipulate objects. If the calling role lacks privileges needed to execute (parts of) the function body, the usual exception is raised. To pave the way, you could make a privileged user OWNER
of the function and use the
SECURITY DEFINER
clause, as documented in the manual here. It causes the function to be run with the permissions of the owner instead of the invoker (default).
If the owner is a superuser, you need to be extra careful who you grant the EXECUTE
privilege and what the function can do to avoid abuse. You may want to
REVOKE ALL ON FUNCTION foo() FROM public;
to begin with and use SET search_path
for the function.
Be sure to read the chapter on Writing SECURITY DEFINER
Functions Safely.
Find a code example in this related answer on SO.
Actually, this is all you need:
NEW := jsonb_populate_record(NEW, NEW.json);
Per documentation:
jsonb_populate_record(base anyelement, from_json jsonb)
Expands the object in from_json
to a row whose columns match the
record type defined by base (see note below).
What's not documented: The row provided as first argument retains all values that are not overwritten (no matching key in the json value). I see no reason why this should change, but you cannot fully rely on it unless it's documented.
One thing to note - you wrote:
Setting the column to NULL if the corresponding json field does not
exist is fine.
This retains all values with no matching key in the JSON value, which should be even better.
If "undocumented" is too uncertain for you, use the hstore
operator #=
doing exactly the same.
NEW := (NEW #= hstore(jsonb_populate_record(NEW, NEW.json)));
The hstore
module should be installed in most systems anyway. Instructions:
Both solution can be derived from my answer that Daniel already referenced:
Function code
CREATE OR REPLACE FUNCTION json_fn()
RETURNS TRIGGER AS
$func$
BEGIN
NEW := jsonb_populate_record(NEW, NEW.json); -- or hstore alternative
RETURN NEW;
END
$func$ LANGUAGE plpgsql;
Everything else in your setup looks right, just add a PK to testy
:
CREATE TABLE testy (
id int PRIMARY KEY REFERENCES testy_index
, data jsonb NOT NULL
);
Tested in pg 9.4 and it works for me as advertised. I doubt that the PLv8 function can rival performance and simplicity.
Set other columns to NULL
As per comment:
CREATE OR REPLACE FUNCTION json_fn()
RETURNS TRIGGER AS
$func$
DECLARE
_j jsonb := NEW.json; -- remember the json value
BEGIN
NEW := jsonb_populate_record(NULL::testy, _j);
NEW.json := _j; -- reassign
RETURN NEW;
END
$func$ LANGUAGE plpgsql;
Obviously, you need to make sure that the column name or your jsonb
column does not appear as key name in the JSON value. And i wouldn't use json
as column name, since it's a data type name and that can get confusing.
Best Answer
Your attempt fails for multiple reasons. First of all a row is not an array. This construct is just not possible:
But there is more.
I am not sure I like the general idea. This kind of trigger would run for at least every statement (don't use a row-level trigger for this!), which is quite a bit of overhead. It is also error-prone to put DDL commands in a trigger. Especially if you are a beginner.
That said, here is a proof of concept:
Trigger function:
Trigger:
SQL Fiddle.
This is a basic proof of concept. It does not check for matching data types and ignores things like schema
search_path
,NOT NULL
constraints orCOLLATION
.Related (with more explanation and links):