executing multiple quieries

RAJ

I am new to verica please help with below question

i want to execute multiple queries in vertica from shell scripting. is there any way i can do by just logging once and execute the next queries or statements one after the other
want to write below code in shell
ex connect to db
pset format unaligned
pset footer
select stmt
dml operation
select stmt
.
.
.
.
disonnect


i tried using different commands but was not successful. please answer

knicely87

Maybe something like this?

[dbadmin@vertica01 ~]$ vsql -Atc "select * from test;insert into test values ('Hi');commit;select * from test;"
Hi

Ben_Vandiver

vsql -c runs all the SQL but only returns the results from the last query.

$ vsql -Atc "select 1; select 2; select 3;"  3

If you want all of the results, use pipes:

$ echo "select 1; select 2; select 3;" | vsql -At  1  2  3    

For vsql scripts that want to run lot of queries, I've had some good luck using \o:

for x in system nodes schemata; do echo -e "\o /tmp/vsql-$x.out\nselect * from $x;\n\o\n"; done | vsql -At

id10t

Another option its redirection (almost same as example 1 of Ben Vandiver):

daniel@synapse:~$ vsql << EOF
> \pset format unaligned
> \pset footer
> select 1;
> insert into table1 values (4, 'AA', '2015-01-01 12:00:00');
> commit;
> select * from table1 where id = 4;
> \q
> EOF
Output format is unaligned.
Default footer is off.
?column?
1
OUTPUT
1
COMMIT
id|col1|date
4|AA|2015-01-01 12:00:00

Cheers