X-Original-To: pgsql-sql@postgresql.org Received: from news.hub.org (news.hub.org [64.49.215.80]) by postgresql.org (Postfix) with ESMTP id 4D7D447590C for ; Fri, 14 Mar 2003 17:43:48 -0500 (EST) Received: by news.hub.org (Postfix, from userid 8) id 05B1B43A611; Fri, 14 Mar 2003 17:43:48 -0500 (EST) From: CoL X-Newsgroups: comp.databases.postgresql.sql Subject: Re: Function calling error in postgreSQL 7.3.1 Date: Fri, 14 Mar 2003 23:49:46 +0100 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 59 Message-ID: References: Reply-To: col@mportal.hu Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.3) Gecko/20030312 X-Accept-Language: hu, en-us, en In-Reply-To: To: pgsql-sql@postgresql.org X-Archive-Number: 200303/410 X-Sequence-Number: 12450 hi, how about: CREATE FUNCTION sc_company.dbseq(name, text) ? Or use execute '' sql ''; C. vincent wrote, On 3/13/2003 4:09 AM: > 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 > >