Thread: function in a view

function in a view

From
"Keith Worthington"
Date:
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

Re: function in a view

From
Michael Fuhr
Date:
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/

Re: function in a view

From
Keith Worthington
Date:
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

Re: function in a view

From
Michael Fuhr
Date:
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/