SQL function to get attachment from central

Showcase
1

I am close to have a 100% SQL solution to retrieve data from Central.
Here is the function to get attachment from Central.
It make use of PostgreSQL COPY FROM COMMAND capability.
We will use the curl command as if we wanted to load a csv into a table. So the server needs curl to be installed and ca-certificates too.

The function creates a temporary table to welcome the command's return, and takes parameters to build the curl command.
Comments and advices are welcome ;-), about for example error handling...

CREATE SCHEMA tools; --if you want to store it in a dedicated schema.
CREATE OR REPLACE FUNCTION tools.get_file_from_central_api(
	unique_id uuid,
	email text,
	password text,
	output text, --where the user postgres has writing rights
	url text,
	schema_name text,
	table_name text
)
    RETURNS void AS 
$BODY$
BEGIN
EXECUTE format('CREATE TEMP TABLE IF NOT EXISTS '||table_name||'(message text) ON COMMIT DROP;');
EXECUTE format('COPY '||table_name||' FROM PROGRAM ''curl --user "'||email||':'||password||'" -o '||output||' "'||url||'"'';');
END;
$BODY$
LANGUAGE plpgsql;

SELECT tools.get_file_from_central_api('5de3ee7b-8f3b-4b80-9dcb-e4cbc1ec7239'::uuid,'my_login_email','my_password','/postgres_availabble_dir/image.jpg','https://my_central_server.fr/v1/projects/4/forms/Sicen/Submissions/uuid:5de3ee7b-8f3b-4b80-9dcb-e4cbc1ec7239/attachments/1611941389030.jpg','public','files');

I Think I will soon be able to publish the final version of our "generalist" form with the whole SQL code to use it within PostgreSQL :slight_smile:. A finalized version of this thread : SQL first try to get Central data into internal PostGIS database

2 Likes