Thread: function in a view
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
On Thu, Apr 28, 2005 at 02:58:53PM -0400, Keith Worthington wrote:
>
> 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.
Here's a simple example that might provide inspiration. It works
in PostgreSQL 8.0.2:
CREATE TYPE testtype AS (
a text,
b text,
c text
);
CREATE FUNCTION testfunc(text) RETURNS testtype AS $$
my @s = split(/:/, $_[0]);
return {a => $s[0], b => $s[1], c => $s[2]};
$$ LANGUAGE plperl IMMUTABLE STRICT;
CREATE TABLE foo (id serial, t text);
INSERT INTO foo (t) VALUES ('abc:def:ghi');
SELECT id, t, testfunc(t) FROM foo;
id | t | testfunc
----+-------------+---------------
1 | abc:def:ghi | (abc,def,ghi)
(1 row)
SELECT id, t, (testfunc(t)).* FROM foo;
id | t | a | b | c
----+-------------+-----+-----+-----
1 | abc:def:ghi | abc | def | ghi
(1 row)
SELECT id, t,
'A: ' || (testfunc(t)).a AS col_a,
'B: ' || (testfunc(t)).b AS col_b,
'C: ' || (testfunc(t)).c AS col_c
FROM foo;
id | t | col_a | col_b | col_c
----+-------------+--------+--------+--------
1 | abc:def:ghi | A: abc | B: def | C: ghi
(1 row)
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
Michael Fuhr wrote:
> On Thu, Apr 28, 2005 at 02:58:53PM -0400, Keith Worthington wrote:
>
>>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.
>
>
> Here's a simple example that might provide inspiration. It works
> in PostgreSQL 8.0.2:
>
> CREATE TYPE testtype AS (
> a text,
> b text,
> c text
> );
>
> CREATE FUNCTION testfunc(text) RETURNS testtype AS $$
> my @s = split(/:/, $_[0]);
> return {a => $s[0], b => $s[1], c => $s[2]};
> $$ LANGUAGE plperl IMMUTABLE STRICT;
>
> CREATE TABLE foo (id serial, t text);
> INSERT INTO foo (t) VALUES ('abc:def:ghi');
>
> SELECT id, t, testfunc(t) FROM foo;
> id | t | testfunc
> ----+-------------+---------------
> 1 | abc:def:ghi | (abc,def,ghi)
> (1 row)
>
> SELECT id, t, (testfunc(t)).* FROM foo;
> id | t | a | b | c
> ----+-------------+-----+-----+-----
> 1 | abc:def:ghi | abc | def | ghi
> (1 row)
>
> SELECT id, t,
> 'A: ' || (testfunc(t)).a AS col_a,
> 'B: ' || (testfunc(t)).b AS col_b,
> 'C: ' || (testfunc(t)).c AS col_c
> FROM foo;
> id | t | col_a | col_b | col_c
> ----+-------------+--------+--------+--------
> 1 | abc:def:ghi | A: abc | B: def | C: ghi
> (1 row)
>
I am out of the office today so I won't be able to play with this idea
for a while but it looks interesting.
It seems like what your saying is that by enclosing the function in a
set of parentheses I can access the return elements.
I can't wait to try this.
Thanks tons for the guidance. I will let you know how I make out.
--
Kind Regards,
Keith
On Fri, Apr 29, 2005 at 10:18:35AM -0400, Keith Worthington wrote: > > It seems like what your saying is that by enclosing the function in a > set of parentheses I can access the return elements. Right. See "Field Selection" in the "SQL Syntax" chapter of the documentation, and "Accessing Composite Types" in the "Data Types" chapter. http://www.postgresql.org/docs/8.0/interactive/sql-expressions.html#AEN1642 http://www.postgresql.org/docs/8.0/interactive/rowtypes.html#AEN5582 -- Michael Fuhr http://www.fuhr.org/~mfuhr/