Thread: Function calling error in postgreSQL 7.3.1

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

On Thu, 13 Mar 2003, vincent wrote:

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

That is not going to use the argument in the FROM.  If you want to do
something like that you're probably going to need to look into using
EXECUTE.

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