1

I want to create a directory in PL/pgsql:

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 copy.history(time) values(now()) RETURNING id INTO mfId;
    EXECUTE('COPY (SELECT 1) TO PROGRAM ' || quote_literal('mkdir --mode=777 -p mfdata '));
    RETURN 'mfdata' || mfId;
END; 
$BODY$ 
LANGUAGE plpgsql;

but occur an error as follow:

enter image description here

Where is the problem? How can I create a directory dynamically in PL/pgSQL?

  • 1
    you should write more about your task and what you're trying to do – Evan Carroll Jan 25 '18 at 16:43
1

It looks good. There might be two issues when using COPY {FROM | TO } PROGRAM though:

  1. missing permissions on the directory you want to create the new one in. With your present code this is unlikely, as your dir will be created in the Postgres data directory. The directory would be created as owned by the OS user/group postgres, permissions set to 700.
  2. you are trying this as a non-superuser. That won't work - and also produces a different error:
ERROR:  must be superuser to COPY to or from an external program
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

Based on the above, your problem is the first case. Check if the user has write permission on the target directory.

  • 1
    Extra bold on ** directory would be created as owned by the OS user/group postgres, permissions set to 700.** that's his problem. – Evan Carroll Jan 25 '18 at 16:42

Your Answer

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

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