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

Re: Help with creating function

From: "Henshall, Stuart - WCP" <SHenshall(at)westcountrypublications(dot)co(dot)uk>
To: "'cmasters(at)nbnet(dot)nb(dot)ca'" <cmasters(at)nbnet(dot)nb(dot)ca>,pgsql-nov <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Help with creating function
Date: 2002-06-27 07:41:40
Message-ID: E2870D8CE1CCD311BAF50008C71EDE8E01F748FE@MAIL_EXCHANGE (view raw or flat)
Thread:
Lists: pgsql-novice
Is something like this what you wanted?
create function my_concat(text,text,text) returns text as '
select $1 || $2 || $3;
' language 'sql';
hth,
- Stuart

> -----Original Message-----
> From: cmasters [mailto:cmasters(at)nbnet(dot)nb(dot)ca]
> 
> Greetings,
> 
> Could someone please help with creating the following function:
> 
> As a psql command it would be
> 
> set var4 = upper(rpad(var1, 5, '-')) || lower(rpad(var2, 5, '-')) ||
> lpad(var3, 5, '0'))
> 
> All variables are text.
> 
> I am attempting to assign a value to var4 that concatenates 
> var 1, 2 & 3. As
> Var 1 to 3 may collectively exist on several tables -- i.e 
> a.1, a.2, a.3 vs
> b.1, b.2, and b.3 -- but all results for var4 will be 
> inserted into yet
> another table "var4".
> 
> Example:
> 
> Table "Client" contains 
> ID
> SURNAME
> GIVEN
> 
> Table "author" contains
> ID
> SURNAME
> GIVEN
> 
> Table "nick" would contain
> NICK (var4)
> table of origin
> SURNAME
> 
> I have attempted to create this procedure as a function:
> 
> CREATE FUNCTION nick_sur (text, text, text)
> RETURNS TEXT
> as
> ... balance of above sql stmt.
> 
> both with and without the 'set =' prefix, using both 'text' 
> and '$1', '$2',
> and '$3' in place of 'var(x)' and combinations of the 
> preceding, also with
> the language (sql) quoted and not qutoed.
> 
> I've searched the documentation, '\h create function' and the 
> postgres site,
> but can't seem to get the right syntax for creating this function.
> 
> Many thanks,
> C-Cose Masters
> 
> 
> 
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to 
> majordomo(at)postgresql(dot)org
> 
> 

Responses

pgsql-novice by date

Next:From: Pierre-Yves LanduréDate: 2002-06-27 07:59:54
Subject: Polygon ?# index creating
Previous:From: Sharon CowlingDate: 2002-06-27 04:17:00
Subject: Re: Date Question

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