Postgresql – How to one create a directory dynamically in PL/pgSQL

plpgsqlpostgresql

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?

Best Answer

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.

Related Question