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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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/
From | Date | Subject | |
---|---|---|---|
Next Message | Keith Worthington | 2005-04-29 14:18:35 | Re: function in a view |
Previous Message | Volkan YAZICI | 2005-04-28 19:56:41 | Re: check CREATE/DROP INDEX |