Using shell scripting for making queries on postgres sql Post: 302900228

Sponsored Content

Top Forums Shell Programming and Scripting Using shell scripting for making queries on postgres sql Post 302900228 by JSNY on Monday 5th of May 2014 03:15:43 PM

05-05-2014

Registered User

Using shell scripting for making queries on postgres sql

I have a situation where I have a list of airplanes that make a series of flights.

Therefore I am looking up the different flights that each airplane makes based on a postgres sql query:

select flightid from plane where airplane='DELTAx'

As a result I get a series of flight numbers from this query.

Flight
------
123
567
8976
245
230
140

I currently have a script that puts these flights into an array:
Unix_Array=($(psql -h $PSQL_HOST $PSQL_RDB $PSQL_USER << EOFF
BEGIN TRANSACTION;
select flightid from plane where airplane='Deltax';
END TRANSACTION;
\q
EOFF
))
#echo ${Unix_Array[3]}
#echo ${Unix_Array[4]}
#echo ${Unix_Array[5]}
#echo ${Unix_Array[6]}

which gives an output of:

123
567
8976

Therefore Unix_Array[3]=123
Unix_Array[4]=567 and so on.

Now the second part of the script takes the flight numbers (Unix_Array[]) from the first script and should be getting a flight status from another data base called FlightReport

while [ $counter -lt $count ]
do
echo $counter
echo "flightid = ${Unix_Array[$counter]}"
Flight_Array=($(psql -h $PSQL_HOST $PSQL_RDB $PSQL_USER << EOFF
BEGIN TRANSACTION;
select status from FlightReport where flightid=${Unix_Array[$counter]};
END TRANSACTION;
\q
EOFF
))
counter=`expr $counter + 1`
done
echo "Status is ${Flight_Array[3]}"

The echo status should give me the output of the query

select status from FlightReport where flightid=123, since the value of Unix_Array[3]=123.

I am not getting any value here. Any ideas on what is going on here?

I want to be able to get flight status from FlightReport based on all the flightids that I have obtained from the plane data base. The flightids in plane and Flightreport are the same.

If anyone could help me with this that would greatly be appreciated.

Thanks,

Jason

JSNY

View Public Profile for JSNY

Find all posts by JSNY

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Sh Shell Script executing remote SQL queries

Hi there folks, I am trying to execute remote sql queries on an Oracle server. I would like to save the result of the executed sql queries on a text file, and send that text file as an attachment to an email address. Could anyone give me an idea on how the above could be achieved? Any help...

2. UNIX for Dummies Questions & Answers

shell script for sql queries

Hi All, I have written 4 sql queries . Now I want to write one SHELL SCRIPTING program for all these queries... i.e 1.select * from head; 2. select * from detail; 3. delete from head; 4. delete from detail; Please let me know how to write a shell script... Thank you

3. Shell Programming and Scripting

Nested SQL queries within Shell script

Hi, Would someone know if I can fire nested sql queries in a shell script? Basically what I am trying to do is as follows: my_sql=$(sqlplus -s /nolog<<EOF|sed -e "s/Connected. *//g" connect...

4. Shell Programming and Scripting

How to write cron job for calling sql function database is postgres

Hi, Please help me to write cron job for calling sql function daily. I have Postgres database.

5. Shell Programming and Scripting

How to store results of multiple sql queries in shell variables in ksh?

Hi, I have a script where I make a sqlplus connection. In the script I have multiple sql queries within that sqlplus connection. I want the result of the queries to be stored in shell variables declared earlier. I dont want to use procedures. Is there anyway else. Thanks in advance.. Cheers

6. Shell Programming and Scripting

Executing set of sql queries from shell script

Hi All, I tried executing set of queries from shell script but not able to capture the input query in the log file. The code looks something similar to below sqlplus user/pwd@dbname << EOF > output.log $(<inputfile.txt) EOF The above code is capturing the output of queries into...

7. Shell Programming and Scripting

How can i run sql queries from UNIX shell script and retrieve data into text docs of UNIX?

Please share the doc asap as very urgently required.

8. Shell Programming and Scripting

run sql queries from UNIX shell script.

How can i run sql queries from UNIX shell script and retrieve data into text docs of UNIX? :confused:

9. Shell Programming and Scripting

Taking information from a postgres sql query and putting it into a shell script array

I have a postgres sql statement that is the following: select age from students; which gives me the entries: Age --- 10 15 13 12 9 14 10 which is about 7 rows of data. Now what I would like to do with this is use a shell script to create an array age. As a results...

10. Shell Programming and Scripting

Storing multiple sql queries output into variable by running sql command only once

Hi All, I want to run multiple sql queries and store the data in variable but i want to use sql command only once. Is there a way without running sql command twice and storing.Please advise. Eg : Select 'Query 1 output' from dual; Select 'Query 2 output' from dual; I want to...

LEARN ABOUT DEBIAN

samizdat-role

SAMIZDAT-ROLE(1)					      General Commands Manual						  SAMIZDAT-ROLE(1)

NAME

       samizdat-role - Samizdat role management

SYNOPSIS

       samizdat-role [ OPTIONS ] --list

       samizdat-role [ OPTIONS ] --grant MEMBER

       samizdat-role [ OPTIONS ] --revoke MEMBER

DESCRIPTION

       samizdat-role Allows to view, grant, and revoke member access to priviledged roles on a Samizdat site.

OPTIONS

       --site SITE
	   Name of the Samizdat site (overrides SAMIZDAT_SITE environment variable).

       --role ROLE
	   Name of a role, default is moderator.

       --grant MEMBER
	   Grant ROLE priviledges to MEMBER. MEMBER may be specified as resource id or login name.

	   NB: To grant or revoke priviledges using this command, you must use database superuser priviledges (in PostgreSQL, this is 'postgres').

       --revoke MEMBER
	   Revoke ROLE priviledges from MEMBER. MEMBER may be specified as resource id or login name.

	   NB: To grant or revoke priviledges using this command, you must use database superuser priviledges (in PostgreSQL, this is 'postgres').

EXAMPLE

       su postgres -c 'samizdat-role --site s1 --grant joe --role moderator --list'

       Grant moderator priviledges to the member 'joe' of site 's1' and list all moderators.

AUTHOR

       This manual page was written by Dmitry Borodaenko <angdraug@debian.org>.  Permission is granted to copy, distribute and/or modify this doc-
       ument under the terms of the GNU GPL version 3 or later.

																  SAMIZDAT-ROLE(1)