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: ^
***********************
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