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

Re: function in a view

From: Keith Worthington <KeithW(at)NarrowPathInc(dot)com>
To: Michael Fuhr <mike(at)fuhr(dot)org>
Cc: PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: function in a view
Date: 2005-04-29 14:18:35
Message-ID: 4272423B.6010100@NarrowPathInc.com (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

Responses

pgsql-novice by date

Next:From: Michael FuhrDate: 2005-04-29 14:49:08
Subject: Re: function in a view
Previous:From: Michael FuhrDate: 2005-04-29 05:00:19
Subject: Re: function in a view

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