Postgresql – How to use a dynamic command with copy from program

copypostgresql

I'm trying to read a JSON API with the Postgres copy from program command.

When I call

copy _temp from program 'curl -uri "https://url"';

it works fine but the API has paging and I need to loop through multiple calls.

When I call it like this:

_command := 'curl -uri "https://url?&page='||(10::text)||"';
copy _temp from program _command;`

I get

ERROR:  syntax error at or near "_command"

You can't even concatenate in place like

copy _temp from program 'curl -uri "https://url?&page='||(10::text)||'"';

Percent parametric replacement in the raise notice style doesn't work either.

What gives? program is a literal string with single quotes so what is the difference between specifying a literal string and using a text or varchar variable? There doesn't seem to be any program data type (::program cast does nothing), what am I missing?

In the docs it says 'it is best to use a fixed command string' not that you can only use a fixed command string…

How do I use a dynamic command string?

Best Answer

If you want to use dynamic commands, you need the right context for it. Plain SQL is not one.

One possibility is using PL/pgSQL, and it's EXECUTE functionality. Use DO to get into the context:

DO $$
DECLARE page integer;
BEGIN
FOR page IN SELECT i FROM generate_series(1, 10) AS t(i)
LOOP
    EXECUTE format($cmd$ COPY _temp 
                         FROM PROGRAM 'curl -uri "https://url?&page=%s"'$cmd$,
                   page::text);
END LOOP;
END; $$;

The other way is to run the loop on the shell side. The following example is using bash:

for i in $(seq 1 10); do
    psql -c "COPY _temp FROM PROGRAM 'curl -uri https://url?&page=${i}'"
done

Notes:

I am using two levels of dollar-quoting ($$ and $cmd$ ) to prevent quote multiplication.
I am also using format() instead of the messy concatenation.
If you really have to quote the URI of the curl command, you will have to escape the quotes in there.

Related Solutions

Postgresql – Automate COPY command – Postgres 9.2

The scripting answers above will obviously work.

But, assuming you have to, have to, have it executed from within the DB (let us know why :) ), the code below (minus the loop, etc.) should get you on your way.

You were very close btw.

CREATE or REPLACE FUNCTION function_1(rows text)
RETURNS void AS $$
begin
  execute 'COPY (
    SELECT * 
      FROM backup_table
     WHERE id = ' || rows || ' 
       AND status = 1
  ) TO ''/var/lib/pgsql/'||rows||'.dat''';
end
$$ language 'plpgsql';

EDIT 1: function loop example and formatting

CREATE or REPLACE FUNCTION function_loop(rows_arr text[])
RETURNS void AS $$
declare
  crtRow text;
begin
  ForEach crtRow in array rows_arr
  LOOP
     perform function_1(crtRow);  
  end loop;
end
$$ language 'plpgsql';

Executed as

select function_loop('{1,2,3}');

Please note there are no checks being performed here.

E.g. This will create a zero byte 3.dat even if there is no id=3 in the table

EDIT 2: A more extensible option for thousands of id would be

CREATE or REPLACE FUNCTION function_loop1(startId int, endId int)
RETURNS void AS $$
declare
  allIds text[];
  crtRow text;
begin
  select array_agg(id::text) into allIds 
    from backup_table
   where status=1 
     and id between startId and endId;
  ForEach crtRow in array allIds
  LOOP
    perform function_1(crtRow);  
  end loop;
end
$$ language 'plpgsql';

Postgresql – copy command in postgresql to append data

I think you cannot (easily) do that (at least on your PostgreSQL version, see Daniel's answer). What you definitely can, however, is changing your approach, and do a UNION ALL from all those tables in the query part of your COPY. This means there is no looping, but you have to construct your query from the collected table names.

The result would look like

COPY (SELECT * FROM table1
      UNION ALL
      SELECT * FROM table2
      ...) 
    TO tmp/result.txt;

Notes:

there is no such thing as a psql function. psql is a client to PostgreSQL. It has built in commands (basically everything starting with \), however.
it is not so clear what you mean by 'archive logs'. If it is the write-ahead log (WAL), then with the above approach you don't have to worry about it. Otherwise, you can use unlogged tables.
if the tables are big, there is a chance your system will write (a lot of) temp files. This might make the execution not so fast.

Best Answer

Related Solutions

Postgresql – Automate COPY command – Postgres 9.2

Postgresql – copy command in postgresql to append data

Related Question