string concatenation errors - Mailing list pgsql-novice

Hi Gang,

I am trying to concatenate 2 values plus an asterisk in between them,
like this: value1*value2.
My function keeps breaking =( Please can someone show me the proper
syntax to fix my function?

Thanks in advance for any help you can offer.

/* start */

CREATE OR REPLACE FUNCTION myTest() RETURNS INT4 AS
'
     DECLARE
     cols record;
     cols2 record;
     newTextField1 CHARACTER(3);
     newTextField2 CHARACTER(50);
     finalTextField CHARACTER(55);

     BEGIN
     DELETE FROM MESSAGES;

     FOR cols IN SELECT * FROM dept LOOP
             FOR cols2 IN SELECT * FROM major LOOP
                 IF cols.deptid = cols2.deptid THEN
                     newTextField1 := cols2.deptid;
                     newTextField2 := cols2.description;
                     finalTextField := newTextField1 || '*' ||
newTextField2;
                     INSERT INTO MESSAGES(results)
                     VALUES (finalTextField);
                 END IF;
             END LOOP;
         END LOOP;
     RETURN 1;
     END;

' LANGUAGE 'plpgsql';


/* end */





Dane Ensign
dane@deepsnow.org