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 7C4374758E6
	for <pgsql-sql@postgresql.org>; Wed, 12 Mar 2003 22:03:41 -0500 (EST)
Received: by news.hub.org (Postfix, from userid 8)
	id 71A9543A611; Wed, 12 Mar 2003 22:03:40 -0500 (EST)
From: "vincent" <waimeng@efkonasia.com.my>
X-Newsgroups: comp.databases.postgresql.sql
Subject: Function calling error in postgreSQL 7.3.1
Date: Thu, 13 Mar 2003 11:09:32 +0800
Organization: efkonasia
Lines: 51
Message-ID: <b4oseb$101n$1@news.hub.org>
Reply-To: "vincent" <waimeng@efkonasia.com.my>
X-Complaints-To: usenet@news.hub.org
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
To: pgsql-sql@postgresql.org
X-Archive-Number: 200303/325
X-Sequence-Number: 12364

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