dynamically generate path to output file

2022-03-26 6:35 GMT-03:00, robert@redo2oo.ch <robert@redo2oo.ch>:

Hi Friends

I would like to generate the path of a CSV file to which I output a query.

This is what I try:

CREATEORREPLACEPROCEDUREexport_cvs(
home_dir varchar
)
AS
$BODY$
DECLARE
OUTFILE varchar;
BEGIN
OUTFILE = (home_dir || '/tmp/company.csv');
copy(
select'company_'||id as"External ID",
nameas"Name",'True'as"Is a Company",
email,
phone ,
company_registry
fromres_company
) TOOUTFILE withCSV HEADER;
END;
$BODY$
LANGUAGEplpgsql;

this produces a syntax error:

psql:export_contacts_short.sql:21: ERROR: syntax error at or near
"OUTFILE"
LINE 17: ) TO OUTFILE with CSV HEADER;

It works fine when I replace "TO OUTFILE" with a hard coded string.

Can anybody of you give me a hint how to do that?

Thanks a lot.

Robert

Look at EXECUTE statement:
43.5.4. Executing Dynamic Commands
https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Osvaldo