Could someone tell me what is wrong withthis function
This is almost a copy / paste from Bruce Momjian's book :
<snip>
create function trigger_iso_code_2_uppercase()
returns opaque
as 'BEGIN
if new.countrycode !~''^[A-Za-z][A-Za-z]$''
then raise exception ''ISO-code are excepted to be uppercase
letters.'';
ENd if;
new.countrycode=upper(new.countrycode);
new.countryname=initcap(new.countryname);
end;'
language 'internal';
</snip>
but I get the following :
<snip>
ERROR: ProcedureCreate: there is no builtin function named "BEGIN
if new.countrycode !~'^[A-Za-z][A-Za-z]$'
then raise exception 'ISO-code are excepted to be uppercase
letters.';
ENd if;
new.countrycode=upper(new.countrycode);
new.countryname=initcap(new.countryname);
end;"
</snip>
Please note that I logging as postgres
tx for your help,
thomas,
--
Thomas SMETS
rue J. Wytsmanstraat 62
1050 Bruxelles
yahoo-id : smetsthomas
1) as 'BEGIN
as '
BEGIN
Put BEGIN in next line
2) if new.countrycode !~''^[A-Za-z][A-Za-z]$''
wont check for only Uppercase Letters try
''^[A-Z]*''
3) You can also use a Rule on the field, but its nice to use a trigger if
you require a better error to be reported
http://www.pgexplorer.com
GUI postgresql Frontend
----- Original Message -----
From: <tsmets@brutele.be>
To: <pgsql-general@postgresql.org>
Sent: Monday, March 25, 2002 4:36 PM
Subject: [GENERAL] Could someone tell me what is wrong withthis function
Show quoted text
This is almost a copy / paste from Bruce Momjian's book :
<snip>
create function trigger_iso_code_2_uppercase()
returns opaque
as 'BEGIN
if new.countrycode !~''^[A-Za-z][A-Za-z]$''
then raise exception ''ISO-code are excepted to be uppercase
letters.'';
ENd if;
new.countrycode=upper(new.countrycode);
new.countryname=initcap(new.countryname);
end;'
language 'internal';
</snip>but I get the following :
<snip>
ERROR: ProcedureCreate: there is no builtin function named "BEGIN
if new.countrycode !~'^[A-Za-z][A-Za-z]$'
then raise exception 'ISO-code are excepted to be uppercase
letters.';
ENd if;
new.countrycode=upper(new.countrycode);
new.countryname=initcap(new.countryname);
end;"
</snip>Please note that I logging as postgres
tx for your help,
thomas,
--
Thomas SMETS
rue J. Wytsmanstraat 62
1050 Bruxelles
yahoo-id : smetsthomas---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
Your language internal i have never heard of that.
Try langauge plpgsql this is what you seem to be writing in
Also the function says RETURNS OPAQUE but it does not return anything
Put just before END:- RETURN NEW;
Also the = should be := in plpgsql anyway. An example is below
HTH
Darren Ferguson
EXAMPLE
CREATE OR REPLACE FUNCTION trigger_iso_code_2_uppercase() RETURNS OPAQUE
AS '
BEGIN
IF NEW.countrycode !~ ''^[A-Za-z][A-Za-z]$'' THEN
RAISE EXCEPTION ''The exception'';
END IF;
NEW.countrycode := upper(NEW.countrycode);
NEW.countryname := initcap(NEW.countryname);
RETURN NEW;
END;' LANGUAGE 'plpgsql';
On Mon, 25 Mar 2002 tsmets@brutele.be wrote:
This is almost a copy / paste from Bruce Momjian's book :
<snip>
create function trigger_iso_code_2_uppercase() returns opaque as '
BEGIN
if new.countrycode !~''^[A-Za-z][A-Za-z]$''
then raise exception ''ISO-code are excepted to be uppercase
Show quoted text
letters.'';
ENd if;
new.countrycode=upper(new.countrycode);
new.countryname=initcap(new.countryname);
end;'
language 'internal';
</snip>but I get the following :
<snip>
ERROR: ProcedureCreate: there is no builtin function named "BEGIN
if new.countrycode !~'^[A-Za-z][A-Za-z]$'
then raise exception 'ISO-code are excepted to be uppercase
letters.';
ENd if;
new.countrycode=upper(new.countrycode);
new.countryname=initcap(new.countryname);
end;"
</snip>Please note that I logging as postgres
tx for your help,
thomas,
--
Thomas SMETS
rue J. Wytsmanstraat 62
1050 Bruxelles
yahoo-id : smetsthomas---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
I created the function OKay, now !
It however complains still when I try to use the :
"create or replace"
instead of the "create function"
any idea ?
thomas,
--
Thomas SMETS
rue J. Wytsmanstraat 62
1050 Bruxelles
yahoo-id : smetsthomas
----- Original Message -----
From: "Darren Ferguson" <darren@crystalballinc.com>
To: <tsmets@brutele.be>
Cc: <pgsql-general@postgresql.org>
Sent: 25 March, 2002 4:15 PM
Subject: Re: [GENERAL] Could someone tell me what is wrong withthis function
Your language internal i have never heard of that.
Try langauge plpgsql this is what you seem to be writing in
Also the function says RETURNS OPAQUE but it does not return anything
Put just before END:- RETURN NEW;
Also the = should be := in plpgsql anyway. An example is below
HTH
Darren Ferguson
EXAMPLE
CREATE OR REPLACE FUNCTION trigger_iso_code_2_uppercase() RETURNS OPAQUE
AS '
BEGINIF NEW.countrycode !~ ''^[A-Za-z][A-Za-z]$'' THEN
RAISE EXCEPTION ''The exception'';
END IF;NEW.countrycode := upper(NEW.countrycode);
NEW.countryname := initcap(NEW.countryname);RETURN NEW;
END;' LANGUAGE 'plpgsql';On Mon, 25 Mar 2002 tsmets@brutele.be wrote:
This is almost a copy / paste from Bruce Momjian's book :
<snip>create function trigger_iso_code_2_uppercase() returns opaque as '
BEGIN
if new.countrycode !~''^[A-Za-z][A-Za-z]$''
then raise exception ''ISO-code are excepted to be uppercaseletters.'';
ENd if;
new.countrycode=upper(new.countrycode);
new.countryname=initcap(new.countryname);
end;'
language 'internal';
</snip>but I get the following :
<snip>
ERROR: ProcedureCreate: there is no builtin function named
"BEGIN
Show quoted text
if new.countrycode !~'^[A-Za-z][A-Za-z]$'
then raise exception 'ISO-code are excepted to be uppercase
letters.';
ENd if;
new.countrycode=upper(new.countrycode);
new.countryname=initcap(new.countryname);
end;"
</snip>Please note that I logging as postgres
tx for your help,
thomas,
--
Thomas SMETS
rue J. Wytsmanstraat 62
1050 Bruxelles
yahoo-id : smetsthomas---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
3) Oops not a rule but a check
ALTER TABLE "your_table"
ADD CONSTRAINT your_table_check CHECK (countrycode ~ '[A-Z]*');
----- Original Message -----
From: "PGMailList" <pgmail@pgexplorer.com>
To: <tsmets@brutele.be>; <pgsql-general@postgresql.org>
Sent: Monday, March 25, 2002 5:06 PM
Subject: Re: [GENERAL] Could someone tell me what is wrong withthis function
1) as 'BEGIN
as '
BEGINPut BEGIN in next line
2) if new.countrycode !~''^[A-Za-z][A-Za-z]$''
wont check for only Uppercase Letters try
''^[A-Z]*''3) You can also use a Rule on the field, but its nice to use a trigger if
you require a better error to be reportedhttp://www.pgexplorer.com
GUI postgresql Frontend----- Original Message -----
From: <tsmets@brutele.be>
To: <pgsql-general@postgresql.org>
Sent: Monday, March 25, 2002 4:36 PM
Subject: [GENERAL] Could someone tell me what is wrong withthis functionThis is almost a copy / paste from Bruce Momjian's book :
<snip>
create function trigger_iso_code_2_uppercase()
returns opaque
as 'BEGIN
if new.countrycode !~''^[A-Za-z][A-Za-z]$''
then raise exception ''ISO-code are excepted to be uppercase
letters.'';
ENd if;
new.countrycode=upper(new.countrycode);
new.countryname=initcap(new.countryname);
end;'
language 'internal';
</snip>but I get the following :
<snip>
ERROR: ProcedureCreate: there is no builtin function named
"BEGIN
Show quoted text
if new.countrycode !~'^[A-Za-z][A-Za-z]$'
then raise exception 'ISO-code are excepted to be uppercase
letters.';
ENd if;
new.countrycode=upper(new.countrycode);
new.countryname=initcap(new.countryname);
end;"
</snip>Please note that I logging as postgres
tx for your help,
thomas,
--
Thomas SMETS
rue J. Wytsmanstraat 62
1050 Bruxelles
yahoo-id : smetsthomas---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Yep your version of postgres does not support the create or replace. It
came out in 7.2 i believe. Just use CREATE FUNCTION instead of CREATE OR
REPLACE.
Darren Ferguson
On Mon, 25 Mar 2002 tsmets@brutele.be wrote:
Show quoted text
I created the function OKay, now !
It however complains still when I try to use the :
"create or replace"
instead of the "create function"any idea ?
thomas,
--
Thomas SMETS
rue J. Wytsmanstraat 62
1050 Bruxelles
yahoo-id : smetsthomas
----- Original Message -----
From: "Darren Ferguson" <darren@crystalballinc.com>
To: <tsmets@brutele.be>
Cc: <pgsql-general@postgresql.org>
Sent: 25 March, 2002 4:15 PM
Subject: Re: [GENERAL] Could someone tell me what is wrong withthis functionYour language internal i have never heard of that.
Try langauge plpgsql this is what you seem to be writing in
Also the function says RETURNS OPAQUE but it does not return anything
Put just before END:- RETURN NEW;
Also the = should be := in plpgsql anyway. An example is below
HTH
Darren Ferguson
EXAMPLE
CREATE OR REPLACE FUNCTION trigger_iso_code_2_uppercase() RETURNS OPAQUE
AS '
BEGINIF NEW.countrycode !~ ''^[A-Za-z][A-Za-z]$'' THEN
RAISE EXCEPTION ''The exception'';
END IF;NEW.countrycode := upper(NEW.countrycode);
NEW.countryname := initcap(NEW.countryname);RETURN NEW;
END;' LANGUAGE 'plpgsql';On Mon, 25 Mar 2002 tsmets@brutele.be wrote:
This is almost a copy / paste from Bruce Momjian's book :
<snip>create function trigger_iso_code_2_uppercase() returns opaque as '
BEGIN
if new.countrycode !~''^[A-Za-z][A-Za-z]$''
then raise exception ''ISO-code are excepted to be uppercaseletters.'';
ENd if;
new.countrycode=upper(new.countrycode);
new.countryname=initcap(new.countryname);
end;'
language 'internal';
</snip>but I get the following :
<snip>
ERROR: ProcedureCreate: there is no builtin function named"BEGIN
if new.countrycode !~'^[A-Za-z][A-Za-z]$'
then raise exception 'ISO-code are excepted to be uppercase
letters.';
ENd if;
new.countrycode=upper(new.countrycode);
new.countryname=initcap(new.countryname);
end;"
</snip>Please note that I logging as postgres
tx for your help,
thomas,
--
Thomas SMETS
rue J. Wytsmanstraat 62
1050 Bruxelles
yahoo-id : smetsthomas---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?