Skip site navigation (1) Skip section navigation (2)

Re: function in a view

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Keith Worthington <keithw(at)narrowpathinc(dot)com>
Cc: PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: function in a view
Date: 2005-04-29 05:00:19
Message-ID: 20050429050019.GA68830@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-novice
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/

In response to

Responses

pgsql-novice by date

Next:From: Keith WorthingtonDate: 2005-04-29 14:18:35
Subject: Re: function in a view
Previous:From: Volkan YAZICIDate: 2005-04-28 19:56:41
Subject: Re: check CREATE/DROP INDEX

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group