CODEWITHSUNDEEP
bashpostgresqlnul
l0b0
l0b0

Reputation: 58908

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

Upvotes: 5

Views: 2439

Answers (3)

Yuri
Yuri

Reputation: 4478

Newer versions of psql support the --field-separator-zero flag.

Upvotes: 0

Peter Eisentraut
Peter Eisentraut

Reputation: 36739

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

Upvotes: 4

Matthew Wood
Matthew Wood

Reputation: 16417

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

Upvotes: 1

Related Questions