Thread: Raise Notice
Hi, I have been trying to figure out why this simple query doesn't execute if no record exist ! CREATE OR REPLACE FUNCTION del_rest() RETURNS TRIGGER AS 'DECLARE t record; BEGIN SELECT * INTO t FROM del2 WHERE (d2=OLD.d2 or old.d2 is null) and (d3=OLD.d3 or old.d2 is null); IF NOT FOUND THEN RAISE NOTICE ''No such record exits in table del2''; END IF; returnnull; END;'LANGUAGE 'plpgsql'; Looking forward for suggestions. Thanks Prasad.
[Please don't post in HTML.] On Mon, Jun 20, 2005 at 12:27:41AM +0000, Prasad dev wrote: > I have been trying to figure out why this simple query doesn't > execute if no record exist ! > > CREATE OR REPLACE FUNCTION del_rest() RETURNS TRIGGER AS ' > DECLARE > t record; > BEGIN > SELECT * INTO t FROM del2 WHERE (d2=OLD.d2 or old.d2 is null) > and (d3=OLD.d3 or old.d2 is null); In the last line above, should the last part of the expression be "old.d3 is null" instead of "old.d2 is null"? Perhaps that's why the subsequent IF NOT FOUND isn't doing what you expect, if that's indeed the problem you mean. If the expression is correct as shown, then please post a minimal but complete example that somebody could load into an empty database to reproduce the problem. That is, all SQL statements to create the necessary tables, functions, and triggers, and to populate the tables with a sample data set; also the query that's not behaving the way you expect and an explanation of what you'd like it to do. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
[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/
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@fuhr.org>
>Reply-To: pgsql-novice@postgresql.org
>To: Prasad dev <esteem3300@hotmail.com>
>CC: pgsql-novice@postgresql.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
On Wed, Jun 22, 2005 at 11:49:59PM +0000, Prasad dev wrote: > > Basically i am dealing with triggers which addresses referential integrity, Are you familiar with foreign key constraints? PostgreSQL can automatically do referential integrity checks for you. http://www.postgresql.org/docs/7.3/static/tutorial-fk.html http://www.postgresql.org/docs/7.3/static/ddl-constraints.html#DDL-CONSTRAINTS-FK http://www.postgresql.org/docs/7.3/static/sql-createtable.html (Links are to the 7.3 documentation because you said you were running that version.) > SO in the end what i look for is a proper message instead of "DELETE 0". You could do this in application code, or you could wrap the delete in a function that checks how many rows were deleted and raises a notice if the count was zero. http://www.postgresql.org/docs/7.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS If you were using 7.4 or later, you could use a statement-level AFTER trigger to raise a notice if no rows were deleted. But it's still not clear what value such a notice would add. > by the way what do you mean by dont post in HTML ? Your previous messages were HTML-formatted. Some people use text-based mail readers, so they have to take extra steps to convert the HTML to something legible; also, some people's spam filters might automatically delete HTML messages. Plain text is more likely to be acceptable to some people. -- Michael Fuhr http://www.fuhr.org/~mfuhr/