How can one create a directory dynamically in PL/pgSQL?

Question

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?

• Evan Carroll: you should write more about your task and what you're trying to do

Answer 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.

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