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
----------------------------------------------------------------------------------------------
