Thread: shell script to populate array values
Hello,
I would like to create a shell script that would populate two variables with
the return of a SELECT statement that would return two attributes...
For example...
#!/bin/bash
SERVER_NAMES=`psql Admin -Upostgres -hMYSERVER -t -c"SELECT servername,
instanceport from server where serverclass = 3 and isactive = 'True' ";`
As you can see, I'm returning the servername "and" the instanceport from the
server table. This will later allow me to create psql commands to connect
to each server dynamically.
I had the script working correctly when I was just running it for the server
name as below...
#!/bin/bash
SERVER_NAMES=`psql Admin -Upostgres -hMYSERVER -t -c"SELECT servername from
server where serverclass = 3 and isactive = 'True' ";`
Does anyone know the easiest way to get both values out of some variables
that I could set?
Later in the script, I'm creating a for loop and iterating through the
server names and would like to build connection strings dynamically with the
results from the select string...
for i in $SERVER_NAMES
do
psql -Upostgres -h$i -p$i -A -t -c"SELECT '$i', '$BATCHTIME', name,
setting, category, short_desc, context, vartype, source, min_val, max_val
FROM pg_settings;" | psql Admin -Upostgres -hMYSERVER -t -c"COPY
serverconfigsetting FROM STDIN WITH DELIMITER '|' NULL AS '';"
echo "Done with $i"
done
As you can see I have "-h$i -p$i" in the script for the host and port.
Again the script worked fine when I just had the -h@i in there...
I know that the current forloop is incorrect specifiying the $i twice but I
just put that in there to show an example of what I was hoping to do... It
would probably be more accessing the array value like -h$i[0:0] -p$i[0:1] in
pseudo code for accessing array values.
Thanks in advance,
Paul
--
View this message in context: http://www.nabble.com/shell-script-to-populate-array-values-tf2796502.html#a7802638
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
You should be able to use something like this in a bash script:
psql -U postgres -hMYSERVER --quiet --no-align --field-separator ' ' -t -c
"SELECT servername,instanceport from server where serverclass = 3 and
isactive = 'True'" Admin |
while read -a SVRDATA ;do
echo "name: ${SVRDATA[0]} port: ${SVRDATA[1]}"
# array item SVRDATA[0] is the name, SVRDATA[1] is the port
#do whatever you want with the name and port inside the loop
done
Notice that you are piping the output of the psql command into the while
loop. Also, the field-separator is a space, which makes 'read' happy.
Susan Cassidy
Paul Silveira
<plabrh1@gmail.co
m> To
Sent by: pgsql-general@postgresql.org
pgsql-general-own cc
er@postgresql.org
Subject
[GENERAL] shell script to populate
12/11/2006 12:42 array values
PM
|-------------------|
| [ ] Expand Groups |
|-------------------|
Hello,
I would like to create a shell script that would populate two variables
with
the return of a SELECT statement that would return two attributes...
For example...
#!/bin/bash
SERVER_NAMES=`psql Admin -Upostgres -hMYSERVER -t -c"SELECT servername,
instanceport from server where serverclass = 3 and isactive = 'True' ";`
As you can see, I'm returning the servername "and" the instanceport from
the
server table. This will later allow me to create psql commands to connect
to each server dynamically.
I had the script working correctly when I was just running it for the
server
name as below...
#!/bin/bash
SERVER_NAMES=`psql Admin -Upostgres -hMYSERVER -t -c"SELECT servername from
server where serverclass = 3 and isactive = 'True' ";`
Does anyone know the easiest way to get both values out of some variables
that I could set?
Later in the script, I'm creating a for loop and iterating through the
server names and would like to build connection strings dynamically with
the
results from the select string...
for i in $SERVER_NAMES
do
psql -Upostgres -h$i -p$i -A -t -c"SELECT '$i',
'$BATCHTIME', name,
setting, category, short_desc, context, vartype, source, min_val, max_val
FROM pg_settings;" | psql Admin -Upostgres -hMYSERVER -t -c"COPY
serverconfigsetting FROM STDIN WITH DELIMITER '|' NULL AS '';"
echo "Done with $i"
done
As you can see I have "-h$i -p$i" in the script for the host and port.
Again the script worked fine when I just had the -h@i in there...
I know that the current forloop is incorrect specifiying the $i twice but I
just put that in there to show an example of what I was hoping to do... It
would probably be more accessing the array value like -h$i[0:0] -p$i[0:1]
in
pseudo code for accessing array values.
Thanks in advance,
Paul
--
View this message in context:
http://www.nabble.com/shell-script-to-populate-array-values-tf2796502.html#a7802638
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
----------------------------------------------------------------------------------------------
Simply protected storage solutions ensure that your information is
automatically safe, readily available and always there, visit us at http://www.overlandstorage.com
----------------------------------------------------------------------------------------------
Thanks Susan... I really appreciate your answer and helping me do what I wanted to do... I posted the code I was working on here... http://people.planetpostgresql.org/paul/ It's a pretty cool script and although it's not even done yet, I'm sure it could be useful to anyone wanting to do the same thing... -Paul Paul Silveira wrote: > > Hello, > > I would like to create a shell script that would populate two variables > with the return of a SELECT statement that would return two attributes... > > For example... > > #!/bin/bash > SERVER_NAMES=`psql Admin -Upostgres -hMYSERVER -t -c"SELECT servername, > instanceport from server where serverclass = 3 and isactive = 'True' ";` > > > As you can see, I'm returning the servername "and" the instanceport from > the server table. This will later allow me to create psql commands to > connect to each server dynamically. > > I had the script working correctly when I was just running it for the > server name as below... > #!/bin/bash > SERVER_NAMES=`psql Admin -Upostgres -hMYSERVER -t -c"SELECT servername > from server where serverclass = 3 and isactive = 'True' ";` > > > Does anyone know the easiest way to get both values out of some variables > that I could set? > > Later in the script, I'm creating a for loop and iterating through the > server names and would like to build connection strings dynamically with > the results from the select string... > > > > for i in $SERVER_NAMES > do > psql -Upostgres -h$i -p$i -A -t -c"SELECT '$i', '$BATCHTIME', name, > setting, category, short_desc, context, vartype, source, min_val, max_val > FROM pg_settings;" | psql Admin -Upostgres -hMYSERVER -t -c"COPY > serverconfigsetting FROM STDIN WITH DELIMITER '|' NULL AS '';" > echo "Done with $i" > done > > > As you can see I have "-h$i -p$i" in the script for the host and port. > Again the script worked fine when I just had the -h@i in there... > > I know that the current forloop is incorrect specifiying the $i twice but > I just put that in there to show an example of what I was hoping to do... > It would probably be more accessing the array value like -h$i[0:0] > -p$i[0:1] in pseudo code for accessing array values. > > Thanks in advance, > > Paul > > > > > > -- View this message in context: http://www.nabble.com/shell-script-to-populate-array-values-tf2796502.html#a7837622 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
I wonder if I could ask another question on this thread...
How would i get the latest ID value of a table in psql and then use that
value as part of an insert statement...
For example...
I would like ot declare a variable in a shell script and then use that value
in the insert statement later in the script...
1) set the variable...
SERVERCOLLECTIONTIMEID = `psql Admin -Upostgres -hMYSERVER -t -c"INSERT INTO
servercollectiontime(batchtime) VALUES('$BATCHTIME'); SELECT
currval('servercollectiontime_servercollectiontimeid_seq');"`
2) use the variable...
psql -Upostgres -hMYSERVER -t -c"SELECT '$SERVERCOLLECTIONTIMEID', column1
FROM mytable;"
The reason why I want to use the variable is because I want to eventually
insert that data into a table that is looking for that ID value.
Thanks in Advance...
Paul Silveira wrote:
>
> Hello,
>
> I would like to create a shell script that would populate two variables
> with the return of a SELECT statement that would return two attributes...
>
> For example...
>
> #!/bin/bash
> SERVER_NAMES=`psql Admin -Upostgres -hMYSERVER -t -c"SELECT servername,
> instanceport from server where serverclass = 3 and isactive = 'True' ";`
>
>
> As you can see, I'm returning the servername "and" the instanceport from
> the server table. This will later allow me to create psql commands to
> connect to each server dynamically.
>
> I had the script working correctly when I was just running it for the
> server name as below...
> #!/bin/bash
> SERVER_NAMES=`psql Admin -Upostgres -hMYSERVER -t -c"SELECT servername
> from server where serverclass = 3 and isactive = 'True' ";`
>
>
> Does anyone know the easiest way to get both values out of some variables
> that I could set?
>
> Later in the script, I'm creating a for loop and iterating through the
> server names and would like to build connection strings dynamically with
> the results from the select string...
>
>
>
> for i in $SERVER_NAMES
> do
> psql -Upostgres -h$i -p$i -A -t -c"SELECT '$i', '$BATCHTIME', name,
> setting, category, short_desc, context, vartype, source, min_val, max_val
> FROM pg_settings;" | psql Admin -Upostgres -hMYSERVER -t -c"COPY
> serverconfigsetting FROM STDIN WITH DELIMITER '|' NULL AS '';"
> echo "Done with $i"
> done
>
>
> As you can see I have "-h$i -p$i" in the script for the host and port.
> Again the script worked fine when I just had the -h@i in there...
>
> I know that the current forloop is incorrect specifiying the $i twice but
> I just put that in there to show an example of what I was hoping to do...
> It would probably be more accessing the array value like -h$i[0:0]
> -p$i[0:1] in pseudo code for accessing array values.
>
> Thanks in advance,
>
> Paul
>
>
>
>
>
>
--
View this message in context: http://www.nabble.com/shell-script-to-populate-array-values-tf2796502.html#a7844387
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
What's stopping you from using the variable? It works fine for me.
The only problem I see is that you are quoting an integer value ("SELECT
'$SERVERCOLLECTIONTIMEID', column1 FROM mytable;") for no reason (leave off
the single quotes around $SERVERCOLLECTIONTIMEID), although it does not
seem to keep it from working on my system, and the actual SELECT statement
is probably not what you really intended. I assume you really meant to use
something with a WHERE clause in it, instead of using the value as an
embedded literal. Something like:
ANSWER=`psql -U postgres --quiet --no-align --field-separator ' ' -t -c
"select txtval1 from test1 where id1 = $SERVERCOLLECTIONTIMEID;" Admin`
Full sample tested (without showing my declarations for user and database)
in a sample table in one of my db's:
SOMEVAL='some example text'
SERVERCOLLECTIONTIMEID=`psql -U $MYUSR -t -c "insert into test1 (txtval1)
values ('$SOMEVAL');select currval('test1_id1_seq');" $MYDB `
echo "SERVERCOLLECTIONTIMEID is $SERVERCOLLECTIONTIMEID"
#simple retrieve:
ANSWER=`psql -U $MYUSR --quiet --no-align -t -c "select id1, txtval1 from
test1 where id1 = $SERVERCOLLECTIONTIMEID;" $MYDB`
echo "ANSWER is $ANSWER"
#another way to retrieve the data:
IFS=\|
psql -U $MYUSR --quiet --no-align -t -c "select id1, txtval1 from test1
where id1 = $SERVERCOLLECTIONTIMEID;" $MYDB |
while read COL1 COL2; do
echo "Col1: $COL1, Col2: $COL2"
done
Produces output:
SERVERCOLLECTIONTIMEID is 16
ANSWER is 16|some example text
Col1: 16, Col2: some example text
This is really not a PostgreSQL question, just a bash-scripting question.
You could also SELECT one column at a time into one variable, without
having to worry about splitting the columns into separate variables.
Susan Cassidy
Paul Silveira
<plabrh1@gmail.co
m> To
Sent by: pgsql-general@postgresql.org
pgsql-general-own cc
er@postgresql.org
Subject
Re: [GENERAL] shell script to
12/12/2006 03:35 populate array values
PM
|-------------------|
| [ ] Expand Groups |
|-------------------|
I wonder if I could ask another question on this thread...
How would i get the latest ID value of a table in psql and then use that
value as part of an insert statement...
For example...
I would like ot declare a variable in a shell script and then use that
value
in the insert statement later in the script...
1) set the variable...
SERVERCOLLECTIONTIMEID = `psql Admin -Upostgres -hMYSERVER -t -c"INSERT
INTO
servercollectiontime(batchtime) VALUES('$BATCHTIME'); SELECT
currval('servercollectiontime_servercollectiontimeid_seq');"`
2) use the variable...
psql -Upostgres -hMYSERVER -t -c"SELECT '$SERVERCOLLECTIONTIMEID', column1
FROM mytable;"
The reason why I want to use the variable is because I want to eventually
insert that data into a table that is looking for that ID value.
Thanks in Advance...
Paul Silveira wrote:
>
> Hello,
>
> I would like to create a shell script that would populate two variables
> with the return of a SELECT statement that would return two attributes...
>
> For example...
>
> #!/bin/bash
> SERVER_NAMES=`psql Admin -Upostgres -hMYSERVER -t -c"SELECT servername,
> instanceport from server where serverclass = 3 and isactive = 'True' ";`
>
>
> As you can see, I'm returning the servername "and" the instanceport from
> the server table. This will later allow me to create psql commands to
> connect to each server dynamically.
>
> I had the script working correctly when I was just running it for the
> server name as below...
> #!/bin/bash
> SERVER_NAMES=`psql Admin -Upostgres -hMYSERVER -t -c"SELECT servername
> from server where serverclass = 3 and isactive = 'True' ";`
>
>
> Does anyone know the easiest way to get both values out of some variables
> that I could set?
>
> Later in the script, I'm creating a for loop and iterating through the
> server names and would like to build connection strings dynamically with
> the results from the select string...
>
>
>
> for i in $SERVER_NAMES
> do
> psql -Upostgres -h$i -p$i -A -t -c"SELECT '$i',
'$BATCHTIME', name,
> setting, category, short_desc, context, vartype, source, min_val, max_val
> FROM pg_settings;" | psql Admin -Upostgres -hMYSERVER -t -c"COPY
> serverconfigsetting FROM STDIN WITH DELIMITER '|' NULL AS '';"
> echo "Done with $i"
> done
>
>
> As you can see I have "-h$i -p$i" in the script for the host and port.
> Again the script worked fine when I just had the -h@i in there...
>
> I know that the current forloop is incorrect specifiying the $i twice but
> I just put that in there to show an example of what I was hoping to do...
> It would probably be more accessing the array value like -h$i[0:0]
> -p$i[0:1] in pseudo code for accessing array values.
>
> Thanks in advance,
>
> Paul
>
>
>
>
>
>
--
View this message in context:
http://www.nabble.com/shell-script-to-populate-array-values-tf2796502.html#a7844387
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
----------------------------------------------------------------------------------------------
Simply protected storage solutions ensure that your information is
automatically safe, readily available and always there, visit us at http://www.overlandstorage.com
----------------------------------------------------------------------------------------------
Thanks again Susan. I agree that this is truly a shell scripting question.
I placed it here because I was hoping that someone (like yourself :) ) would
have easy examples of other things they've done in the psql space.
I actually did want to insert a litteral version of the variable. I found
my problem by indirectly noticing somethign in your example.
My code wasn't working because of a "Space" character between the "=" and
"psql". I'm not an expert in shell scripting and I wasn't aware that a
space would actually make a difference..
example...
## Code
SERVERCOLLECTIONTIMEID = `psql Admin -Upostgres -hMYSERVER -t -c"INSERT INTO
servercollectiontime(batchtime) VALUES('$BATCHTIME'); SELECT
currval('servercollectiontime_servercollectiontimeid_seq');"`
echo "SERVERCOLLECTIONTIMEID is: $SERVERCOLLECTIONTIMEID"
## RETURN
SERVERCOLLECTIONTIMEID is:
(you'll notice that there is just whitespace after the is)
If I change the code to remove the space around the "="
SERVERCOLLECTIONTIMEID=`psql ...
I get this...
SERVERCOLLECTIONTIMEID is: 11
now that the variable is set correctly, it works in my insert statement
further in the code...
I actually did want to insert the literal value of the variable because that
will end up being a foreign key to this table...
Thanks for all the help susan. I will update my blog to include this newest
version.. Also, thanks for the multiple examples in your last post. Those
are definitely going to come in handy for some of my other development...
-Paul
Paul Silveira wrote:
>
> Hello,
>
> I would like to create a shell script that would populate two variables
> with the return of a SELECT statement that would return two attributes...
>
> For example...
>
> #!/bin/bash
> SERVER_NAMES=`psql Admin -Upostgres -hMYSERVER -t -c"SELECT servername,
> instanceport from server where serverclass = 3 and isactive = 'True' ";`
>
>
> As you can see, I'm returning the servername "and" the instanceport from
> the server table. This will later allow me to create psql commands to
> connect to each server dynamically.
>
> I had the script working correctly when I was just running it for the
> server name as below...
> #!/bin/bash
> SERVER_NAMES=`psql Admin -Upostgres -hMYSERVER -t -c"SELECT servername
> from server where serverclass = 3 and isactive = 'True' ";`
>
>
> Does anyone know the easiest way to get both values out of some variables
> that I could set?
>
> Later in the script, I'm creating a for loop and iterating through the
> server names and would like to build connection strings dynamically with
> the results from the select string...
>
>
>
> for i in $SERVER_NAMES
> do
> psql -Upostgres -h$i -p$i -A -t -c"SELECT '$i', '$BATCHTIME', name,
> setting, category, short_desc, context, vartype, source, min_val, max_val
> FROM pg_settings;" | psql Admin -Upostgres -hMYSERVER -t -c"COPY
> serverconfigsetting FROM STDIN WITH DELIMITER '|' NULL AS '';"
> echo "Done with $i"
> done
>
>
> As you can see I have "-h$i -p$i" in the script for the host and port.
> Again the script worked fine when I just had the -h@i in there...
>
> I know that the current forloop is incorrect specifiying the $i twice but
> I just put that in there to show an example of what I was hoping to do...
> It would probably be more accessing the array value like -h$i[0:0]
> -p$i[0:1] in pseudo code for accessing array values.
>
> Thanks in advance,
>
> Paul
>
>
>
>
>
>
--
View this message in context: http://www.nabble.com/shell-script-to-populate-array-values-tf2796502.html#a7880857
Sent from the PostgreSQL - general mailing list archive at Nabble.com.