From: | Philippe Lefèvre <ph(dot)l(at)libertysurf(dot)fr> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Trying to use a foreign key with Postgresql |
Date: | 2000-04-01 09:32:20 |
Message-ID: | 38E5C224.7F88EF62@libertysurf.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
I've already sent this request for help to the novice mail list but I
never get any answer. So I wonder if it was the good list for that kind
of question and I'm publishing it here.
(thousand of apologies if I'm wrong !)
I'm a beginner with postgres and I'm trying to understand how I can
workaround the FOREIGN KEY mechanism within Postgres.
I've read that it's not supported yet and we can use a trigger.
So I created:
CREATE TABLE "tbl_article" (
"i_article" serial PRIMARY KEY,
"repere" text,
"refmedia" int4 NOT NULL,
"theme" int4 NOT NULL,
"mot_cle1" int4 NOT NULL,
"mot_cle2" int4,
"mot_cle3" int4);
and:
CREATE TABLE "tbl_theme" (
"i_theme" serial PRIMARY KEY,
"theme" text NOT NULL UNIQUE);
then:
CREATE FUNCTION chk_itheme_proc(int4) RETURNS int4
AS 'SELECT COUNT(i_theme) FROM tbl_theme
WHERE i_theme = $1;'
LANGUAGE 'sql';
and I created a trigger:
CREATE TRIGGER chk_itheme_trig BEFORE INSERT OR UPDATE ON tbl_article
FOR EACH ROW EXECUTE PROCEDURE chk_itheme_proc ('theme');
but I get the message:
ERROR: CreateTrigger: function chk_itheme_proc() does not exist
If I do:
SELECT theme FROM tbl_article
WHERE chk_itheme_proc(5) = 1; -- i_theme goes from 1 to 12
theme
-----
11
(1 row)
If I do now:
SELECT theme FROM tbl_article
WHERE chk_itheme_proc(15) = 1;
theme
-----
(0 row)
So I can see that the function is available ....
I'm stuck with this issue and I wasn't able to find any help on
documentations.
Is there somebody who could help me ???
Many thanks in advance,
PhiL
From | Date | Subject | |
---|---|---|---|
Next Message | Tulassay Zsolt | 2000-04-01 11:00:35 | Re: Can't access a table. It seems to have been corrupted hsomehow |
Previous Message | Robert Chalmers | 2000-04-01 08:49:33 | Can't access a table. It seems to have been corrupted hsomehow |