Postgres Pro
Downloads
Home   >   mailing lists

Thread: string concatenation errors

string concatenation errors

From
Dane Ensign
Date:
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

Re: string concatenation errors

From
Gnanavel S
Date:


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.

Re: string concatenation errors

From
Gnanavel S
Date:


On 8/4/05, Dane Ensign <dane@deepsnow.org> wrote:
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.

You have to put two single quotes instead of double quotes

ps. reply to the list

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






--
with regards,
S.Gnanavel
Satyam Computer Services Ltd.