Обсуждение: 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.