plperl (postgreSQL perl) get url function

marchello has asked for the wisdom of the Perl Monks concerning the following question:

Hi all, I'm new to perl. The reason I need it is that PostgreSQL RDS (Amazon) does not support python extension, but it supports perl instead. This is how it looks like in python:

CREATE OR REPLACE FUNCTION get(uri character varying)
  RETURNS json AS
$BODY$
import urllib2
 
data = urllib2.urlopen(uri)
 
return data.read()
 
$BODY$
  LANGUAGE plpython2u VOLATILE
  COST 100;
[download]

This is how I think it should look in perl:

CREATE OR REPLACE FUNCTION get(input_url character varying)
  RETURNS json AS
$BODY$
use warnings;

import LWP::Simple;
my $url = input_url;
my $content = get($url);
$content =~ s/ /%20/g;
return $content;
 
$BODY$
  LANGUAGE plperl VOLATILE
  COST 100;
[download]

But when I try to run my perl function it returns error:

select get('ipinfo.io/ip');

ERROR:  Undefined subroutine &main::get called at line 6.
CONTEXT:  PL/Perl function "get"
********** Error **********

ERROR: Undefined subroutine &main::get called at line 6.
SQL state: 38000
Context: PL/Perl function "get"
[download]

Please advise.

Comment on plperl (postgreSQL perl) get url function Select or Download Code

Replies are listed 'Best First'.
Re: plperl (postgreSQL perl) get url function by LanX (Saint) on May 27, 2019 at 21:58 UTC
> `import LWP::Simple;` I'd say substitute import with `use` Make sure that `get` is exported by default by LWP::Simple , otherwise add it to the imported list. ( Update it is exported by default) Cheers Rolf _{(addicted to the Perl Programming Language :) Wikisyntax for the Monastery FootballPerl is like chess, only without the dice}	[reply] [d/l] [select]
Re^2: plperl (postgreSQL perl) get url function by marchello (Novice) on May 27, 2019 at 22:24 UTC
Thanks Rolf, though it does not work for me. I tried `use LWP::Simple;` [download] but then I can't even compile my function, it returns error: `ERROR: Unable to load LWP/Simple.pm into plperl at line 4. BEGIN failed--compilation aborted at line 4. CONTEXT: compilation of PL/Perl function "get"` [download] Please advise.	[reply] [d/l] [select]
Re^3: plperl (postgreSQL perl) get url function by poj (Abbot) on May 28, 2019 at 12:45 UTC
see Trusted and Untrusted PL/Perl I was able to get this to work using `plperlu` after running `CREATE EXTENSION plperlu;` `CREATE OR REPLACE FUNCTION get(character varying) RETURNS json AS $BODY$ use warnings; use strict; use LWP::Simple; my $content = get($_[0]); $content =~ s/ /%20/g; return $content; $BODY$ LANGUAGE plperlu VOLATILE COST 100;` [download] poj	[reply] [d/l] [select]
Re^3: plperl (postgreSQL perl) get url function by LanX (Saint) on May 27, 2019 at 22:37 UTC
> ERROR: Unable to load LWP/Simple.pm into plperl at line 4. make sure that LWP::Simple is available on that server elaborate this libs path try `warn "@INC";` to see if it's already included otherwise add `use lib "PATH";` before trying to load LWP::Simple see `lib` for details I've never worked with such postgres exetensions, probably they have been deliberately disabled by your server's admin. Cheers Rolf _{(addicted to the Perl Programming Language :) Wikisyntax for the Monastery FootballPerl is like chess, only without the dice}	[reply] [d/l] [select]
Re^4: plperl (postgreSQL perl) get url function by marchello (Novice) on May 28, 2019 at 16:25 UTC
Re: plperl (postgreSQL perl) get url function by karlgoethebier (Abbot) on May 28, 2019 at 13:01 UTC
Mmh, perhaps you might take a look at Trusted and Untrusted PL/Perl? From there: „...In general, the operations that are restricted are those that interact with the environment. This includes file handle operations, require, and use (for external modules)...“ Update: Oops, i was too late, sorry 😐 Best regards, Karl «The Crux of the Biscuit is the Apostrophe» `perl -MCrypt::CBC -E 'say Crypt::CBC->new(-key=>'kgb',-cipher=>"Blowfish")->decrypt_hex($ENV{KARL});'`Help	[reply] [d/l]