Обсуждение: Function xxxx() does no exist

I've already sent this request for help to the novice mail list but I <br />never get any answer. So I wonder if it
wasthe good list for that kind <br />of question and I'm publishing it here. <br />(thousand of apologies if I'm wrong
!)<p>I'm a beginner with postgres and I'm trying to understand how I can <br />workaround the FOREIGN KEY mechanism
withinPostgres. <p>I've read that it's not supported yet (before rel 7) and I can use a trigger but I have some trouble
witha Function. <p>So I created: <br />CREATE TABLE "tbl_article" ( <br />   "i_article" serial PRIMARY KEY, <br />  
"repere"text, <br />   "refmedia" int4 NOT NULL, <br />   "theme" int4 NOT NULL, <br />   "mot_cle1" int4 NOT NULL, <br
/>  "mot_cle2" int4, <br />   "mot_cle3" int4); <p>and: <br /> CREATE TABLE "tbl_theme" ( <br />    "i_theme" serial
PRIMARYKEY, <br />    "theme" text NOT NULL UNIQUE); <p>then: <br />  CREATE FUNCTION chk_itheme_proc(int4) RETURNS
int4<br />    AS 'SELECT COUNT(i_theme) FROM tbl_theme <br />    WHERE i_theme = $1;' <br />    LANGUAGE 'sql'; <p>and
Icreated a trigger: <br /> CREATE TRIGGER chk_itheme_trig BEFORE INSERT OR UPDATE ON tbl_article <br />    FOR EACH ROW
EXECUTEPROCEDURE chk_itheme_proc ('theme'); <p>but I get the message: <br />     ERROR:  CreateTrigger: function
chk_itheme_proc()does not exist <p>If I do: <br />SELECT theme FROM tbl_article <br />   WHERE chk_itheme_proc(5) = 1; 
--i_theme goes from 1 to 12 <p>theme <br />----- <br />   11 <br />(1 row) <p>If I do now: <br />SELECT theme FROM
tbl_article<br />   WHERE chk_itheme_proc(15) = 1; <br />theme <br />----- <br />(0 row) <p>So I can see that the
functionis available .... <p>I'm stuck with this issue and I wasn't able to find any help on <br />documentations.
<p>Isthere somebody who could help me ??? <p>Many thanks in advance, <br />PhiL <pre>-- 
 
==============================================================================
*  PhiL is using a Linux Kernel 2.2.14                                       *
==============================================================================</pre>  

Philippe Lefèvre <ph.l@libertysurf.fr> 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

> Philippe Lefèvre <ph.l@libertysurf.fr> 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) #