Excellent question. It boils down to the nature of F-number, which is focalLength/physicalAperture, and the fact that longer focal lengths magnify more. Keep in mind that light projected through an aperture still has to travel from the aperture to the sensor. The greater the distance from aperture to sensor, the greater the magnification...including magnification of the airy disc. The difference between a 180mm lens and a 24mm lens is about 7.5x. To get the same amount of diffraction from a 180mm lens as you would from 24mm lens at f/18, the 180mm lens would need a physical aperture of about 11.25mm in diameter. Given that 180/18 = 10mm, the amount of diffraction present at the sensor is actually a little bit more than with the 24mm lens.

Regarding the Sigma 105/2.8 lens you mention. I believe that is a macro lens. When it comes to macro photography, things change a little bit. You tend to focus extremely close to your subjects with macro photography, so close that depth of field is incredibly small...sometimes millimeters thick. In such situations, it is often more desirable to deal with some diffraction softening as a trade-off for increasing depth of field. In other words, you trade perfect sharpness at the focal plane for additional sharpness beyond the focal plane.

Apertures of f/32 or even f/64 are sometimes necessary to even get a shot at all when involving extension tubes. Additionally, at macro scale, particularly with extension, the effective aperture is usually greater than the actual aperture, thereby requiring exposure compensation to get a proper exposure. A general rule of thumb is that you will need 2x the exposure to compensate at macro scale. This is true for 1:1 magnification, however if you add any extension, you will likely need more. The formula for computing effective aperture at macro scale is as follows:

Ne = N * (M + 1)

Where N is the selected f/#, M is the current magnification (i.e. 2x, 5x) and Ne is the effective aperture number. For the 105mm macro lens with enough extension tubes to produce 2x magnification, at an actual aperture of f/4, the effective aperture from an exposure and diffraction standpoint would be f/12. Most modern cameras will compensate for this automatically, given that they have built in metering. It is still useful to understand exactly how macro photography affects aperture, though...and the possible implications from a diffraction standpoint.

Generally, you will want to set an aperture that gives you the effective aperture (not the actual, or physical, aperture) you need to get the exposure and DOF you want, at an acceptable level of diffraction. For a 1:1 macro lens, you have to double the actual aperture to get the effective aperture. On the Nikon D300s, which has a 12.3mp APS-C sensor, the diffraction limit kicks in at around f/11, and becomes a visible problem by around f/22 or so. At f/32, diffraction will likely be a real problem. If you want to take a macro shot at f/22, you would need to set the actual aperture to f/16.