To be very straightforward :
your bash script will dynamically create the sql query in a string, then send it to database using psql.
For example :
Cheers,$4 -c "COPY $1 FROM '/tmp/pipe_ply_binaire_vers_ply_ascii_"$1"' WITH CSV DELIMITER AS ' ';";where $4 is the psql command to connect to db, $1 the number in the name of the file we are working in, etc
Rémi-C
2013/9/24 Steve Crawford <scrawford@xxxxxxxxxxxxxxxxxxxx>
On 09/23/2013 05:47 PM, Andreas wrote:
Assuming your main script - the one that mounts the directory and finds the file name - is in bash you can easily put a small script into a heredoc block with variable substitution:Am 24.09.2013 02:25, schrieb Adrian Klaver:
On 09/23/2013 05:19 PM, Andreas wrote:
I need to import some log-files of an application [...]
The import would be easy if the files had a constant name but the app
creates csv files with names like "ExportYYYYMMDD".
So how would I get the filenames into the sql-script?
Do man on find and look for -exec.
I could find the files and exec a shell script but how can I have a SQL script take the found filenames as parameter?
The SQL script needs to create a temp table
then COPY the file with the filename it got as parameter into the temp table
then insert from there into the log-table
How would I get the filenames into the SQL script?
some script stuff that mounts remote directory and sets variable logfilename
...
psql -your -connection -parameters <<EOS
some preliminary setup statements
\copy .... from $logfilename ...
some processing statements
EOS
The disadvantage of this approach is that it is difficult-to-impossible to detect and handle statement-level errors. But for short scripts like simple imports this may not be an issue or may be easily solved by wrapping things in a begin;...commit; block.
Cheers,
Steve
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
