Help with creating function

From: cmasters <cmasters(at)nbnet(dot)nb(dot)ca>
To: pgsql-nov <pgsql-novice(at)postgresql(dot)org>
Subject: Help with creating function
Date: 2002-06-27 03:06:40
Message-ID: 20020627000640.A17684@grommit.mastersdaley.sys
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Browse pgsql-novice by date

  From Date Subject
Next Message Josh Berkus 2002-06-27 03:56:35 Re: Date Question
Previous Message Doug Silver 2002-06-27 00:07:30 Re: Can't get pg_hba.conf to work