Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

psql --(record|field)-separator NUL

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
like image 805
l0b0 Avatar asked Jul 28 '11 10:07

l0b0


2 Answers

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

like image 119
Peter Eisentraut Avatar answered Sep 26 '22 00:09

Peter Eisentraut


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
like image 31
Matthew Wood Avatar answered Sep 23 '22 00:09

Matthew Wood