Function calling error in postgreSQL 7.3.1

From: "vincent" <waimeng(at)efkonasia(dot)com(dot)my>
To: pgsql-sql(at)postgresql(dot)org
Subject: Function calling error in postgreSQL 7.3.1
Date: 2003-03-13 03:09:32
Message-ID: b4oseb$101n$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Stephan Szabo 2003-03-13 06:05:29 Re: Function calling error in postgreSQL 7.3.1
Previous Message Dan Langille 2003-03-12 22:59:53 Re: extending ORDER BY make query much slower