How output a json file in postgresql functions?

Question

I want to define a trigger in postgresql when updated a record in a db table, It output the record as a json file:

CREATE TABLE mytable(
   id serial primary key,
   first_name varchar(40) NOT NULL,
   last_name varchar(40) NOT NULL
);

CREATE OR REPLACE FUNCTION myFunction() RETURNS trigger AS
$BODY$
BEGIN

     // My Code

     RETURN NEW;
END;
$BODY$ 
LANGUAGE plpgsql
SECURITY DEFINER;

CREATE TRIGGER myTrigger AFTER UPDATE ON mytable 
    FOR EACH ROW EXECUTE PROCEDURE myFunction();

How can I do it?

Answer 1

The JSON representation of NEW in a trigger is just row_to_json(NEW), so that part is straightforward.

But writing into files on the server is a different story. It's only allowed to superusers because it gives the ability to corrupt or destroy all the instance's data.

I don't think there's a builtin function to write on the filesystem, but the contrib module adminpack provides one:

pg_file_write(filename text, data text, append boolean)

data would be row_to_json(NEW) in your case.

The file should be inside the data directory, because:

Only files within the database cluster directory can be accessed

although I think you may use symbolic links to circumvent that limitation, if using a file system that supports them.

Here's a working example:

CREATE FUNCTION mytrigger()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
 PERFORM pg_catalog.pg_file_write('outputlog', row_to_json(NEW, true)::text, true);
 return NEW;
END
$function$

The file outputlog will be created in the data directory.