The key to this is to recognize that ST_AsPNG returns a bytea, ie a byte array, representing a png. So, you need to somehow convert those bytes to a file on the file system. There are a couple of ways of doing this as shown in the docs.
The oid approach, as outlined by @Stefan, works, but requires psql and is a bit obscure, in my opinion.
The other approach, is to use Pythonu (ie, a Python stored procecure) to convert the bytea to an actual png file.
CREATE OR REPLACE FUNCTION write_file (param_bytes bytea, param_filepath text)
RETURNS text AS $$
f = open(param_filepath, 'wb+')
$$ LANGUAGE plpythonu;
After which you can do:
WITH png (bytes) AS
WHERE town = 'Macclesfield')
SELECT write_file(bytes, path_to_png) FROM png;
which is very clean.
As a note of warning, the Python stored procedure langauge is called plpythonu -- the final u stands for untrusted. As it states in the docs:
PL/Python is only available as an "untrusted" language, meaning it does not offer any way of restricting what users can do in it and is therefore named plpythonu. A trusted variant plpython might become available in the future if a secure execution mechanism is developed in Python. The writer of a function in untrusted PL/Python must take care that the function cannot be used to do anything unwanted, since it will be able to do anything that could be done by a user logged in as the database administrator. Only superusers can create functions in untrusted languages such as plpythonu
so you will want to make sure you use it in an isolated environment, if you go this route.
EDIT: As has been pointed out by @jpmc26, the plpythonu approach will only work server-side, and you will need to install plpythonu (which is not in the standard package), both of which means that you will need to have admin/super user privileges on the server, both of which are reasonable observations. I would argue, however, that if you don't have access to the server, once you have understood that ST_AsPNG returns a byte array, it is would be reasonably straight forward to write something client-side to consume the return value -- which could even be plain vanilla python, via psycopg2 (or anything other programming language that can call a Postgres function and write the resulting byte stream to disk locally).
For anyone wanting to go this route, in Ubuntu/Debian, you need to install the plpythonu extension, and then create the extension, which can be done with (change for your version).
sudo apt install postgresql-plpython-9.6
CREATE LANGUAGE plpythonu;
either within psql, pgadmin, or from a shell prompt of some description.