Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

what are the advantages of using plpgsql in postgresql

Besides the syntactic sugar and expressiveness power what are the differences in runtime efficiency. I mean, plpgsql can be faster than, lets say plpythonu or pljava? Or are they all approximately equals?

We are using stored procedures for the task of detecting nearly-duplicates records of people in a moderately sized database (around 10M of records)

like image 333
Alex. S. Avatar asked Oct 29 '08 15:10

Alex. S.


People also ask

What is Plpgsql used for?

With PL/pgSQL you can group a block of computation and a series of queries inside the database server, thus having the power of a procedural language and the ease of use of SQL, but with considerable savings because you don't have the whole client/server communication overhead.


1 Answers

plpgsql provides greater type safety I believe, you have to perform explicit casts if you want to perform operations using two different columns of similar type, like varchar and text or int4 and int8. This is important because if you need to have your stored proc use indexes, postgres requires that the types match exactly between join conditions (edit: for equality checks too I think).

There may be a facility for this in the other languages though, I haven't used them. In any case, I hope this gives you a better starting point for your investigation.

like image 63
Dana the Sane Avatar answered Oct 14 '22 05:10

Dana the Sane