postgres stored procerure - ghdrako/doc_snipets GitHub Wiki

• https://www.postgresql.org/docs/15/sql-createlanguage.html
• https://postgrespro.ru/docs/postgrespro/16/plhandler?lang=en - Writing a Procedural Language Handler

Description: define a new procedural language
Syntax:
CREATE [ OR REPLACE ] [ TRUSTED ] [ PROCEDURAL ]
  LANGUAGE name
    HANDLER call_handler [ INLINE inline_handler ]
  [ VALIDATOR valfunction ]
CREATE [ OR REPLACE ] [ TRUSTED ] [ PROCEDURAL ]
  LANGUAGE name

• HANDLER: This function is actually the glue between PostgreSQL and any external language that you want to use. It is in charge of mapping PostgreSQL data structures to whatever is needed by thelanguage and helps pass the code around.
• VALIDATOR: This is the police officer of the infrastructure. If it is available, it will be in charge of delivering tasty syntax errors to the end user. Many languages are able to parse code before actually executing it. PostgreSQL can use that and tell you whether a function is correct or not when you create it. Unfortunately, not all languages can do this, so in some cases, you will still be left with problems showing up at runtime.
• INLINE: If this is present, PostgreSQL will be able to run anonymous code blocks utilizing this handler function.

The following options are available and are shipped along with the PostgreSQL core:

• SQL
• PL/pgSQL
• PL/Perl and PL/PerlU
• PL/Python
• PL/Tcl and PL/TclU

trusted/untrasted language

The idea is that a trusted language is restricted to the very core of the language, therefore, it is not possible to do the following:

• Include libraries
• Open network sockets
• Perform system calls of any kind, which would include opening files Perl offers something called taint mode, which is used to implementacja this feature in PostgreSQL. Perl will automatically restrict itself to trusted mode and error out if a security violation is about to happen. In untrusted mode, everything is possible; therefore, only the superuser is allowed to run untrusted code.If you want to run trusted as well as untrusted code, you have to activate both languages, that is, plperl and plperlu (pltcl and pltclu, respectively).