Wow. Sorry. what a mess.On 3/16/2015 4:45 PM, Andy Colson wrote:
On 3/16/2015 4:30 PM, David G. Johnston wrote:
psql "$SERVICE" \
--echo-queries \
--set=string_input="${1:-ok_to_return}" \
--set=start="${2:-5}" \
--set=end="${3:-10}" \
<<'SQL'
SELECT idx
FROM generate_series(1, 20) gs (idx)
WHERE 'short-circuit' != :'string_input'
AND idx BETWEEN :start AND :end;
SQL
# (6 rows)
--set=end="${3:-10 AND false}"
# (0 rows)
Am I forced to represent the input as text (using :'end') and then
perform a conversion to integer?
Thanks!
David J.
The --set's make it a little complicated. How about:
string_input="${1:-ok_to_return}"
start="${2:-5}"
end="${3:-10}"
psql "$SERVICE" --echo-queries <<'SQL'
prepare tmp as SELECT idx
FROM generate_series(1, 20) gs (idx)
WHERE 'short-circuit' != $1
AND idx BETWEEN $2 AND :$3;
execute tmp($string_input, $start, $end);
deallocate tmp;
SQL
That's untested, and probably wont work. The "execute tmp($1, $2, $3)"
need to be passed to psql as-is, but $string_input, $start and $end need
to be replaced in bash before its sent to psql. Maybe use \$1?
Docs here:
http://www.postgresql.org/docs/9.4/static/sql-prepare.html
-Andy
> AND idx BETWEEN $2 AND :$3;
should be:
AND idx BETWEEN $2 AND $3;
> That's untested, and probably wont work. The "execute tmp($1, $2, $3)"
should be: execute tmp($string_input, $start, $end);
-Andy
Thanks! I got the gist even with the typo. I actually pondered about prepare/execute after hitting send. Am I correct in remembering that "CREATE TEMP TABLE" cannot be prepared? I was using the actual query with CREATE TEMP TABLE and then issuing "\copy" to dump the result out to the file. The limitation of copy to having to be written on a single line makes the intermediary temporary table seem almost a necessity.
I'd rather write the :'start'::integer than go through the prepare/execute cycle...
David J.
