external_file--1.1.sql

-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION external_file" to load this file. \quit

CREATE TABLE directories (
	directory_name name NOT NULL PRIMARY KEY,
	directory_path text NOT NULL
);

REVOKE ALL ON directories FROM PUBLIC;
GRANT SELECT ON directories TO PUBLIC;

CREATE TABLE directory_roles (
	directory_name name REFERENCES directories(directory_name) ON DELETE CASCADE ON UPDATE CASCADE,
	directory_role name,
	directory_read boolean NOT NULL,
	directory_write boolean NOT NULL,
	PRIMARY KEY (directory_name,directory_role)
);

REVOKE ALL ON directory_roles FROM PUBLIC;
GRANT SELECT ON directory_roles TO PUBLIC;

-- Include tables into pg_dump
SELECT pg_catalog.pg_extension_config_dump('directories', '');
SELECT pg_catalog.pg_extension_config_dump('directory_roles', '');


CREATE TYPE efile AS (
	directory name,
	filename varchar(256)
);

REVOKE ALL ON TYPE efile FROM PUBLIC;
GRANT USAGE ON TYPE efile TO PUBLIC;


CREATE OR REPLACE FUNCTION getEfilePath(e_file efile, need_read boolean, need_write boolean)
  RETURNS text
AS $$
DECLARE
  p_path text;
  r record;
  read_enable boolean := false;
  write_enable boolean := false;
BEGIN
  IF coalesce(e_file.filename,'')='' THEN
    RAISE EXCEPTION 'Filename is empty.';
  END IF;
  IF e_file.filename ~ '(\\|/)' THEN
	RAISE EXCEPTION '/ or \ are forbidden inside filename';
  END IF;
  SELECT directory_path INTO p_path FROM directories WHERE directory_name= e_file.directory;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'Directory % don''t exist.',e_file.directory;
  END IF;
  FOR r IN 
    (SELECT directory_role,directory_read,directory_write FROM directory_roles WHERE directory_name= e_file.directory) LOOP
	IF pg_has_role(session_user,r.directory_role,'USAGE') THEN
		IF r.directory_read THEN
			read_enable := true;
		END IF;
		IF r.directory_write THEN
			write_enable := true;
		END IF;
	END IF;
  END LOOP;
  IF (need_read AND NOT read_enable) OR (need_write AND NOT write_enable) THEN
    RAISE EXCEPTION 'Missing right for this directory: %' ,e_file.directory;
  END IF;
  p_path := p_path || e_file.filename;
  RETURN p_path;
END;
$$
LANGUAGE PLPGSQL STABLE SECURITY DEFINER SET search_path = @extschema@, pg_temp;


CREATE OR REPLACE FUNCTION writeEfile(buffer bytea, e_file efile)
  RETURNS void
AS $$
DECLARE
  l_oid oid;
  lfd integer;
  lsize integer;
BEGIN
  l_oid := lo_create(0);
  lfd := lo_open(l_oid,131072); --0x00020000 write mode
  lsize := lowrite(lfd,buffer);
  PERFORM lo_close(lfd);
  PERFORM lo_export(l_oid,getEfilePath(e_file,false,true));
  PERFORM lo_unlink(l_oid);
END;
$$ LANGUAGE PLPGSQL SECURITY DEFINER SET search_path = @extschema@, pg_temp;


CREATE OR REPLACE FUNCTION readEfile(e_file efile, p_result OUT bytea)
AS $$
DECLARE
  l_oid oid;
  r record;
BEGIN
  p_result := '';
  SELECT lo_import(getEfilePath(e_file,true,false)) INTO l_oid;
  FOR r IN ( SELECT data 
             FROM pg_largeobject 
             WHERE loid = l_oid 
             ORDER BY pageno ) LOOP
    p_result = p_result || r.data;
  END LOOP;
  PERFORM lo_unlink(l_oid);
END;
$$
LANGUAGE PLPGSQL SECURITY DEFINER SET search_path = @extschema@, pg_temp;


CREATE OR REPLACE FUNCTION copyEfile(src efile, dest efile)
  RETURNS void
AS $$
DECLARE
  l_oid oid;
BEGIN
  SELECT lo_import(getEfilePath(src,true,false)) INTO l_oid;
  PERFORM lo_export(l_oid,getEfilePath(dest,false,true));
  PERFORM lo_unlink(l_oid);
END;
$$
LANGUAGE PLPGSQL SECURITY DEFINER SET search_path = @extschema@, pg_temp;

CREATE OR REPLACE FUNCTION efile_check_role()
  RETURNS trigger
AS $$
BEGIN
  PERFORM *  from pg_roles where rolname = NEW.directory_role;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'role % must exists', NEW.directory_role;
    RETURN NULL;
  END IF;
  RETURN NEW;
END;
$$
LANGUAGE PLPGSQL;

CREATE TRIGGER trg_efile_check_role
    BEFORE UPDATE OR INSERT ON directory_roles
    FOR EACH ROW
    EXECUTE PROCEDURE efile_check_role();


-- Function used to replace Oracle BFILENAME that returns efile
CREATE OR REPLACE FUNCTION efilename(directory name, filename varchar(256)) RETURNS efile
AS 'SELECT ($1, $2)::efile;'
LANGUAGE SQL STRICT;