0

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:

  1. Are PostgreSQL extensions and SQL Server extended/CLR stored procedure having similar purpose? If not, why?

  2. If answer to above is yes, then, which one is better - PostgreSQL extensions or SQL Server extended/CLR stored procedures? Why?

Thanks.

4
  • I expect the main difference will be that Pg's stored procedures don't have autonomous transactions. If you want transaction control you need to run your procs from outside Pg its self or use hacks like dblink. I don't really know SQL server so it's hard to say whether they have similar purposes and which is "better". Commented Apr 16, 2013 at 11:40
  • PostgreSQL extensions probably provide a bit more functionality because they also allow to define new procedural languages and index structures. But the question which one is better will depend on what you need it to do. Commented Apr 16, 2013 at 11:47
  • @JakubKania: I am trying to learn PostgreSQL. I know of SQL Server, therefore I am trying to understand PostgreSQL by comparing it with SQLServer. I do not have any specific use-case that is to be achieved.
    – Anand Patel
    Commented Apr 16, 2013 at 13:44
  • 1
    Flagging this to be moved to DBA, since it will allow more collaboration between SQL Server and Pg folks. Commented Apr 18, 2013 at 5:28

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.