[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 907
  • Last Modified:

running external program inside plsql

Hi all,

Is there a way to run an external executable, a CGI, a bash script  for exanple inside plpgsql function/trigger ?

What I need is:
- When a function is executed,
inside function I want to execute an outside script with parameter[s]
i.e. :  /usr/bin/test  alo hello

is it possible ? ( no return required in fact)

thanks.
0
Ege
Asked:
Ege
1 Solution
 
rjkimbleCommented:
It appears that the answer to your question is no. From the PostgreSQL 7.4.1 documentation, chapter 36. Procedural Languages:

3. The PL must be declared with the command

CREATE [TRUSTED] [PROCEDURAL] LANGUAGE language-name
    HANDLER handler_function_name;

The optional key word TRUSTED specifies that ordinary database users that have no superuser privileges should be allowed to use this language to create functions and trigger procedures. Since PL functions are executed inside the database server, the TRUSTED flag should only be given for languages that do not allow access to database server internals or the file system. The languages PL/pgSQL, PL/Tcl, and PL/Perl are considered trusted; the languages PL/TclU, PL/PerlU, and PL/PythonU are designed to provide unlimited functionality and should not be marked trusted.

The key point is that PL/pgSQL is TRUSTED, and that implies that you won't be able to run an outside script. I suspect that you'll have to use an untrusted version of PL/Perl or PL/Python, although further reading indicates that Perl might not work, because it can't be used for trigger functions. From the manual, it appears to me that PL/Python should work. I have no experience with PL/Python, however, so I can't offer any expertise in that area.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now