Hi all,
I'm trying to create a function that take in 2 varchar string and return a
string.
The function was created successfully, but when i issued the following
command via pgadmin II,
it give me the following error:
select * from sc_company.dbseq('sc_company', 'COMPANY');
ERROR: Namespace "in_schname" does not exist
CREATE FUNCTION sc_company.dbseq(text, text) RETURNS varchar AS 'DECLARE in_schname ALIAS FOR $1; in_seqname ALIAS FOR
$2;retval VARCHAR := 0;BEGIN SELECT SEQ_VAL INTO retval FROM in_schname.DB_SEQ WHERE SEQ_NAME =
in_seqname;
IF NOT FOUND THEN INSERT INTO in_schname.DB_SEQ VALUES (in_seqname, 1); RETURN 1; ELSE
retval:= TO_CHAR(TO_NUMBER(retval) + 1); UPDATE in_schname.DB_SEQ SET SEQ_VAL = retval WHERE SEQ_NAME =
in_seqname; RETURN retval; END IF;END;
' LANGUAGE 'plpgsql'
CREATE TABLE sc_company.db_seq ( seq_name varchar(10) NOT NULL, seq_val varchar(12), CONSTRAINT db_seq_pkey PRIMARY KEY
(seq_name)
);
Note: I'm using postgres version 7.3.1 on windows2k/cygwin
Please help!
vincent