How can create directory dynamically in PL/pgSQL?

Asked Jan 24 '18 at 07:42

Active Jan 24 '18 at 07:42

Viewed 399 times

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:

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

edited Jun 20 '20 at 09:12

Community

asked Jan 24 '18 at 07:42

Morteza Malvandi

1,656
7
30
73

Works for me. Does that command work for you from shell, in main directory, as postgres user? – Łukasz Kamiński Jan 24 '18 at 08:09
@ŁukaszKamiński I run this function from a trigger function(When a record changed, it run) – Morteza Malvandi Jan 24 '18 at 08:10
https://stackoverflow.com/a/39950673/5315974 `1 EPERM Operation not permitted` - user that runs posrtgres cluster has no permissions on the parent(s) directory(ies), also why not using the full path?.. – Vao Tsun Jan 24 '18 at 08:18
full path => same result. I changed parent folder permission to full access, but result is same – Morteza Malvandi Jan 24 '18 at 08:26

How can create directory dynamically in PL/pgSQL?

0 Answers0