From PostgreSQL wiki
I started work on implementation PSM language for PostgreSQL. This is a second attack on this subject. I wrote a PLpgPSM interpret based on PLpgSQL interpret. After a few years I decided so this isn't a well way, and started from scratch.
- PLpgSQL is very mature language (interpret)
- Code is relative complex but not stable,
- It is a interpret with very lazy checking of SQL code,
- Code is hardly optimized on speed,
- Code is old - a few known trick are not used in all from backward compatibility reasons.
- PLpgSQL type system is relative complex - it uses a scalars, rows and records.
There is a minimal chance to move a new language with similar code complexity like PLpgSQL to core. But outside the core is relative hard work to maintain a complex code, then isn't PLpgSQL good base for new language. This is a reason why I started from zero. For some near years there isn't goal a same performance like PLpgSQL but good maintainability, readability and extensibility. Unlike PL/pgSQL PSM language requires a query checking in compile time - a automatic variables related to statement FOR are deduced from query's result. So these similar languages demands little bit different design. Next issue of PL/pgSQL so this is more dynamic, than is necessary. Dynamic SQL was a evil for years. So there was a goal to eliminate a usage of dynamic sql. You can use a query in functions that reads from non existing tables (in startup execution time), you can call a non existing functions. These features are not available in PSM, because a integrated queries are checked early. Now, dynamic sql cannot be a risk (with USAGE clause), so there are no reason why do compiler more complex. More, a errors in SQL are found early.
Points that should be changed about SPI
- some expression parser and executor support,
- possibility to ensure a result types and typmods,
- possibility to activate a implicit casting,
- possibility to fetch from cursor to type specified target,
- some better work with composite types.
- psm0 .. simple but with full features compiler
- psm1 .. user friendly error messages and diagnostics + regress tests
- psm2 .. maximum possibility speed
- plpsm .. goal implementation
a implemented features will not be changed between psm0 and plpsm - only bugs will be fixed. A complete list of implemented features is specified by samples in test.sql file.