Re: Help with creating function - Mailing list pgsql-novice

From Henshall, Stuart - WCP
Subject Re: Help with creating function
Date
Msg-id E2870D8CE1CCD311BAF50008C71EDE8E01F748FE@MAIL_EXCHANGE
Whole thread Raw
In response to Help with creating function  (cmasters <cmasters@nbnet.nb.ca>)
Responses Re: Help with creating function  (cmasters <cmasters@nbnet.nb.ca>)
List 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@nbnet.nb.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@postgresql.org
>
>

pgsql-novice by date:

Previous
From: Sharon Cowling
Date:
Subject: Re: Date Question
Next
From: Pierre-Yves LANDURE
Date:
Subject: Creating polygon index