Re: Function calling error in postgreSQL 7.3.1

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
> 
>