Re: back slash separated values

["surabhi.ahuja" <surabhi.ahuja@xxxxxxxxxxx>]

Title: Re: [GENERAL] back slash separated values

SHOW server_version;
 server_version
----------------
 8.0.6
(1 row)

 

the stored procedure is :

 

CREATE OR REPLACE FUNCTION update_exam_modality(bigint, varchar(16)) RETURNS text AS'
DECLARE
    mod_of_study varchar(16);
    mod_pattern varchar(16);
       pos int;
BEGIN
    pos := -1;
       select into mod_of_study trim(mod_in_exam) from exam where exam_id = $1;
    if (mod_of_study is NULL)
    then
        update exam set mod_in_exam = $2 where exam_id =$1;
    else
        pos := strpos(mod_of_study, trim($2));
        if( pos != 0 )
        then
            RETURN ''no_change'';
        else
            mod_pattern := mod_of_study || '\\' || $2;
            update exam set mod_in_exam = mod_pattern where exam_id =$1;
        end if;
    end if;
    RETURN ''done'';
END;
'LANGUAGE 'plpgsql';

 

 

and when i run

\i temsql

 

i get the following error:

 

 

psql:temsql:21: invalid command \
psql:temsql:22: ERROR:  syntax error at or near "update" at character 669
psql:temsql:22: LINE 22:             update exam set mod_in_exam = mod_pattern where ...
psql:temsql:22:                      ^
psql:temsql:23: ERROR:  syntax error at or near "if" at character 5
psql:temsql:23: LINE 1: end if;
psql:temsql:23:             ^
psql:temsql:24: ERROR:  syntax error at or near "if" at character 5
psql:temsql:24: LINE 1: end if;
psql:temsql:24:             ^
psql:temsql:25: ERROR:  syntax error at or near "RETURN" at character 1
psql:temsql:25: LINE 1: RETURN ''done'';
psql:temsql:25:         ^
psql:temsql:26: WARNING:  there is no transaction in progress
COMMIT
psql:temsql:27: ERROR:  syntax error at or near "'LANGUAGE '" at character 1
psql:temsql:27: LINE 1: 'LANGUAGE 'plpgsql';
psql:temsql:27:         ^

---

From: pgsql-general-owner@xxxxxxxxxxxxxx on behalf of William ZHANG
Sent: Wed 3/22/2006 3:59 PM
To: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: [GENERAL] back slash separated values

***********************
Your mail has been scanned by InterScan VirusWall.
***********-***********


What's the version of pgsql?

postgres=# show server_version;
 server_version
----------------
 8.1.0
(1 row)

postgres=# CREATE OR REPLACE FUNCTION foo (v_s1 varchar, v_s2 varchar)
postgres-# RETURNS varchar AS $$
postgres$# BEGIN
postgres$#     RETURN v_s1 || '\\' || v_s2;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
CREATE FUNCTION

postgres=# SELECT foo ('pgsql', 'hackers');
      foo
---------------
 pgsql\hackers
(1 row)

Regards,
William ZHANG



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend