Thread: Help with creating function

Help with creating function

From

cmasters

Date:

27 June 2002, 02:07:58

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

Re: Help with creating function

From

"Henshall, Stuart - WCP"

Date:

27 June 2002, 06:55:08

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
>
>

Re: Help with creating function

From

cmasters

Date:

27 June 2002, 19:40:55

Greetings Stuart and Novices

On 27-06-02, Henshall, Stuart - WCP wrote:
> 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
>

Although the above command was accepted, as I understand command syntax,
this would still force me to explicityly define each case as 'my_concat'
variables. This diesn't save my much typing :-(.

What I'm attempting to do is convert the following 'update' command to an
insert command. Is this possible?

for ease of use, we will assume that all var exist in the ~same~ table:

update table table1
set var4 =
upper (rpad (var1, 5, '-')) ||
lower (rpad (var2, 5, '-')) ||
lpad (var3, 5, '0'))
;

When I execute this as an update command, it works fine. However if I choose
to create a separate table for 'var4' and related reference fields, I will
have to ~insert~ the values into it.

As such I am attempting to create a function that can be called from any
legal command that will accomplish the same thing: chomp the variable(s) per
requirements and concatenate them into a new fourth variable (examples below).

Many thanks,
C-Cose Masters

> > -----Original Message-----
> > From: cmasters [mailto:cmasters@nbnet.nb.ca]
> >

... snip previous explanattion

> >
> > Example:
> >
> > Table "Client" contains
> > ID
> > SURNAME
> > GIVEN
> >
> > Table "author" contains
> > ID
> > SURNAME
> > GIVEN
> >
> > Table "nick" would contain
> > NICK (var4)
> > table of origin
> > SURNAME
> >