From PostgreSQL wiki
Jump to navigationJump to search

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.