function in a view

Поиск
Список
Период
Сортировка
От Keith Worthington
Тема function in a view
Дата
Msg-id 20050428184647.M32146@narrowpathinc.com
обсуждение исходный текст
Ответы Re: function in a view  (Michael Fuhr <mike@fuhr.org>)
Список 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

В списке pgsql-novice по дате отправления:

Предыдущее
От: tövis
Дата:
Сообщение: check CREATE/DROP INDEX
Следующее
От: Volkan YAZICI
Дата:
Сообщение: Re: check CREATE/DROP INDEX