let tar auto detect compression type when extracting from stdin

This is not the answer you want to hear, but this is not supported by GNU tar according to its manual:

The only case when you have to specify a decompression option while reading the archive is when reading from a pipe or from a tape drive that does not support random access. However, in this case GNU tar will indicate which option you should use. For example:

$ cat archive.tar.gz | tar tf -
tar: Archive is compressed.  Use -z option
tar: Error is not recoverable: exiting now

If you see such diagnostics, just add the suggested option to the invocation of GNU tar:

$ cat archive.tar.gz | tar tzf -

-- 8.1.1 Creating and Reading Compressed Archives

Finally, I realized the reason I cannot let tar detect and decompress the archive from stdin is that I use GNU tar. BSD tar can do it automatically without problem. So I decide to use bsdtar instead of tar in my script now.