Re: Raise Notice

Hi,

Basically i am dealing with triggers which addresses referential integrity,
lets take a delete restrict case, I have a parent table - "del2", child -
"del1"

when i try to delete a record from del2 it should perform following steps.

begin
perform lookup in del2
if found then
perform lookup in del1
if found then
restrict delete
else
delete from del2
end if
else
message " no record found"
end if
end

While posting i removed a few lines concerning delete restrict and just
placed raise notice query. SO in the end what i look for is a proper message
instead of "DELETE 0". Hope you get what i was looking for, by the way what
do you mean by dont post in HTML ?

Cheers
Prasad.

>From: Michael Fuhr <mike(at)fuhr(dot)org>
>Reply-To: pgsql-novice(at)postgresql(dot)org
>To: Prasad dev <esteem3300(at)hotmail(dot)com>
>CC: pgsql-novice(at)postgresql(dot)org
>Subject: Re: [NOVICE] Raise Notice
>Date: Tue, 21 Jun 2005 18:18:46 -0600
>
>[Please copy the mailing list on replies, and please don't post in HTML.
>Ordinarily I'd trim the message I'm replying to, but since it wasn't
>sent to the mailing list I'll post it here in its entirety. See my
>response below.]
>
>On Tue, Jun 21, 2005 at 10:49:35PM +0000, Prasad dev wrote:
> >
> > After posting i realised what i asked for was very unclear so here it
> > goes,
> > create table del2 (d2 int,d3 int,d4 int,primary key(d2,d3));
> > create table del1 (d1 int, d2 int,d3 int,primary key(d1),foreign key
> > (d2,d3) references del2(d2,d3));
> > insert into del2 values (1,1,1);
> > insert into del2 values (2,2,2);
> > insert into del2 values (3,3,3);
> > insert into del1 values (1,1,1);
> >
>-------------------------------------------------------------------------
> > CREATE OR REPLACE FUNCTION del_rest() RETURNS TRIGGER AS '
> > DECLARE
> > t record;
> > BEGIN
> > SELECT * INTO t FROM del2 WHERE d2=OLD.d2 and d3=OLD.d3;
> > IF NOT FOUND THEN
> > RAISE NOTICE ''No such record exits in table del2'';
> > return null;
> > END IF;
> > END;
> > 'LANGUAGE 'plpgsql';
> > CREATE TRIGGER delrest BEFORE DELETE ON del2 FOR EACH ROW EXECUTE
> > PROCEDURE del_rest();
> >
> > delete from del2 where d2=44 and d3=44;
> > Here record 44 doesn't exist in the table del2 i just want it to show
> > me the message in raise notice, i am using postgre - 7.3. When i
> > run the query this is the output without the message.
> >
> > data=> delete from del2 where d2=44 and d3=44;
> > DELETE 0
> > data=>
>
>A row-level trigger calls the trigger function once for each row
>that the operation would affect. Since there are no matching rows,
>the trigger function is never called.
>
>What problem are you trying to solve? The "DELETE 0" response
>already shows that no rows were deleted -- what purpose would the
>notice serve?
>
>--
>Michael Fuhr
>http://www.fuhr.org/~mfuhr/
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match