How is PostgreSQL extension feature compared to SQL Server extended and CLR stored procedures? I am new to PostgreSQL. If I am not wrong, SQL Server has similar mechanism - extended stored procedures for adding new functionality to the base SQL Server functionality. CLR stored procedures could also be used for extending the SQL Server base functionality. I am interested in knowing the following:


Are PostgreSQL extensions and SQL Server extended/CLR stored procedure having similar purpose? If not, why?
If answer to above is yes, then, which one is better - PostgreSQL extensions or SQL Server extended/CLR stored procedures? Why?


Thanks.
I think you have to look at extensions entirely differently.  The CLR stored procedures are most closely related to the procedural language interface which is one application of the extension system.  The extension system has goals quite a bit broader than mere stored procedure languages.  Extensions can define primitives or functions as well.  

However if we limit the question to the stored procedure interface, the answer is, broadly speaking, that they have a similar purpose.  There are a significant number of major differences between the two and they come from different database viewpoints.

The two most obvious differences are that functions have a lot less transactional control in PostgreSQL than MS SQL, and that MS SQL can more easily return multiple result sets from functions than PostgreSQL can.  On the other hand, you can write stored procedures in PostgreSQL in a wide range of languages including TCL, Perl, Python, Java, and more.  I suspect that PL/Mono is relatively unmaintained, but it could be polished up without too much effort allowing you to run CLR code as well.  So PostgreSQL gives you a great variety of languages, while MS SQL gives a little more control in the stored procedures over things like transactions.

This being said, in general these just force different perspectives on database development.  Both are quite programmable databases, though I personally think PostgreSQL is slightly more programmable, this may say more about the fact that I know PostgreSQL better than MS SQL than it does about the two RDBMS's.

My reason for siding with PostgreSQL here has to do with the ability to hook a lot of these functions into indexes.  This is also useful when combined with filters on indexes (which both support).  In essence I am a little happier with the way functions fit into SQL with PostgreSQL.  However, this is a really minor detail.....

In the end I think it is probably possible to do pretty much anything you can do with one with the other.  I find the "object-relational" perspective of PostgreSQL to be very empowering.  I would, however, not recommend making a purchasing decision based on such an abstract criteria.  Nitty gritty matters.