Re: string concatenation errors

Tried that but it didnt work =( Here is the error:

CREATE FUNCTION
cs442=# select myTest();
ERROR:  column "*" does not exist
CONTEXT:  SQL statement "SELECT   $1  || "*" ||  $2 "
         PL/pgSQL function "mytest" line 16 at assignment
ERROR:  column "*" does not exist
CONTEXT:  SQL statement "SELECT   $1  || "*" ||  $2 "
PL/pgSQL function "mytest" line 16 at assignment

Thank you for any help you can offer.

Best regards,

Dane Ensign




On Aug 3, 2005, at 10:58 PM, Gnanavel S wrote:

>
>
> On 8/4/05, Dane Ensign <dane@deepsnow.org> wrote: 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 || '*' ||
>
> you have to escape the codes with an additional single quotes like
> this
>  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
>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
>
>
> --
> with regards,
> S.Gnanavel
> Satyam Computer Services Ltd.

Dane Ensign
dane@deepsnow.org