From: | "Keith Worthington" <keithw(at)narrowpathinc(dot)com> |
---|---|
To: | "PostgreSQL Novice" <pgsql-novice(at)postgresql(dot)org> |
Subject: | function in a view |
Date: | 2005-04-28 18:58:53 |
Message-ID: | 20050428184647.M32146@narrowpathinc.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Hi All,
I have created a function that extracts three parts of a string using plperl.
Now I want to use those parts in a view and I don't even know where to start.
My first feeble attempt looked like this:
SELECT tbl_line_item.so_number,
tbl_line_item.so_line,
tbl_line_item.quantity,
'Border: '::text ||
func_parse_net_desc(tbl_item_description.description).border_str AS line_1,
'Size: '::text ||
func_parse_net_desc(tbl_item_description.description).size_str AS line_2,
'Tag: '::text ||
func_parse_net_desc(tbl_item_description.description).tag_str AS line_3
FROM tbl_line_item
LEFT JOIN tbl_item_description
ON tbl_line_item.so_number = tbl_item_description.so_number AND
tbl_line_item.so_line = tbl_item_description.so_line
LEFT JOIN tbl_item
ON tbl_line_item.item_id::text = tbl_item.id::text
WHERE tbl_item.item_type::text = 'NET'::text
ORDER BY tbl_line_item.so_number,
tbl_line_item.so_line;
I knew even before I ran it it was going to fail miserably. The end objective
is to create a view of this query.
Can this be done?
For reference here is the function and an example run using a fixed string.
-- DROP FUNCTION func_parse_net_desc("varchar");
-- DROP TYPE func_parse_net_desc;
CREATE TYPE func_parse_net_desc AS
(border_str varchar(64),
size_str varchar(64),
tag_str varchar(64));
ALTER TYPE func_parse_net_desc OWNER TO postgres;
CREATE OR REPLACE FUNCTION func_parse_net_desc("varchar")
RETURNS func_parse_net_desc AS
$BODY$
# A function to parse a net description into its border, size and tag parts.
# One input argument. description Case insensitive.
use strict;
use warnings;
# Initialize the program variables.
my $v_description = shift(@_);
my $v_border_str = "";
my $v_size_str = "";
my $v_tag_str = "";
# Perform a case insensitive check for the proper data format. Capture the
# desired parts of the data using parentheses.
if ($v_description =~ /.*border:\s*(.*)\s*size:\s*(.*)\s*tag:\s*(.*)\s*/i){
# Store the capture patterns in variables to avoid unpredictable results.
($v_border_str, $v_size_str, $v_tag_str) = ($1, $2, $3);
} else {
($v_border_str, $v_size_str, $v_tag_str) = ("", "", "");
}
return {border_str => $v_border_str,
size_str => $v_size_str,
tag_str => $v_tag_str};
$BODY$
LANGUAGE 'plperlu' STABLE STRICT;
TESTDB=# SELECT * FROM func_parse_net_desc('3000 HTPP Black 4in sq Border:
WNY200BK Size: 14\'8.5" x 16\'7" Tag: None');
border_str | size_str | tag_str
------------+------------------+---------
WNY200BK | 14'8.5" x 16'7" | None
(1 row)
Kind Regards,
Keith
From | Date | Subject | |
---|---|---|---|
Next Message | Volkan YAZICI | 2005-04-28 19:56:41 | Re: check CREATE/DROP INDEX |
Previous Message | tövis | 2005-04-28 18:44:26 | check CREATE/DROP INDEX |