Is it possible to use variables in a postgresql copy from program URL?

Question

im importing data in postgresql with the following command:

COPY test FROM PROGRAM 'curl https://example.com/events/123&start_date=20210331T191500Z' ;

i have to manually change the "&startdate=***" to only get the data from the last hour everytime i import something. I would like to know if there is a variable i could insert in the "startdate" part of the url so it always corresponds to an hour past the current time. Thanks in advance.

score 2 · Accepted Answer · answered Apr 28 '21 at 02:47

Parameters can only be used with SELECT, INSERT, UPDATE and DELETE in PostgreSQL.

If you need a parameterized COPY statement, you have to construct an SQL string using the parameter and send that. How exactly you construct a query string depends on the programming language you are using.

In PL/pgSQL, the standard stored procedure language of PostgreSQL, it would for example look like

EXECUTE format(
           'COPY test FROM PROGRAM ''curl https://example.com/events/123&start_date=%s''',
           '20210331T191500Z'
        );

Is it possible to use variables in a postgresql copy from program URL?

1 Answers1