Thu, Aug 24, 2006 at 01:03:43AM -0400, Jasbinder Bali wrote:
CREATE R REPLACE FUNCTIN sp_trigger_raw_email(int4, text)
RETURNS bool AS
'/usr/local/pgsql/jsbali/parser', 'parse_email'
LANGUAGE 'c' VLATILE STRICT;
ALTER FUNCTIN sp_trigger_raw_email(int4,text ) WNER T postgres;

function parse_email(int caseno, char *rawemail)
populates a few global variables first and then
call another function parse_header().
function parse_header() makes use of the global variables and then using
ECPG stores values in a table in the database.

Is there a reason this server-side code is using ECPG instead of SPI?

My question is, when we try to make use of a specific function of a shared
object dynamically loaded as show above, then
would that function be able to access all global variables populated
elsewhere in the program or all the global variables can't be accessed
inside that function of the shared object.

A function should be able to access any global symbol and any static
symbol in the same object file. Are you having trouble doing so?

Also, in the above function definition,
the signature of parse_email function is
parse_email(int, char*) and i am passing (int4 , text) to int as seen in the
function code pasted above.
Is text in pgsql going to match with char* or i should use some other
datatype?

See "C-Language Functions" in the documentation, in particular what
it says about version 1 calling conventions.

Is there a reason you're coding in C instead of a higher-level
language like PL/Perl? If you're parsing email messages then coding
in Perl, Python, Ruby, etc., would probably be easier than C.

Wed, Aug 23, 2006 at 11:41:50PM -0600, Michael Fuhr wrote:
Is there a reason this server-side code is using ECPG instead of SPI?
To make sure it doesn't work? There is N guarantee that ECPG will work
in this scenario.
Michael

Hi,
The way we use ECPG for the database related activites while working with
C, what do i need to look up if i'm using perl.
As alot of people have pointed out that perl is the best language to use
while dealing with email parsing.
We have the perl code ready to parse the email.
Just wondering what would be the best method to deal with database
(postgresql) calls from the perl code.

Thanks and regards,
Jas

8/24/06, Michael Fuhr <mike (AT) fuhr (DOT) orgwrote:

Thu, Aug 24, 2006 at 01:03:43AM -0400, Jasbinder Bali wrote:
CREATE R REPLACE FUNCTIN sp_trigger_raw_email(int4, text)
RETURNS bool AS
'/usr/local/pgsql/jsbali/parser', 'parse_email'
LANGUAGE 'c' VLATILE STRICT;
ALTER FUNCTIN sp_trigger_raw_email(int4,text ) WNER T postgres;

function parse_email(int caseno, char *rawemail)
populates a few global variables first and then
call another function parse_header().
function parse_header() makes use of the global variables and then
using
ECPG stores values in a table in the database.

Is there a reason this server-side code is using ECPG instead of SPI?

My question is, when we try to make use of a specific function of a
shared
object dynamically loaded as show above, then
would that function be able to access all global variables populated
elsewhere in the program or all the global variables can't be accessed
inside that function of the shared object.

A function should be able to access any global symbol and any static
symbol in the same object file. Are you having trouble doing so?

Also, in the above function definition,
the signature of parse_email function is
parse_email(int, char*) and i am passing (int4 , text) to int as seen in
the
function code pasted above.
Is text in pgsql going to match with char* or i should use some other
datatype?

See "C-Language Functions" in the documentation, in particular what
it says about version 1 calling conventions.

Is there a reason you're coding in C instead of a higher-level
language like PL/Perl? If you're parsing email messages then coding
in Perl, Python, Ruby, etc., would probably be easier than C.

Thu, 24 Aug 2006 15:46:00 -0400 "Jasbinder Bali" <jsbali (AT) gmail (DOT) comthought long, then sat down and wrote:

Hi,
The way we use ECPG for the database related activites while working with
C, what do i need to look up if i'm using perl.
As alot of people have pointed out that perl is the best language to use
while dealing with email parsing.
We have the perl code ready to parse the email.
Just wondering what would be the best method to deal with database
(postgresql) calls from the perl code.

Use DBD::Pg, available at CPAN.

Thu, Aug 24, 2006 at 03:46:00PM -0400, Jasbinder Bali wrote:
The way we use ECPG for the database related activites while working with
C, what do i need to look up if i'm using perl.

For information about writing server-side Perl functions see the
PL/Perl documentation (adjust the link if you're using a version
other than 8.1):

If the function needs to execute SQL statements in the same backend
in which it's running then see the "Database Access from PL/Perl"
section. If it needs to connect to a different database then you
could use the DBI module.

As alot of people have pointed out that perl is the best language to use
while dealing with email parsing.

"Best" is a matter of opinion; others might prefer Ruby, Python,
or something else. Perl is good at text handling, it has a large
number of third-party modules (CPAN), and there's a lot of material
written about it so it's a popular choice.

We have the perl code ready to parse the email.
Just wondering what would be the best method to deal with database
(postgresql) calls from the perl code.

See the aforementioned PL/Perl documentation. You could also use
PL/Perl just for parsing and use PL/pgSQL for working with the
database.

Hi,
Do we have any concept of shared objects in perl.
Just wondering, how do we dynamically load something written in perl in
postgresql.

Thanks,
Jas

8/24/06, Michael Fuhr <mike (AT) fuhr (DOT) orgwrote:

Thu, Aug 24, 2006 at 03:46:00PM -0400, Jasbinder Bali wrote:
The way we use ECPG for the database related activites while working
with
C, what do i need to look up if i'm using perl.

For information about writing server-side Perl functions see the
PL/Perl documentation (adjust the link if you're using a version
other than 8.1):

If the function needs to execute SQL statements in the same backend
in which it's running then see the "Database Access from PL/Perl"
section. If it needs to connect to a different database then you
could use the DBI module.

As alot of people have pointed out that perl is the best language to use
while dealing with email parsing.

"Best" is a matter of opinion; others might prefer Ruby, Python,
or something else. Perl is good at text handling, it has a large
number of third-party modules (CPAN), and there's a lot of material
written about it so it's a popular choice.

We have the perl code ready to parse the email.
Just wondering what would be the best method to deal with database
(postgresql) calls from the perl code.

See the aforementioned PL/Perl documentation. You could also use
PL/Perl just for parsing and use PL/pgSQL for working with the
database.

Sat, Aug 26, 2006 at 03:32:37PM -0400, Jasbinder Bali wrote:
Do we have any concept of shared objects in perl.
Just wondering, how do we dynamically load something written in perl in
postgresql.

A PL/Perl function can load external code with "use", "require",
or "do". Since those are potentially dangerous operations you'll
need to create the function with plperlu, which means you'll need
to create the function as a database superuser. See "Trusted and
Untrusted PL/Perl" in the documentation for more information.

Regarding "use", "require", and "do" see the Perl documentation,
in particular the perlfunc and perlmod manual pages.

Hi,
Can you please give me pointers to how to establish clinet server model
using PL/Perl.
I mean how do i give the ip address of the database server in my Perl script
running in another machine.
Regards,
Jas

8/26/06, Michael Fuhr <mike (AT) fuhr (DOT) orgwrote:

Sat, Aug 26, 2006 at 03:32:37PM -0400, Jasbinder Bali wrote:
Do we have any concept of shared objects in perl.
Just wondering, how do we dynamically load something written in perl in
postgresql.

A PL/Perl function can load external code with "use", "require",
or "do". Since those are potentially dangerous operations you'll
need to create the function with plperlu, which means you'll need
to create the function as a database superuser. See "Trusted and
Untrusted PL/Perl" in the documentation for more information.

Regarding "use", "require", and "do" see the Perl documentation,
in particular the perlfunc and perlmod manual pages.

Sun, Aug 27, 2006 at 05:13:25PM -0400, Jasbinder Bali wrote:
Can you please give me pointers to how to establish clinet server model
using PL/Perl.
I mean how do i give the ip address of the database server in my Perl script
running in another machine.

DBI is a Perl module for connecting to databases; to connect to a
PostgreSQL database you'll also need DBD::Pg. If you have those
modules installed then use a command like "man DBI" or "perldoc
DBI" (or however you read documentation on your system) for more
information.

Are you creating a PL/Perl function in one database that needs to
connect to a different database? What exactly are you trying to
do?

Michael Fuhr wrote:
Sun, Aug 27, 2006 at 05:13:25PM -0400, Jasbinder Bali wrote:
>Can you please give me pointers to how to establish clinet server model
>using PL/Perl.
>I mean how do i give the ip address of the database server in my Perl script
>running in another machine.

DBI is a Perl module for connecting to databases; to connect to a
PostgreSQL database you'll also need DBD::Pg. If you have those
modules installed then use a command like "man DBI" or "perldoc
DBI" (or however you read documentation on your system) for more
information.

and 'perldoc Pg' for Pg specific info

The actual scenario is like my perl code is on one computer and database
server is on the other
computer. The perl code needs to connect to that database server residing on
a diff computer.

I think client machine should also have DBI module in it. right?

Also, how much of a change would it be if i have to migrate my triggers and
functions written for C to Perl

Thanks,
Jas

8/27/06, Michael Fuhr <mike (AT) fuhr (DOT) orgwrote:

Sun, Aug 27, 2006 at 05:13:25PM -0400, Jasbinder Bali wrote:
Can you please give me pointers to how to establish clinet server model
using PL/Perl.
I mean how do i give the ip address of the database server in my Perl
script
running in another machine.

DBI is a Perl module for connecting to databases; to connect to a
PostgreSQL database you'll also need DBD::Pg. If you have those
modules installed then use a command like "man DBI" or "perldoc
DBI" (or however you read documentation on your system) for more
information.

Are you creating a PL/Perl function in one database that needs to
connect to a different database? What exactly are you trying to
do?

Sun, Aug 27, 2006 at 09:41:39PM -0400, Jasbinder Bali wrote:
The actual scenario is like my perl code is on one computer and database
server is on the other computer. The perl code needs to connect to that
database server residing on a diff computer.

I think client machine should also have DBI module in it. right?

Right. The client machine needs DBI (the database-independent
module), DBD::Pg (the PostgreSQL-specific driver), and libpq (the
PostgreSQL client library).

Also, how much of a change would it be if i have to migrate my triggers
and functions written for C to Perl

That depends on how many triggers you have, how elaborate they are,
and how proficient you are at Perl. I tend to use PL/pgSQL for
functions that involve a lot of SQL statements; I use PL/Perl or
PL/Ruby for things like text manipulation that those languages are
good at.

Just wondering why would i need libpq here.
Doesn't DBD::pg has its own functions for database related activities.
I think i'm quite naive in this.

Also, the triggers that i wrote in C are not all that elaborative. They are
pretty basic triggers. Also, I'm a rookie in perl but don't need to do
something hifi with it.

Thanks,
~Jas

8/28/06, Michael Fuhr <mike (AT) fuhr (DOT) orgwrote:

Sun, Aug 27, 2006 at 09:41:39PM -0400, Jasbinder Bali wrote:
The actual scenario is like my perl code is on one computer and database
server is on the other computer. The perl code needs to connect to that
database server residing on a diff computer.

I think client machine should also have DBI module in it. right?

Right. The client machine needs DBI (the database-independent
module), DBD::Pg (the PostgreSQL-specific driver), and libpq (the
PostgreSQL client library).

Also, how much of a change would it be if i have to migrate my triggers
and functions written for C to Perl

That depends on how many triggers you have, how elaborate they are,
and how proficient you are at Perl. I tend to use PL/pgSQL for
functions that involve a lot of SQL statements; I use PL/Perl or
PL/Ruby for things like text manipulation that those languages are
good at.

Mon, Aug 28, 2006 at 01:29:11AM -0400, Jasbinder Bali wrote:
Just wondering why would i need libpq here.
Doesn't DBD::pg has its own functions for database related activities.
I think i'm quite naive in this.

DBD::Pg uses libpq to do the talking to the database. Think of it as
the driver that interfaces DBI (the perl database interface) with
libpq.

Incidently, there is a DBD::PgPP (Pg Pure Perl) which tries to do the
same without libpq, but I've never seen it in action.

Also, the triggers that i wrote in C are not all that elaborative. They are
pretty basic triggers. Also, I'm a rookie in perl but don't need to do
something hifi with it.

What are you good at then. If you understand SQL, write them in
pl/pgsql. Use what you're confortable with.

And you don't need DBI or anything like that for writing triggers,
that's what SPI is for.

Have a nice day,

Thanks for that reply Martijn,

Just wondering what is this SPI all about and where can i read up on that.
Any quick pointers please?

Thanks
~Jas

8/28/06, Martijn van <kleptog (AT) svana (DOT) orgwrote:

Mon, Aug 28, 2006 at 01:29:11AM -0400, Jasbinder Bali wrote:
Just wondering why would i need libpq here.
Doesn't DBD::pg has its own functions for database related activities.
I think i'm quite naive in this.

DBD::Pg uses libpq to do the talking to the database. Think of it as
the driver that interfaces DBI (the perl database interface) with
libpq.

Incidently, there is a DBD::PgPP (Pg Pure Perl) which tries to do the
same without libpq, but I've never seen it in action.

Also, the triggers that i wrote in C are not all that elaborative. They
are
pretty basic triggers. Also, I'm a rookie in perl but don't need to do
something hifi with it.

What are you good at then. If you understand SQL, write them in
pl/pgsql. Use what you're confortable with.

And you don't need DBI or anything like that for writing triggers,
that's what SPI is for.

Have a nice day,

Mon, Aug 28, 2006 at 02:08:28AM -0400, Jasbinder Bali wrote:
Thanks for that reply Martijn,

Just wondering what is this SPI all about and where can i read up on that.
Any quick pointers please?

The documentation is a good start,

Though you should go through the entire "Server Side Programming"
section to wrok out what is relevent for you.

Have a ncie day,

Its because my trigger has to initiate some unix tools and the code for
the same is already written in Perl.
So my trigger just needs to call the Perl program that would do the needful
eventually.

~Jas

8/28/06, Gerald Timothy G Quimpo <gerald.quimpo (AT) qualservcentral (DOT) com>
wrote:

Mon, 2006-08-28 at 01:29 -0400, Jasbinder Bali wrote:
Also, the triggers that i wrote in C are not all that elaborative.
They are pretty basic triggers. Also, I'm a rookie in perl but don't
need to do something hifi with it.

Any reason why you don't do your functions and triggers in
pl/pgsql instead? It's simpler to setup and learn
than pl/perl, IM

tiger

Mon, 2006-08-28 at 01:29 -0400, Jasbinder Bali wrote:
Also, the triggers that i wrote in C are not all that elaborative.
They are pretty basic triggers. Also, I'm a rookie in perl but don't
need to do something hifi with it.

Any reason why you don't do your functions and triggers in
pl/pgsql instead? It's simpler to setup and learn
than pl/perl, IM

tiger