pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Function xxxx() does no exist
3+ messages / 3 participants
[nested] [flat]

* Function xxxx() does no exist
@ 2000-04-13 20:27  Philippe Lefèvre <ph.l@libertysurf.fr>
  0 siblings, 1 reply; 3+ messages in thread

From: Philippe Lefèvre @ 2000-04-13 20:27 UTC (permalink / raw)
  To: 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 (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                                       *
==============================================================================

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Function xxxx() does no exist
@ 2000-04-13 22:39  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Philippe Lefèvre <ph.l@libertysurf.fr>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2000-04-13 22:39 UTC (permalink / raw)
  To: Philippe Lefèvre <ph.l@libertysurf.fr>; +Cc: pgsql-sql

Philippe =?iso-8859-1?Q?Lef=E8vre?= <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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Function xxxx() does no exist
@ 2000-04-14 06:44  Jan Wieck <wieck@debis.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 3+ messages in thread

From: wieck@debis.com @ 2000-04-14 06:44 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Philippe Lefèvre <ph.l@libertysurf.fr>; pgsql-sql

> Philippe =?iso-8859-1?Q?Lef=E8vre?= <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) #





^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2000-04-14 06:44 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-04-13 20:27 Function xxxx() does no exist Philippe Lefèvre <ph.l@libertysurf.fr>
2000-04-13 22:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-04-14 06:44   ` Jan Wieck <wieck@debis.com>

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