Hi All,

Three days ago I figured out that the text processing that I needed to do was
not going to be easy and perhaps even impossible in plpgsql. Undaunted I set
out to learn perl, write a program, add plperl to my database and create a
plperl function. As I said that was 3 days ago. 8-0 But now I am back and I
have only the last item to accomplish. The function is written out below and
an example call to it is here

SELECT * FROM func_extract_size(quote_literal('3000 HTPP Black 4in sq Border:
WNY200BK Size: 14\'8.5" x 16\'7" Tag: None'));

First run through I get
ERROR: creation of Perl function failed: 'require' trapped by operation mask
at (eval 12) line 4.

Hmmm, so I lose the "use strict;" On the next run I get
ERROR: creation of Perl function failed: 'require' trapped by operation mask
at (eval 14) line 4.

Okay, so I put the "use strict;" back in and delete "use warnings;" On the
next run I get
ERROR: creation of Perl function failed: 'require' trapped by operation mask
at (eval 16) line 4.

So now I figure well I can't use either of them. Naturally I delete "use
warnings;" again so that neither of them are in the function. On the next run
I get
ERROR: error from Perl function: Can't call method "RETURN" without a package
or object reference at (eval 18) line 96.

This leaves me with two questions.
1) Why can I not use "use strict;" or "use warnings;" as they are apparently
good perl programming practice. I say apparently because if you remember I
started learning this language 3 days ago and must be considered a neophyte. :-)
2) What is wrong with the use of RETURN?

Kind Regards,
Keith

-- Function: func_extract_size(varchar)

-- DROP FUNCTION func_extract_size("varchar");

CREATE OR REPLACE FUNCTION func_extract_size("varchar")
RETURNS int AS
$BODY$
# A function to extract the net size.
# One input argument. description Case insensitive.

# Define a subroutine that accepts a dimension string (xx' xx") and returns
# feet and inch strings.
sub sub_parse_dimension{
# Initialize local variables.
my $v_feet_str = "";
my $v_inch_str = "";
my $v_dim_str = shift(@_);
# Split the dimension into feet and inch parts using pattern matching
# and parentheses to capture the desired parts.
$v_dim_str =~ /(?[\d\.\s\-\/]+)')?\s*(?[\d\.\s\-\/]+)")?/;
$v_feet_str = defined $1 ? $1 : 0;
$v_inch_str = defined $2 ? $2 : 0;
return ($v_feet_str, $v_inch_str);
}

# Define a subroutine that accepts a mixed number string and returns
# a decimal number.
sub sub_xform_mixed_number{
# Initialize local variables.
my $v_decimal = 0;
my $v_dim_str = shift(@_);
# Check for fraction in dimension string.
if ($v_dim_str =~ /\//){
# There is a fraction to deal with.
# Parse the fraction using whitespace or a hyphen (-) and the forward
# slash (/) character.
$v_dim_str =~ /(?[\d\.]+))?[\-\s]*(?\d+))?\/(?\d+))?/;
my $v_whole = defined $1 ? $1 : 0;
my $v_numer = defined $2 ? $2 : 0;
my $v_denom = defined $3 ? $3 : 0;
$v_decimal = $v_whole + $v_numer/$v_denom;
} else {
# There is no fraction present. Set the output equal to the input.
$v_decimal = $v_dim_str;
}
return $v_decimal;
}

# Begining of the program.
my $v_description = shift(@_);
my $v_border_id = "";
my $v_dim1_total = 0;
my $v_dim2_total = 0;
my $v_tag = "";

# Perform a case insensitive check for the proper data format. Capture the
# desired parts of the data using parentheses.
if (/.*border:\s*(.*)\s*size:\s*(.*)\s*tag:\s*(.*)\s*/i){
# Store the capture patterns in variables to avoid unpredictable results.
my ($v_border_str, $v_size_str, $v_tag_str) = ($1, $2, $3);
# Check for no border.
if ($v_border_str =~ /none/i){
$v_border_id = "";
} else {
$v_border_id = $v_border_str;
}
# Parse up the size string.
if ($v_size_str =~ /\d+\s*['"]\s*x\s*\d+\s*['"]/i){
# It looks like a size string so continue to process.
my $v_dim1_str = "";
my $v_dim2_str = "";
my $v_feet_str = "";
my $v_inch_str = "";
# Split the size string into its two parts.
($v_dim1_str, $v_dim2_str) = split(/\s*x\s*/i, $v_size_str);
# Now split dimension one into feet and inch parts.
($v_feet_str, $v_inch_str) = sub_parse_dimension($v_dim1_str);
# Merge the components of the dimension into a single value.
$v_dim1_total = ( ( 12 * sub_xform_mixed_number($v_feet_str) )
+ sub_xform_mixed_number($v_inch_str)
);
# Now split dimension two into feet and inch parts.
($v_feet_str, $v_inch_str) = sub_parse_dimension($v_dim2_str);
# Merge the components of the dimension into a single value.
$v_dim2_total = ( ( 12 * sub_xform_mixed_number($v_feet_str) )
+ sub_xform_mixed_number($v_inch_str)
);
}
# Check for no tag.
if ($v_tag_str =~ /none/i){
$v_tag = "";
} else {
$v_tag = $v_tag_str;
}
} else {
$v_border_id = "";
$v_dim1_total = 0;
$v_dim2_total = 0;
$v_tag = "";
}

RETURN $v_dim1_total;
$BODY$
LANGUAGE 'plperl' STABLE STRICT;

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Le Thursday 21 April 2005 21:38, Keith Worthington a écrit :
> This leaves me with two questions.
> 1) *Why can I not use "use strict;" or "use warnings;" as they are
> apparently good perl programming practice. *I say apparently because ifyou
> remember I started learning this language 3 days ago and must be considered
> a neophyte. :-)

You can't use "use" instruction for security reason. All disk access are
denied and use need a disk access.

I think you need to use plperlu.

see: http://www.postgresql.org/docs/8.0/i...l-trusted.html

> 2) *What is wrong with the use of RETURN?

Don't understand myself.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (GNU/Linux)

iD8DBQBCaAu+k29cDOWzfVARAmsdAJ4tNC+KN/1vPp6RO3kgW+jZCUY2VwCgvj/D
IZjDI1D7dNtTPK2+NpKxAAw=
=fiHQ
-----END PGP SIGNATURE-----