I haven't found a stack outside a city to attack with my catapults yet (cannon are coming soon!), but my understanding is that some percentage (10%? 15%?.. probably varies by unit) of the damage you do to whichever unit stepped forward to defend is also applied to the other units in the stack. Some of the promotions will increase this damage. A single catapult by itself isn't going to obliterate and entire stack, but making all the units in it take 10% damage is nothing to sneeze at. And if if do it often enough (or have barracks, theology and hereditary rule), you can stack up the promotions and make the number much bigger (+30%?).
edit: 2kgames has posted the tech poster which includes the promotions. Three promotions to a siege unit gives +50% bombardment damage (or is it 100%? not sure if the promotion percentages stack), and also +10% combat strength vs melee and gunpowder units.