Is there some way to make psql
separate the fields and records by \0
, aka NUL? It's the only way to be able to pass arbitrary data to Bash scripts.
Based on Matthew Wood's answer, I would expect this to print more that 1
on a newly initialized database:
declare -i count=0
echo "\pset recordsep '\000'
\f '\000'
select typname from pg_type" | \
sudo -iu postgres psql --no-align --quiet --tuples-only -d dbname -U username | while IFS= read -r -d ''
do
#echo "$REPLY"
let count++
done
if [ -n "$REPLY" ]
then
#echo "$REPLY"
let count++
fi
echo $count
Workaround: Iff the SELECT
results are unique, you can use this workaround to handle one at a time:
next_record() {
psql --no-align --quiet --tuples-only -d dbname -U username <<SQL
SELECT colname
FROM tablename
WHERE colname > '${1}'
ORDER BY colname
LIMIT 1
SQL
}
last_col=
while true
do
colx="$(next_record "$last_col"; printf x)"
if [ "$colx" = x ]
then
exit
fi
col="${colx%$'\nx'}" # The extra \n character is from psql
# Do your thing here
col_escaped="${col//"'"/''}" # Double single quotes
col_escaped="${col_escaped//\\/\\\\}" # Double backslashes
last_col="$col_escaped"
done
This is not supported. psql uses C print functions to print out the result tables, and printing a zero byte just doesn't work there.
Update: This is now supported in PostgreSQL 9.2-to-be (git).
Try this:
psql --field-separator '\000' --no-align -c '<your query>'
Edit: Maybe not. However, it appear to work in psql using these commands:
\f '\000'
\a
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With