Automating Wiki Page Creation from a PostgreSQL Table

This is a rather specific requirement. In my case, I found myself having a Postgresql table from which I needed to create a page on an internal mediawiki for each row. For each row, I also had to insert a call to a template in the body of the page.

Initial Data

• In my case, the table is called: public.cities
• The only field of interest to me is friendly_name
• The mediawiki template in my case is called city_t and has the city name as a parameter. The parameter is called city_name.

Requirements

• The name of the page should not just be the city name, but should be composed similarly to: cityname_(city).
• The parameter passed to the template should instead be just the city name.

Execution

Here is the query executed on PostgreSQL:

SELECT xmlelement(name mediawiki,
  xmlagg(
    xmlelement(name page,
      xmlelement(name title, friendly_name || '_(city)',
      xmlelement(name revision,
        xmlelement(name text, '{{city_t|city_name=' || friendly_name || '}}')
      )
    )
  )
)::text
FROM public.cities
where active;

Note: the XML is converted to text because it was more convenient for me with the client I was using (DBeaver) to then copy and paste into a text file.

Having saved the text file with an .xml extension, it was enough to go to the wiki at: "SpecialPages -> Page tools -> Import pages" and upload the XML from there.

Done, hundreds of pages created quickly!