• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 929
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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