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