FAQ
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;

return null;

END;

'LANGUAGE 'plpgsql'; Looking forward for suggestions. Thanks Prasad.

Search Discussions

  • Michael Fuhr at Jun 20, 2005 at 3:15 am
    [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 at Jun 22, 2005 at 12:20 am
    [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?
  • Prasad dev at Jun 22, 2005 at 11:50 pm
    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
  • Michael Fuhr at Jun 23, 2005 at 1:38 am

    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.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJun 20, '05 at 12:27a
activeJun 23, '05 at 1:38a
posts5
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Michael Fuhr: 3 posts Prasad dev: 2 posts

People

Translate

site design / logo © 2022 Grokbase