Search Postgresql Archives

Re: shell script to populate array values

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxx                                             
             m>                                                         To 
             Sent by:                  pgsql-general@xxxxxxxxxxxxxx        
             pgsql-general-own                                          cc 
             er@xxxxxxxxxxxxxx                                             
                                                                   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
----------------------------------------------------------------------------------------------



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux