1

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?

0

1 Answer 1

3

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.