CODEWITHSUNDEEP
postgresqlplpgsql
kallem
kallem

Reputation: 811

Dynamically-generated table-name in PostgreSQL COPY command

This PostgreSQL COPY command works:

copy tablename from E'c:\\abc\\a.txt';

but I want the tablename to be dynamically generated. How can I do this?

Upvotes: 6

Views: 12111

Answers (2)

jian
jian

Reputation: 4824

You can copy from multi csv files to an table via shell script.

  1. Make an script file: vim csvtotable

  2. write csvtotable script. Here is my example:

    #!/bin/sh DBNAME=postgres files=$1 for file in ${files}; do psql -d ${DBNAME} -c "\copy parent_tree(parent_id, some_text) FROM '${file}' delimiters ',' csv header" done

  3. Execute the script.

    ./csv2table "$(ls *.out.csv)"

Obviously, Local CSV file should be match with table. Then It will import from csv to database tables if csv file name ending with .out.csv. I am not sure csv name match is global or just at present directory match. Reference link:
-c command reference https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-PATTERNS chomd command: https://linuxize.com/post/chmod-command-in-linux/
bash shanebang: https://linuxize.com/post/bash-shebang/

Upvotes: 0

John Powell
John Powell

Reputation: 12571

You need to build a string, concatenating in the dynamic table name, and then use execute. Note that you escape the ' by ''. This also includes a dynamic name to save the file too. You need to replace savedir with the actual directory you are using. 

CREATE OR REPLACE FUNCTION dynamicCopy(tablename text, outname text) RETURNS VOID AS $$

DECLARE STATEMENT TEXT;

BEGIN

  STATEMENT := 'COPY (select * from ' || quote_ident(tablename) || ') to ''savedir' || outname ||'.txt''';

  EXECUTE STATEMENT;

END;

$$ LANGUAGE 'plpgsql';

EDIT:

Since I first wrote this, I have discovered the format function, which I think is generally easier to read than SQL generated with the concatenation operator || and more flexible.

CREATE OR REPLACE FUNCTION dynamicCopy(tablename text, outname text) RETURNS VOID AS 
$BODY$
BEGIN
  EXECUTE FORMAT('COPY (SELECT * FROM %s) TO ''savedir%s.csv''',
                  tablename, 
                  outname);
END
$BODY$ 
LANGUAGE plpgsql;

See the official docs for a full discussion: https://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Upvotes: 16

Related Questions