Обсуждение: Combine psql command with shell script

How to combine psql commands, such as "\copy", with shell script? Is there any sample code? For example, I have 10
tablesand want to user the "\copy" command to import data from 10 different text files. I can execute the "\copy"
command10 times. But it is not convenient. 

Thanks.

John Wang wrote:
> How to combine psql commands, such as "\copy", with shell script? Is there any sample code? For example, I have 10
tablesand want to user the "\copy" command to import data from 10 different text files. I can execute the "\copy"
command10 times. But it is not convenient. 
>


$ psql .... -f filename.sql

where filename.sql contains whatever stack of commands you wanna run.

In response to John Wang :
>
> How to combine psql commands, such as "\copy", with shell script? Is there any sample code? For example, I have 10
tablesand want to user the "\copy" command to import data from 10 different text files. I can execute the "\copy"
command10 times. But it is not convenient. 
>
> Thanks.

A very simple example, a shell-script to create 3 tables, create 3
text-files for COPY, and copy that files into the 3 tables:

kretschmer@tux:~/sql-test$ cat shell.sh
#!/bin/bash

for i in `seq 1 3` ; do
  psql test -c "create table table$i(i int);";
  echo -e "$i\n\\." > copy$i.copy;
  psql test -c "copy table$i from '/home/kretschmer/sql-test/copy$i.copy'";
done;


kretschmer@tux:~/sql-test$ ./shell.sh
CREATE TABLE
COPY 1
CREATE TABLE
COPY 1
CREATE TABLE
COPY 1
kretschmer@tux:~/sql-test$ psql test
Welcome to psql 8.3.6, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

test=# select * from table3;
 i
---
 3
(1 row)

test=*#



Hope that helps, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

John Wang wrote:
>
> How to combine psql commands, such as "\copy", with shell script? Is
> there any sample code? For example, I have 10 tables and want to user
> the "\copy" command to import data from 10 different text files. I can
> execute the "\copy" command 10 times. But it is not convenient.

If the 10 files go into different 10 tables, you have no option but to
call \copy 10 times anyway.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support