MIME-Version: 1.0
References: <CANzqJaAJVQVa0Wbx1fHv5n7zfp9ZyCXZMPqJF_JLTBo68ToNeA@mail.gmail.com>
 <CAKAnmmKv=A6d=q_CqYPP6a7qS2+tacYV4WOzXziBuXq3MPTwjw@mail.gmail.com>
In-Reply-To: <CAKAnmmKv=A6d=q_CqYPP6a7qS2+tacYV4WOzXziBuXq3MPTwjw@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Thu, 28 Aug 2025 01:31:41 -0400
Message-ID: <CANzqJaAMDU6eRWOjetDQ_BK9uvGau=2q20mbv_jQqZR4Gd2Ccg@mail.gmail.com>
Subject: Re: psql --html and to_char()
Cc: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000fa1ee5063d663801"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaAMDU6eRWOjetDQ_BK9uvGau%3D2q20mbv_jQqZR4Gd2Ccg%40mail.gmail.com>
Precedence: bulk

--000000000000fa1ee5063d663801
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wed, Aug 27, 2025 at 6:13=E2=80=AFPM Greg Sabino Mullane <htamfids@gmail=
.com>
wrote:

> On Wed, Aug 27, 2025 at 11:28=E2=80=AFAM Ron Johnson <ronljohnsonjr@gmail=
.com>
> wrote:
>
>> Is there a way around this, other than writing my own HTMLifier?
>>
>
> Not unless your to_char() output can be coerced back into a numeric. (for
> the archives sake, see fe_utils/print.c for the complete list of
> right-aligned columns: int family, float, numeric, oid, xid, cid, and the
> when-will-it-finally-die money type)
>

Easier to write a generic shell function.

HTMLify_csv()
{
    local -rn pFormats=3D"$1"
    local line
    local -a lFields
    printf '<table border=3D1><font size=3D"2">\n'
    read -r line
    IFS=3D'|' read -r -a lFields <<< "${line}"
    local -i lFldCnt=3D${#lFields[@]}
    printf '<tr>'
    for ((i=3D0; i<$lFldCnt; i++)); do printf '<th align=3D"%s">%s</th>' ce=
nter
"${lFields[i]}"; done
    printf "</tr>\n"
    while IFS=3D read -r line
    do
        [[ $line =3D~ rows?\)$ ]] && { printf '<tr><td>%s</td></tr>\n'
"$line"; continue; }
        IFS=3D'|' read -r -a lFields <<< "${line}"
        printf '<tr>'
        for ((i=3D0; i<$lFldCnt; i++))
        do
            printf '<td align=3D"%s">%s</td>' "${pFormats[i]}" "${lFields[i=
]}"
        done
        printf "</tr>\n"
    done
    printf '</table>\n'
}

declare -a Formats=3D(right left)
psql -XAc "select to_char(oid::integer, '99,999') as oid, datname from
pg_database;" | HTMLify_csv Formats

It requires an array that describes how you want each column aligned.

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

--000000000000fa1ee5063d663801
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote g=
mail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Wed, Aug 27,=
 2025 at 6:13=E2=80=AFPM Greg Sabino Mullane &lt;<a href=3D"mailto:htamfids=
@gmail.com">htamfids@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D=
"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(2=
04,204,204);padding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr">On Wed, Aug=
 27, 2025 at 11:28=E2=80=AFAM Ron Johnson &lt;<a href=3D"mailto:ronljohnson=
jr@gmail.com" target=3D"_blank">ronljohnsonjr@gmail.com</a>&gt; wrote:</div=
><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" style=3D"marg=
in:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1e=
x"><div dir=3D"ltr"><div>Is there a way around this, other than writing my =
own HTMLifier?</div></div></blockquote><div><br></div><div>Not unless your =
to_char() output can be coerced back into a numeric. (for the archives sake=
, see fe_utils/print.c for the complete list of right-aligned columns: int =
family, float, numeric, oid, xid, cid, and the when-will-it-finally-die mon=
ey type)</div></div></div></blockquote><div><br></div><div>Easier to write =
a generic shell function.</div><div><br></div><div><font face=3D"monospace"=
>HTMLify_csv()<br>{<br>=C2=A0 =C2=A0 local -rn pFormats=3D&quot;$1&quot;<br=
>=C2=A0 =C2=A0 local line<br>=C2=A0 =C2=A0 local -a lFields<br>=C2=A0 =C2=
=A0 printf &#39;&lt;table border=3D1&gt;&lt;font size=3D&quot;2&quot;&gt;\n=
&#39;<br>=C2=A0 =C2=A0 read -r line<br>=C2=A0 =C2=A0 IFS=3D&#39;|&#39; read=
 -r -a lFields &lt;&lt;&lt; &quot;${line}&quot;<br>=C2=A0 =C2=A0 local -i l=
FldCnt=3D${#lFields[@]}<br>=C2=A0 =C2=A0 printf &#39;&lt;tr&gt;&#39;<br>=C2=
=A0 =C2=A0 for ((i=3D0; i&lt;$lFldCnt; i++)); do printf &#39;&lt;th align=
=3D&quot;%s&quot;&gt;%s&lt;/th&gt;&#39; center &quot;${lFields[i]}&quot;; d=
one<br>=C2=A0 =C2=A0 printf &quot;&lt;/tr&gt;\n&quot;<br>=C2=A0 =C2=A0 whil=
e IFS=3D read -r line<br>=C2=A0 =C2=A0 do<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 [[=
 $line =3D~ rows?\)$ ]] &amp;&amp; { printf &#39;&lt;tr&gt;&lt;td&gt;%s&lt;=
/td&gt;&lt;/tr&gt;\n&#39; &quot;$line&quot;; continue; }<br>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 IFS=3D&#39;|&#39; read -r -a lFields &lt;&lt;&lt; &quot;${lin=
e}&quot;<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 printf &#39;&lt;tr&gt;&#39;<br>=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 for ((i=3D0; i&lt;$lFldCnt; i++))<br>=C2=A0 =C2=A0=
 =C2=A0 =C2=A0 do<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 printf &#39;=
&lt;td align=3D&quot;%s&quot;&gt;%s&lt;/td&gt;&#39; &quot;${pFormats[i]}&qu=
ot; &quot;${lFields[i]}&quot;<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 done<br>=C2=A0=
 =C2=A0 =C2=A0 =C2=A0 printf &quot;&lt;/tr&gt;\n&quot;<br>=C2=A0 =C2=A0 don=
e<br>=C2=A0 =C2=A0 printf &#39;&lt;/table&gt;\n&#39;<br>}</font></div><div>=
<font face=3D"monospace"><br></font></div><div><font face=3D"monospace">dec=
lare -a Formats=3D(right left)</font></div><div><font face=3D"monospace">ps=
ql -XAc &quot;select to_char(oid::integer, &#39;99,999&#39;) as oid, datnam=
e from pg_database;&quot; | HTMLify_csv Formats</font></div><div><br></div>=
<div>It requires an array that describes how you want each column aligned.<=
/div><div><br></div></div><span class=3D"gmail_signature_prefix">-- </span>=
<br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">Death to &l=
t;Redacted&gt;, and butter sauce.<div>Don&#39;t boil me, I&#39;m still aliv=
e.<br><div><div>&lt;Redacted&gt; lobster!</div></div></div></div></div></di=
v>

--000000000000fa1ee5063d663801--