From ph.l@libertysurf.fr Thu Apr 13 19:31:06 2000 Received: from mailhub5.libertysurf.fr (mailhub5.libertysurf.fr [195.154.209.25]) by hub.org (8.9.3/8.9.3) with ESMTP id PAA52586 for ; Thu, 13 Apr 2000 15:31:06 -0400 (EDT) (envelope-from ph.l@libertysurf.fr) Received: from libertysurf.fr (ppp235-orleans1.libertysurf.fr [213.36.24.235]) by mailhub5.libertysurf.fr (8.9.3/8.9.3) with ESMTP id VAA26943 for ; Thu, 13 Apr 2000 21:34:41 +0200 (CEST) Message-ID: <38F62DAE.3B8BBBE0@libertysurf.fr> Date: Thu, 13 Apr 2000 16:27:26 -0400 From: Philippe =?iso-8859-1?Q?Lef=E8vre?= X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.14 i586) X-Accept-Language: fr-FR, fr, fr-BE, fr-CH, en-US, en-GB, en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Function xxxx() does no exist Content-Type: multipart/alternative; boundary="------------89BCD296EF0870A96125EDA7" X-Archive-Number: 200004/149 --------------89BCD296EF0870A96125EDA7 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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 (before rel 7) and I can use a trigger but I have some trouble with a Function. 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 -- ============================================================================== * PhiL is using a Linux Kernel 2.2.14 * ============================================================================== --------------89BCD296EF0870A96125EDA7 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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 (before rel 7) and I can use a trigger but I have some trouble with a Function.

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

-- 
==============================================================================
*  PhiL is using a Linux Kernel 2.2.14                                       *
==============================================================================
  --------------89BCD296EF0870A96125EDA7-- From tgl@sss.pgh.pa.us Thu Apr 13 22:40:01 2000 Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id SAA07752 for ; Thu, 13 Apr 2000 18:40:01 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id SAA12555; Thu, 13 Apr 2000 18:39:10 -0400 (EDT) To: Philippe =?iso-8859-1?Q?Lef=E8vre?= cc: pgsql-sql@postgreSQL.org Subject: Re: Function xxxx() does no exist In-reply-to: <38F62DAE.3B8BBBE0@libertysurf.fr> References: <38F62DAE.3B8BBBE0@libertysurf.fr> Comments: In-reply-to Philippe =?iso-8859-1?Q?Lef=E8vre?= message dated "Thu, 13 Apr 2000 16:27:26 -0400" Date: Thu, 13 Apr 2000 18:39:10 -0400 Message-ID: <12552.955665550@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200004/150 Philippe =?iso-8859-1?Q?Lef=E8vre?= writes: > So I created: > 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 Poorly worded error message, I'd say. You have a function chk_itheme_proc(int4), but the trigger as you show it requires a function with a signature of chk_itheme_proc(text) or something close to it. Those are different functions. The error message ought to print out the expected parameter types to remind you about that... regards, tom lane From wieck@debis.com Fri Apr 14 06:51:54 2000 Received: from orion.SAPserv.Hamburg.dsh.de (Tpolaris2.sapham.debis.de [53.2.131.8]) by hub.org (8.9.3/8.9.3) with SMTP id CAA58252 for ; Fri, 14 Apr 2000 02:51:54 -0400 (EDT) (envelope-from wieck@debis.com) Received: by orion.SAPserv.Hamburg.dsh.de for pgsql-sql@postgreSQL.org id m12fzqU-0003knC; Fri, 14 Apr 100 08:44 MET DST Message-Id: From: wieck@debis.com (Jan Wieck) Subject: Re: Function xxxx() does no exist In-Reply-To: <12552.955665550@sss.pgh.pa.us> from Tom Lane at "Apr 13, 2000 06:39:10 pm" To: Tom Lane Date: Fri, 14 Apr 2000 08:44:50 +0200 (CEST) CC: =?UNKNOWN-8BIT?Q?Philippe_Lef=E8vre?= , pgsql-sql@postgreSQL.org Reply-To: Jan Wieck X-Mailer: ELM [version 2.4ME+ PL68 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Archive-Number: 200004/155 > Philippe =?iso-8859-1?Q?Lef=E8vre?= writes: > > So I created: > > 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 > > Poorly worded error message, I'd say. You have a function > chk_itheme_proc(int4), but the trigger as you show it requires a > function with a signature of chk_itheme_proc(text) or something close > to it. Those are different functions. > > The error message ought to print out the expected parameter types to > remind you about that... No, trigger procedures in Postgres are allways defined to take no arguments and have a return type "opaque". The main error above is, that the "sql" language cannot be used to define a trigger! Use the PL/pgSQL or PL/Tcl languages instead. They're documented in the programmers manual. Version 7.0 will have PL/Perl too, but I don't know if that one can be used for trigger procs yet. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #========================================= wieck@debis.com (Jan Wieck) #