Thread: Dynamic loading of Perl Code in Postgres functions

Hi,
I have some perl code that I need to load dynamically in my postgres function.
How can this be accomplished?
I can do it in C using shared objects but don't know how would the same work
with perl.
Is there anything like shared objects in Perl or something.
Thanks,
Jas

You treat it like any other perl code (you don't have to do
anything special just because it's in postgres):

If it's pure perl code, see "require"

http://perldoc.perl.org/functions/require.html

If it's a perl module, see "use"

http://perldoc.perl.org/functions/use.html

If you want to access a C library using perl,
see XS:

http://perldoc.perl.org/perlxs.html
http://perldoc.perl.org/perlxstut.html




-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Jasbinder Singh
Bali
Sent: Tuesday, January 16, 2007 8:07 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Dynamic loading of Perl Code in Postgres functions


Hi,
I have some perl code that I need to load dynamically in my postgres
function.
How can this be accomplished?
I can do it in C using shared objects but don't know how would the same
work
with perl.
Is there anything like shared objects in Perl or something.
Thanks,
Jas

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

Actually I want to load my perl code in postgres function.
How would i do that?
In C you have the notion of shared objects that you dynamically load
in postgres functions.
If one has to dynamically load the functionality of some perl code in
postgres function, how would that be done?
I think i was not clear enough in my last email
Thanks
Jas

On 1/16/07, Adam Rich <adam.r@sbcglobal.net> wrote:
>
> You treat it like any other perl code (you don't have to do
> anything special just because it's in postgres):
>
> If it's pure perl code, see "require"
>
> http://perldoc.perl.org/functions/require.html
>
> If it's a perl module, see "use"
>
> http://perldoc.perl.org/functions/use.html
>
> If you want to access a C library using perl,
> see XS:
>
> http://perldoc.perl.org/perlxs.html
> http://perldoc.perl.org/perlxstut.html
>
>
>
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Jasbinder Singh
> Bali
> Sent: Tuesday, January 16, 2007 8:07 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Dynamic loading of Perl Code in Postgres functions
>
>
> Hi,
> I have some perl code that I need to load dynamically in my postgres
> function.
> How can this be accomplished?
> I can do it in C using shared objects but don't know how would the same
> work
> with perl.
> Is there anything like shared objects in Perl or something.
> Thanks,
> Jas
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
>

Assuming you have pl/perl support compiled into PostgreSQL, just use
CREATE FUNCTION:

http://www.postgresql.org/docs/8.2/interactive/plperl-funcs.html




On Jan 16, 2007, at 9:24 AM, Jasbinder Singh Bali wrote:

> Actually I want to load my perl code in postgres function.
> How would i do that?
> In C you have the notion of shared objects that you dynamically load
> in postgres functions.
> If one has to dynamically load the functionality of some perl code in
> postgres function, how would that be done?
> I think i was not clear enough in my last email



John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

So there is nothing called dynamic loading of perl code in postgres.
I'll have to include the whole perl script in the postgres function you mean?
Thanks,
Jas

On 1/16/07, John DeSoi <desoi@pgedit.com> wrote:
> Assuming you have pl/perl support compiled into PostgreSQL, just use
> CREATE FUNCTION:
>
> http://www.postgresql.org/docs/8.2/interactive/plperl-funcs.html
>
>
>
>
> On Jan 16, 2007, at 9:24 AM, Jasbinder Singh Bali wrote:
>
> > Actually I want to load my perl code in postgres function.
> > How would i do that?
> > In C you have the notion of shared objects that you dynamically load
> > in postgres functions.
> > If one has to dynamically load the functionality of some perl code in
> > postgres function, how would that be done?
> > I think i was not clear enough in my last email
>
>
>
> John DeSoi, Ph.D.
> http://pgedit.com/
> Power Tools for PostgreSQL
>
>

I assume your choices are to include the entire script in a
PostgreSQL function or simply create a small stub function that calls
a function you have loaded with use or require (as mentioned by the
other poster). But either way, you have to create a function so you
can tell PostgreSQL what to call, what the parameters are, and what
values should be returned.

John



On Jan 16, 2007, at 10:25 AM, Jasbinder Singh Bali wrote:

> So there is nothing called dynamic loading of perl code in postgres.
> I'll have to include the whole perl script in the postgres function
> you mean?



John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

"Harpreet Dhaliwal" <harpreet.dhaliwal01@gmail.com> writes:
> I was just wondering if one could use something like this

> CREATE FUNCTION *funcname* (*argument-types*) RETURNS *return-type* AS $$
>     require "abc.pl"
> $$ LANGUAGE plperl;

You'd have to use plperlu, since "require" isn't considered a trusted
operation.

> To include abc.pl here, how is the path of abc.pl specified.

Same as you'd do it in plain Perl.

            regards, tom lane

so my syntax is correct? just wondering if there's some fundamental mistake in it
~Harpreet

I'm kind of confused how this require thing would actually work
because I tried testing it at my end at its really not working with
postgres.
I'm sure there is some fundamental mistake.

I have to do the following:

I have a perl file and need to call and use full functionaily  of
this perl file in my postgres function.
    For this, the create function script that I wrote is as follows:
CREATE FUNCTION test_perl (int) RETURNS int AS $$
     require 'email_parser1.pl'
 $$ LANGUAGE plperlu;

really don't know if this is correct because if i try to load a file
that doesn't exist atall, then also postgres doesn't throw any errors.

Also do i need to give the fully qualified path of the perl file in
the create function script?

A quick help would be highly appreciated as I'm badly stuck up here.

Thanks,
Jas



On 1/16/07, Harpreet Dhaliwal <harpreet.dhaliwal01@gmail.com> wrote:
> so my syntax is correct? just wondering if there's some fundamental mistake
> in it
> ~Harpreet
>
>
> On 1/16/07, Tom Lane < tgl@sss.pgh.pa.us> wrote:
> > "Harpreet Dhaliwal" < harpreet.dhaliwal01@gmail.com> writes:
> > > I was just wondering if one could use something like this
> >
> > > CREATE FUNCTION *funcname* (*argument-types*) RETURNS *return-type* AS
> $$
> > >     require " abc.pl"
> > > $$ LANGUAGE plperl;
> >
> > You'd have to use plperlu, since "require" isn't considered a trusted
> > operation.
> >
> > > To include abc.pl here, how is the path of abc.pl specified.
> >
> > Same as you'd do it in plain Perl.
> >
> >                         regards, tom lane
> >
>
>

Lately i've been able to user 'require' command successfully and the
script was pretty straight forward and simple. I had to play around
with @INC.

Moving forward, I have another question here,

CREATE FUNCTION *funcname* (*argument-types*) RETURNS *return-type* AS
    require " abc.pl"
 $$ LANGUAGE plperlu;

In the above script if I have to call a particular sub-routine in abc.pl.
How can that be done?
I have to pass values to the arguments of a sub routine in abc.pl from
the the function funcname (arguments of the funcname in particular).
How would this thing be done?

Thanks,
Jas

On 1/16/07, Harpreet Dhaliwal <harpreet.dhaliwal01@gmail.com> wrote:
> so my syntax is correct? just wondering if there's some fundamental mistake
> in it
> ~Harpreet
>
>
> On 1/16/07, Tom Lane < tgl@sss.pgh.pa.us> wrote:
> > "Harpreet Dhaliwal" < harpreet.dhaliwal01@gmail.com> writes:
> > > I was just wondering if one could use something like this
> >
> > > CREATE FUNCTION *funcname* (*argument-types*) RETURNS *return-type* AS
> $$
> > >     require " abc.pl"
> > > $$ LANGUAGE plperl;
> >
> > You'd have to use plperlu, since "require" isn't considered a trusted
> > operation.
> >
> > > To include abc.pl here, how is the path of abc.pl specified.
> >
> > Same as you'd do it in plain Perl.
> >
> >                         regards, tom lane
> >
>
>

Don't think it would work the way you are doing it.
This way it would only work if you dealing with shared objects in C where in you dynamically load the shared object and then call a specific function of that shared object.

Lately i tried the following for you but it doesn't execute the Insert script inside sql.pl

CREATE OR REPLACE FUNCTION test_perl_param(int4)
  RETURNS bool AS
$BODY$
     require "/usr/local/pgsql/jsbali/sql.pl"
 $BODY$
  LANGUAGE 'plperlu' VOLATILE;
ALTER FUNCTION test_perl_param(int4) OWNER TO postgres;

SELECT test_perl_param(23)

Here in sql.pl i have one insert statement but SELECT test_perl_param(23) doesn't run the INSERT statement inside the sql.pl

Can anyone throw light on how to make it work so that whatever script i have inside sql.pl run as soon as I run SELECT test_perl_param(23).

I think this wold help out jas alot.

Thanks,
Harpreet

Thats exactly what I'm facing here.

CREATE OR REPLACE FUNCTION test(int4)
   RETURNS int4 AS
 $BODY$
      require "abc.pl"
  $BODY$
   LANGUAGE 'plperlu' VOLATILE;

 SELECT test(23) doesn't run the script inside abc.pl that happens to
be a some insert statements.

Now, when i actually copy and paste the script of abc.pl inside the
postgres function body and then do SELECT test(23) it works fine by
executing those insert statements.

Don't know whats wrong here now.
Can anyone please throw some light on it.

Thanks,
Jas




On 1/18/07, Harpreet Dhaliwal <harpreet.dhaliwal01@gmail.com> wrote:
> Don't think it would work the way you are doing it.
> This way it would only work if you dealing with shared objects in C where in
> you dynamically load the shared object and then call a specific function of
> that shared object.
>
> Lately i tried the following for you but it doesn't execute the Insert
> script inside sql.pl
>
> CREATE OR REPLACE FUNCTION test_perl_param(int4)
>   RETURNS bool AS
> $BODY$
>      require "/usr/local/pgsql/jsbali/sql.pl"
>  $BODY$
>   LANGUAGE 'plperlu' VOLATILE;
> ALTER FUNCTION test_perl_param(int4) OWNER TO postgres;
>
> SELECT test_perl_param(23)
>
> Here in sql.pl i have one insert statement but SELECT test_perl_param(23)
> doesn't run the INSERT statement inside the sql.pl
>
> Can anyone throw light on how to make it work so that whatever script i have
> inside sql.pl run as soon as I run SELECT test_perl_param(23).
>
> I think this wold help out jas alot.
>
> Thanks,
> Harpreet
>
>
> On 1/18/07, Jasbinder Singh Bali <jsbali@gmail.com> wrote:
> >
> > Lately i've been able to user 'require' command successfully and the
> > script was pretty straight forward and simple. I had to play around
> > with @INC.
> >
> > Moving forward, I have another question here,
> >
> > CREATE FUNCTION *funcname* (*argument-types*) RETURNS *return-type* AS
> >     require " abc.pl"
> > $$ LANGUAGE plperlu;
> >
> > In the above script if I have to call a particular sub-routine in abc.pl.
> > How can that be done?
> > I have to pass values to the arguments of a sub routine in abc.pl from
> > the the function funcname (arguments of the funcname in particular).
> > How would this thing be done?
> >
> > Thanks,
> > Jas
> >
> > On 1/16/07, Harpreet Dhaliwal < harpreet.dhaliwal01@gmail.com> wrote:
> > > so my syntax is correct? just wondering if there's some fundamental
> mistake
> > > in it
> > > ~Harpreet
> > >
> > >
> > > On 1/16/07, Tom Lane < tgl@sss.pgh.pa.us> wrote:
> > > > "Harpreet Dhaliwal" < harpreet.dhaliwal01@gmail.com > writes:
> > > > > I was just wondering if one could use something like this
> > > >
> > > > > CREATE FUNCTION *funcname* (*argument-types*) RETURNS *return-type*
> AS
> > > $$
> > > > >     require " abc.pl"
> > > > > $$ LANGUAGE plperl;
> > > >
> > > > You'd have to use plperlu, since "require" isn't considered a trusted
> > > > operation.
> > > >
> > > > > To include abc.pl here, how is the path of abc.pl specified.
> > > >
> > > > Same as you'd do it in plain Perl.
> > > >
> > > >                         regards, tom lane
> > > >
> > >
> > >
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 6: explain analyze is your friend
> >
>
>