Thread: dynamically generate path to output file

dynamically generate path to output file

From
"robert@redo2oo.ch"
Date:

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


Re: dynamically generate path to output file

From
Osvaldo Kussama
Date:
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