Обсуждение: dynamically generate path to output file
Hi Friends
I would like to generate the path of a CSV file to which I output a query.
This is what I try:
CREATE OR REPLACE PROCEDURE export_cvs(
home_dir varchar
)
AS
$BODY$
DECLARE
OUTFILE varchar;
BEGIN
OUTFILE = (home_dir || '/tmp/company.csv');
copy (
select 'company_'||id as "External ID",
name as "Name",'True' as "Is a Company",
email,
phone ,
company_registry
from res_company
) TO OUTFILE with CSV HEADER;
END;
$BODY$
LANGUAGE plpgsql;
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
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