From: vincent <waimeng@efkonasia.com.my>
To: pgsql-sql@postgresql.org
Subject: Function calling error in postgreSQL 7.3.1
Date: Thu, 13 Mar 2003 11:09:32 +0800
Message-ID: <b4oseb$101n$1@news.hub.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: waimeng@efkonasia.com.my
Subject: Re: Function calling error in postgreSQL 7.3.1
In-Reply-To: <b4oseb$101n$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox