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
Wow. Sorry. what a mess.
> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
