pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Philippe Lefèvre <ph.l@libertysurf.fr>
To: pgsql-sql@postgresql.org
Subject: Trying to use a foreign key with Postgresql
Date: Sat, 01 Apr 2000 04:32:20 -0500
Message-ID: <38E5C224.7F88EF62@libertysurf.fr> (raw)

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





Message-ID: <38E5C224.7F88EF62@libertysurf.fr>
Permalink:  ../38E5C224.7F88EF62@libertysurf.fr/
Also on:    postgresql.org/message-id/38E5C224.7F88EF62@libertysurf.fr

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: ph.l@libertysurf.fr
  Subject: Re: Trying to use a foreign key with Postgresql
  In-Reply-To: <38E5C224.7F88EF62@libertysurf.fr>
  References: <38E5C224.7F88EF62@libertysurf.fr>

* 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