Thread: pl/pgsql question

I have created a function in pl/pgsql to modify a row before it gets put 
into the database, but it seems my modification is being ignored, and 
the unmodified row is being inserted.

I have confirmed with this RAISE EXCEPTION that my "NEW" row is modified 
properly, however it is not being stored in the db.

NEW.start_date := NEW.start_date+delta;
--      RAISE EXCEPTION ''new start date: % '',NEW.start_date;
NEW.end_date := NEW.end_date+delta;

It's probably something very obvious, but I'm mystified.

Tim


--
--      Function to enforce dependencies in the table structure
--
CREATE OR REPLACE FUNCTION projtask_insert_depend () RETURNS OPAQUE AS '
DECLARE        dependent RECORD;        delta INTEGER;
BEGIN        --        --      First make sure we start on or after end_date of tasks        --      that we depend on
     --        FOR dependent IN SELECT * FROM project_depend_vw                                WHERE 
 
project_task_id=NEW.project_task_id LOOP                --                --      See if the task we are dependent on
            --      ends after we are supposed to start                --                IF dependent.end_date >
NEW.start_dateTHEN                        delta := dependent.end_date-NEW.start_date;                --      RAISE
EXCEPTION''delta: % '',delta;                        NEW.start_date := NEW.start_date+delta;                --
RAISEEXCEPTION ''new start date: % 
 
'',NEW.start_date;                        NEW.end_date := NEW.end_date+delta;                END IF;
        END LOOP;        RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER projtask_insert_depend_trig AFTER INSERT ON project_task        FOR EACH ROW EXECUTE PROCEDURE
projtask_insert_depend();

Tim Perdue <tim@perdue.net> writes:
> I have created a function in pl/pgsql to modify a row before it gets put
^^^^^^^^^^^^^^^^^^^
> into the database, but it seems my modification is being ignored, and 
> the unmodified row is being inserted.

> CREATE TRIGGER projtask_insert_depend_trig AFTER INSERT ON project_task
^^^^^^^^^^^^
>          FOR EACH ROW EXECUTE PROCEDURE projtask_insert_depend();

I believe I see your problem ...
        regards, tom lane

--- Tim Perdue <tim@perdue.net> wrote:
> I have created a function in pl/pgsql to modify a
> row before it gets put 
> into the database, but it seems my modification is
> being ignored, and 
> the unmodified row is being inserted.
> 
> I have confirmed with this RAISE EXCEPTION that my
> "NEW" row is modified 
> properly, however it is not being stored in the db.
> 
> NEW.start_date := NEW.start_date+delta;
> --      RAISE EXCEPTION ''new start date: %
> '',NEW.start_date;
> NEW.end_date := NEW.end_date+delta;
> 
> It's probably something very obvious, but I'm
> mystified.
> 
> CREATE TRIGGER projtask_insert_depend_trig AFTER
> INSERT ON project_task
>          FOR EACH ROW EXECUTE PROCEDURE
> projtask_insert_depend();
> 
> 
 Try changing the "AFTER" to "BEFORE"

CREATE TRIGGER projtask_insert_depend_trig BEFORE...

Changes made to the "NEW" will not be reflect in the
AFTER trigger since, the row is already inserted.

__________________________________________________
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com

Ludwig Lim wrote:

>   Try changing the "AFTER" to "BEFORE"
> 
> CREATE TRIGGER projtask_insert_depend_trig BEFORE...
> 
> Changes made to the "NEW" will not be reflect in the
> AFTER trigger since, the row is already inserted.

Thanks, however this seems to present a different problem now.
        FOR dependon IN SELECT * FROM project_depend_vw                                WHERE 
project_task_id=NEW.project_task_id LOOP


That loop apparently does not find any matching rows, which would have 
been inserted just before this row was, inside the same transaction.

It was successfully finding those rows before, when the trigger was 
AFTER INSERT. If I manually select those rows after the query is 
committed, I am able to pull up the matching rows.

Tim

Tim,

> That loop apparently does not find any matching rows, which would
> have been inserted just before this row was, inside the same
> transaction.
> 
> It was successfully finding those rows before, when the trigger was
> AFTER INSERT. If I manually select those rows after the query is
> committed, I am able to pull up the matching rows.

I think that triggers are probably not a good strategy for the kind of
calculation you're doing.  I'd suggest instead a middleware module or a
"data push" function which would bundle all of the calculation logic
before calling any of the inserts.

-Josh

Josh Berkus wrote:
> Tim,
> 
> 
>>That loop apparently does not find any matching rows, which would
>>have been inserted just before this row was, inside the same
>>transaction.
>>
>>It was successfully finding those rows before, when the trigger was
>>AFTER INSERT. If I manually select those rows after the query is
>>committed, I am able to pull up the matching rows.
> 
> 
> I think that triggers are probably not a good strategy for the kind of
> calculation you're doing.  I'd suggest instead a middleware module or a
> "data push" function which would bundle all of the calculation logic
> before calling any of the inserts.

Yeah, but this is so much cooler. ;-)

Essentially this would be like recursion to push back/pull forward tasks 
which are dependent on each other. The "UPDATE" trigger I wrote is about 
5x longer.

I guess I can push this back into the PHP code and do a recusive 
function call, but that seems less sexy.

Tim