From: Keith Worthington <keithw@narrowpathinc.com>
To: PostgreSQL Interfaces <pgsql-interfaces@postgresql.org>
Subject: plperl user function
Date: Thu, 21 Apr 2005 15:46:08 -0400
Message-ID: <20050421194438.M68704@narrowpathinc.com> (raw)
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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: keithw@narrowpathinc.com
Subject: Re: plperl user function
In-Reply-To: <20050421194438.M68704@narrowpathinc.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox