pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFunction calling error in postgreSQL 7.3.1
3+ messages / 3 participants
[nested] [flat]
* Function calling error in postgreSQL 7.3.1
@ 2003-03-13 03:09 vincent <waimeng@efkonasia.com.my>
2003-03-13 06:05 ` Re: Function calling error in postgreSQL 7.3.1 Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-03-14 22:49 ` Re: Function calling error in postgreSQL 7.3.1 CoL <col@mportal.hu>
0 siblings, 2 replies; 3+ messages in thread
From: vincent @ 2003-03-13 03:09 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Function calling error in postgreSQL 7.3.1
2003-03-13 03:09 Function calling error in postgreSQL 7.3.1 vincent <waimeng@efkonasia.com.my>
@ 2003-03-13 06:05 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
1 sibling, 0 replies; 3+ messages in thread
From: Stephan Szabo @ 2003-03-13 06:05 UTC (permalink / raw)
To: vincent <waimeng@efkonasia.com.my>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Function calling error in postgreSQL 7.3.1
2003-03-13 03:09 Function calling error in postgreSQL 7.3.1 vincent <waimeng@efkonasia.com.my>
@ 2003-03-14 22:49 ` CoL <col@mportal.hu>
1 sibling, 0 replies; 3+ messages in thread
From: CoL @ 2003-03-14 22:49 UTC (permalink / raw)
To: pgsql-sql
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
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-03-14 22:49 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-13 03:09 Function calling error in postgreSQL 7.3.1 vincent <waimeng@efkonasia.com.my>
2003-03-13 06:05 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-03-14 22:49 ` CoL <col@mportal.hu>
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