FAQ
How do I run an operating system command from a stored procedure? In
INFORMIX I would use the SYSTEM command, can't see to find the equivalent
for POSTGRES.

I'm running AIX6.1.

Thanks

Atif

Search Discussions

  • Andreas Kretschmer at Jun 17, 2010 at 2:51 pm

    Atif Jung wrote:

    How do I run an operating system command from a stored procedure? In INFORMIX I
    would use the SYSTEM command, can't see to find the equivalent for POSTGRES.
    You can use a un-trusted programming language, for instance pl/perlu or
    plsh.


    Andreas
    --
    Really, I'm not out to destroy Microsoft. That will just be a completely
    unintentional side effect. (Linus Torvalds)
    "If I was god, I would recompile penguin with --enable-fly." (unknown)
    Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
  • Tom Lane at Jun 17, 2010 at 3:15 pm

    Andreas Kretschmer writes:
    Atif Jung wrote:
    How do I run an operating system command from a stored procedure? In INFORMIX I
    would use the SYSTEM command, can't see to find the equivalent for POSTGRES.
    You can use a un-trusted programming language, for instance pl/perlu or
    plsh.
    Keep in mind that more often than not the answer is "you shouldn't do
    that". Commands run in that way will execute as the postgres user,
    not as your client user.

    regards, tom lane
  • Atif Jung at Jun 17, 2010 at 3:19 pm
    Sorry I don't understand. I am porting over some stored procedures from
    INFORMIX to POSTGRES. Some seem to port over OK, but the first problem I am
    encountering is the lack of SYSTEM command in POSTGRES.

    I don't understand what you mean by using an untrusted programming language.
    The stored procedure is written in PL/pgSQL, are you suggesting to rewrite
    it in PERL, excuse me for my ignorance.

    Thanks

    Atif



    On 17 June 2010 15:51, Andreas Kretschmer wrote:

    Atif Jung wrote:
    How do I run an operating system command from a stored procedure? In
    INFORMIX I
    would use the SYSTEM command, can't see to find the equivalent for
    POSTGRES.

    You can use a un-trusted programming language, for instance pl/perlu or
    plsh.


    Andreas
    --
    Really, I'm not out to destroy Microsoft. That will just be a completely
    unintentional side effect. (Linus Torvalds)
    "If I was god, I would recompile penguin with --enable-fly." (unknown)
    Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

    --
    Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-novice
  • Andreas Kretschmer at Jun 17, 2010 at 3:37 pm

    Atif Jung wrote:

    Sorry I don't understand. I am porting over some stored procedures from
    INFORMIX to POSTGRES. Some seem to port over OK, but the first problem I am
    encountering is the lack of SYSTEM command in POSTGRES.
    That's right.
    I don't understand what you mean by using an untrusted programming language.
    We have several internal languages, for instance plpgsql, plperl and
    plperlu. Some of them are so called 'trusted languages', for instance
    plpgsql. With a trusted language you can't operate outside the pg-system
    (no access to the underlaying operating system), with untrusted
    languages like plperlu you have access.

    The stored procedure is written in PL/pgSQL, are you suggesting to rewrite it
    in PERL, excuse me for my ignorance.
    Right, if you really needs that. But you should reconsider your idea,
    see Tom's answer.



    Andreas
    --
    Really, I'm not out to destroy Microsoft. That will just be a completely
    unintentional side effect. (Linus Torvalds)
    "If I was god, I would recompile penguin with --enable-fly." (unknown)
    Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
  • Atif Jung at Jun 18, 2010 at 10:52 am
    Thanks Adreas, I understand now. Like I mentioned I have several pgSQL
    stored procedures which access the OS and need to be rewritten in perlu.

    The problem is I have no knowledge of perl at all. Can you direct me to a
    quick and easy to get to grips with resource online?
    Thanks again

    Atif

    --
    Words of Wisdom
    =============

    George Bernard Shaw, the British playwright said:

    “The world is in dire need of a man with the mind of Muhammad; religious
    people in the Middle Ages, due to their ignorance and prejudice, had
    pictured him in a very dark way as they used to consider him the enemy of
    Christianity. But after looking into the story of this man I found it to be
    an amazing and a miraculous one and I came to the conclusion that he was
    never an enemy of Christianity, and must be called instead the saviour of
    humanity. In my opinion, if he was to be given control over the world today,
    he would solve our problems and secure the peace and happiness for which the
    world is longing.”


    On 17 June 2010 16:37, Andreas Kretschmer wrote:

    Atif Jung wrote:
    Sorry I don't understand. I am porting over some stored procedures from
    INFORMIX to POSTGRES. Some seem to port over OK, but the first problem I am
    encountering is the lack of SYSTEM command in POSTGRES.
    That's right.
    I don't understand what you mean by using an untrusted programming
    language.

    We have several internal languages, for instance plpgsql, plperl and
    plperlu. Some of them are so called 'trusted languages', for instance
    plpgsql. With a trusted language you can't operate outside the pg-system
    (no access to the underlaying operating system), with untrusted
    languages like plperlu you have access.

    The stored procedure is written in PL/pgSQL, are you suggesting to
    rewrite it
    in PERL, excuse me for my ignorance.
    Right, if you really needs that. But you should reconsider your idea,
    see Tom's answer.



    Andreas
    --
    Really, I'm not out to destroy Microsoft. That will just be a completely
    unintentional side effect. (Linus Torvalds)
    "If I was god, I would recompile penguin with --enable-fly." (unknown)
    Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

    --
    Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-novice
  • John Gage at Jun 18, 2010 at 11:41 am
    http://docstore.mik.ua/orelly/perl/index.htm

    They may be pirate, but they're good.

    John

    On Jun 18, 2010, at 12:52 PM, Atif Jung wrote:

    The problem is I have no knowledge of perl at all. Can you direct me
    to a quick and easy to get to grips with resource online?
  • John Gage at Jun 18, 2010 at 11:45 am
    Oh, and also, moderately obviously, there is:

    http://perldoc.perl.org/

    The Perl Monks will answer questions:

    http://www.perlmonks.org/

    Perl, very, very unfortunately, has an initially extremely steep
    learning curve. But it manipulates text wonderfully.

    Friedl's Regular Expressions is mostly a Perl text.

    • ISBN-10: 0596528124
    • ISBN-13: 978-0596528126

    John

    On Jun 18, 2010, at 12:52 PM, Atif Jung wrote:

    The problem is I have no knowledge of perl at all. Can you direct me
    to a quick and easy to get to grips with resource online?
  • Mladen Gogala at Jun 18, 2010 at 8:12 pm

    Atif Jung wrote:
    Sorry I don't understand. I am porting over some stored procedures
    from INFORMIX to POSTGRES. Some seem to port over OK, but the first
    problem I am encountering is the lack of SYSTEM command in POSTGRES.

    I don't understand what you mean by using an untrusted programming
    language. The stored procedure is written in PL/pgSQL, are you
    suggesting to rewrite it in PERL, excuse me for my ignorance.

    Thanks

    Atif
    Atif, PostgreSQL has several possibilities for writing stored
    procedures. Among other things, you can use languages like Perl and
    Python from within the database. Here is a little test function that I
    wrote just to see how can I write stored procedures for PgSQL in The
    Only True Programming Language (TM):

    create or replace function external_table(varchar(255))
    returns setof text
    as $$
    my $file=shift;
    open(FILE,"<$file")||die("Cannot open file $file for reading:$!\n");
    while(<FILE>) {
    chomp;
    return_next($_);
    }
    return(undef);
    $$ language plperlu;

    If you are interfacing with the OS, you have to use the "untrusted
    version". Only a superuser can define such functions but he can also
    grant the execute privilege to other people. It's called "untrusted
    Perl" because it was developed at BP.
    Having said that, if you are porting from one database to another, from
    one OS to another, you should probably not port the routine that
    executes a OS command. Make sure that nobody will invoke something like
    "rm -rf" or the situation can get rather dramatic, and quickly. I would
    advise creating a "black hole" function which does nothing (see the
    "NULL" statement here:
    http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html),
    just to be on the safe side:

    create or replace function system(varchar(255))
    returns integer
    as $$
    begin
    return(0);
    end;
    $$ language plpgsql;

    mgogala=# select system('rm -rf /');
    system
    --------

    (1 row)

    Time: 11.932 ms

    This function should get you through the hoops. That is much safer than
    allowing users to actually execute OS commands on the DB server. I know
    that I would get extremely excited if someone attempted that on one of
    my servers.

    --

    Mladen Gogala
    Sr. Oracle DBA
    1500 Broadway
    New York, NY 10036
    (212) 329-5251
    http://www.vmsinfo.com
    The Leader in Integrated Media Intelligence Solutions
  • Jasen Betts at Jun 18, 2010 at 9:17 am

    On 2010-06-17, Atif Jung wrote:
    --0016e6db2979ca8a9d04893aae3a
    Content-Type: text/plain; charset=ISO-8859-1

    How do I run an operating system command from a stored procedure? In
    INFORMIX I would use the SYSTEM command, can't see to find the equivalent
    for POSTGRES.

    I'm running AIX6.1.
    PLPGSQL doesn't allow that. use one of the untrusted languages:
    C, PLPERLU, PLTCLU, etc...

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJun 17, '10 at 2:30p
activeJun 18, '10 at 8:12p
posts10
users6
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase