From: CoL <col@mportal.hu>
To: pgsql-sql@postgresql.org
Subject: Re: Function calling error in postgreSQL 7.3.1
Date: Fri, 14 Mar 2003 23:49:46 +0100
Message-ID: <b4tlv3$40r$1@news.hub.org> (raw)
In-Reply-To: <b4oseb$101n$1@news.hub.org>
References: <b4oseb$101n$1@news.hub.org>
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
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: col@mportal.hu
Subject: Re: Function calling error in postgreSQL 7.3.1
In-Reply-To: <b4tlv3$40r$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox