Trying to use a foreign key with Postgresql

Поиск
Список
Период
Сортировка
От Philippe Lefèvre
Тема Trying to use a foreign key with Postgresql
Дата
Msg-id 38E5C224.7F88EF62@libertysurf.fr
обсуждение исходный текст
Список 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
NOTNULL,  "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
PROCEDUREchk_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




В списке pgsql-sql по дате отправления:

Предыдущее
От: "Robert Chalmers"
Дата:
Сообщение: Can't access a table. It seems to have been corrupted hsomehow
Следующее
От: Tulassay Zsolt
Дата:
Сообщение: Re: Can't access a table. It seems to have been corrupted hsomehow