multiple sql results to shell

On Mon, Oct 23, 2017 at 7:08 AM, Mark Lybarger <mlybarger@gmail.com> wrote:

I have this bash/sql script which outputs some curl commands. the
backticks causes it to get interpreted by the shell. This works fine if
there is one result, but when there are many rows returned, it looks like
one shell command.

any help on getting multiple rows returned to be executed by the shell
would be appreciated!

thanks!

`psql -P "tuples_only=on" -h ${DB_HOST} -d ${DB_NAME} -U ${DB_USER} -c
"select 'curl -X POST http://${REGISTER_HOST}:8080/&#39; || source_id ||
'/${MT}/' || model || '/' || site || '/backoffice/register' from
myschema.events where source_id = $SOURCE_ID and ineffective_date is null"`

​You will need to, instead, "SELECT source_id, model, site​ FROM ..." to
return the raw record data to bash and then use bash's loop facilities to
dynamically generate and execute the curl command.

A second option, that I've never tried, is returning the full string but
not within a backtick command, then using bash looping simply invoke the
string like a normal command.

David J.

On 23 October 2017 at 15:08, Mark Lybarger <mlybarger@gmail.com> wrote:

I have this bash/sql script which outputs some curl commands. the backticks
causes it to get interpreted by the shell. This works fine if there is one
result, but when there are many rows returned, it looks like one shell
command.

any help on getting multiple rows returned to be executed by the shell would
be appreciated!

I tend to do

psql ..... | while read a; do
# some code
done

The only problem I find with this is that you can't pass variables out
of the while loop, because the pipe runs as a subshell. You could of
course use echo and encapsulate the whole thing, eg this would take
your results and (assuming they're integers) return the largest -
obviously your own code could decide differently how to output things.
You also have to move the read to later on, so you can send your
output to the parent.

myres=$(
a=0
biga=0
psql -tqAX -h ${DB_HOST} -d ${DB_NAME} -u ${DB_USER} -c "select
'curl -X POST http://${REGISTER_HOST}:8080/&#39; || source_id || '/${MT}/'
|| model || '/' || site || '/backoffice/register' from
myschema.events where source_id = $SOURCE_ID and ineffective_date is
null" | while true; do
if [ $a -gt $biga ] ; then
biga=$a
fi
if ! read a; then echo $biga; break; fi
done
)

A mess, but it works.

To be honest, by the time you've got to this level of complexity you
probably shouldn't be using shellscript any more.

Geoff

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Oct 23, 2017, at 08:37, pgsql-general-owner@postgresql.org<mailto:pgsql-general-owner@postgresql.org> wrote:

psql ..... | while read a; do
# some code
done

The only problem I find with this is that you can't pass variables out
of the while loop,

To get input from a file w/o a sub-shell,
you can put the input at the end of the loop:

====
#!/bin/sh

cat > file <<EOF
ab
cd
ef
EOF
while read a; do
b="$b $a"
echo $b
done < file
====
-Randy

On 23 October 2017 at 23:14, Randy Strauss <rstr@stanford.edu> wrote:

To get input from a file w/o a sub-shell,
you can put the input at the end of the loop:

That's a good point: and it also jogged my memory that in bash you can
do process substitution, so

while read a; do $a; done < <(psql -tqAX -c 'SELECT ...')

will keep the read loop in the main process, not the child (unlike the
"| while read" syntax)

Geoff

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin