0

I want to export updated records to json files. For this purpose I defined a trigger as follow:

CREATE OR REPLACE FUNCTION test() RETURNS trigger AS
$BODY$

DECLARE
    directory text := 'dblogs/me.json';
    jsonText text;
    copy record;
BEGIN
    directory := getNextId() || '.json';
    copy := NEW;
    jsonText := row_to_json(copy, true)::text;

    PERFORM pg_catalog.pg_file_write(directory, jsonText, true);

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

CREATE TRIGGER last_name_changes
  AFTER UPDATE
  ON fava_config
  FOR EACH ROW
  EXECUTE PROCEDURE test();

getNextId method, get directory, If directory don't exist, it create directory and the get directory. Its as follow:

CREATE OR REPLACE FUNCTION getNextId() RETURNS VARCHAR(200) AS
$BODY$

DECLARE
    mfId integer;
    rootDir varchar(50) := 'mfdata/';
    createDir text := 'mkdir --mode=777 -p mfdata';
    dir varchar(100);
BEGIN
    INSERT INTO history(time) values(now()) RETURNING id INTO mfId;
    EXECUTE('COPY (SELECT 1) TO PROGRAM ' || quote_literal(createDir));
    RETURN rootDir || mfId;
END; 
$BODY$ 
LANGUAGE plpgsql;

When I update a record in fava_config, It occur an error:

enter image description here

How Can I Fix the Problem?

  • I don't sure but you can try to prepare the sql first than execute only. Besides, if your command is fixed, why you put in into your query also? – Mabu Kloesen Jan 27 '18 at 8:18
  • @MabuKloesen My directory changed dynamcially, I remove it here. I want to create directory from mfId. – Morteza Malvandi Jan 27 '18 at 8:26
  • so please try to prepare the query first, then execute that variable only. – Mabu Kloesen Jan 27 '18 at 8:34
  • 1
    I would highly suggest not doing this. It's a bad design. Triggers aren't for I/O outside of the database. If you need to notify the outside world, use a daemon and LISTEN/NOTIFY – Evan Carroll Jan 27 '18 at 20:49
  • use one of the "unsafe" languages instead, they have better file manipulation capabilities. eg plsh or plperlu etc... – Jasen Nov 12 '18 at 3:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.