Обсуждение: trigger and plpgsq help needed

Поиск
Список
Период
Сортировка

trigger and plpgsq help needed

От
"SUNDAY A. OLUTAYO"
Дата:
Dear all,

I have issue with the red portion function below;

This IF last_id IS NULL THEN suppose to test if last_id is NULL, that is the select query did not found it
then execute the rest of the red sql but it always fail to insert the NEW.amount into amount, every other things fine.

Kindly help out.



CREATE OR REPLACE FUNCTION invoice_trigger_func() RETURNS trigger AS
$$

DECLARE
last_id integer;
current_balance_id integer;
oldbalance numeric(10,2);
newbalance numeric(10,2);

BEGIN

    SELECT lastbal_id INTO last_id FROM patient
        WHERE patient_id = NEW.patient_id;
        IF last_id IS NULL THEN
           INSERT INTO balance (invoice_id, patient_id, amount)
            VALUES (NEW.invoice_id, NEW.patient_id, NEW.amount);
           UPDATE patient SET lastbal_id = (SELECT currval('balance_balance_id_seq'))
            WHERE patient_id = NEW.patient_id;

            ELSE
           SELECT amount INTO oldbalance FROM  balance
            WHERE balance_id = last_id;
           INSERT INTO balance (invoice_id, patient_id, amount)
            VALUES (NEW.invoice_id, NEW.patient_id, oldbalance + NEW.amount);
           UPDATE patient SET lastbal_id = (SELECT currval('balance_balance_id_seq'))
            WHERE patient_id = NEW.patient_id;
            END IF;
    RETURN NEW;
    
END;
$$ LANGUAGE plpgsql;


Thanks,

Sunday Olutayo


Re: trigger and plpgsq help needed

От
Chris Ernst
Дата:
On 09/26/2012 10:42 AM, SUNDAY A. OLUTAYO wrote:
> This IF last_id IS NULL THEN suppose to test if last_id is NULL, that is
> the select query did not found it
> then execute the rest of the red sql but it always fail to insert the
> *NEW.amount *into*amount*, every other things fine.

I believe what you're looking for is "IF NOT FOUND THEN" instead of "IF
last_id IS NULL THEN"

Cheers!

    - Chris



Re: trigger and plpgsq help needed

От
Rob Richardson
Дата:

Why not use IF FOUND?

 

RobR

 

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of SUNDAY A. OLUTAYO
Sent: Wednesday, September 26, 2012 12:43 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] trigger and plpgsq help needed

 

Dear all,

I have issue with the red portion function below;

This
IF last_id IS NULL THEN suppose to test if last_id is NULL, that is the select query did not found it
then execute the rest of the red sql but it always fail to insert the NEW.amount into amount, every other things fine.

Kindly help out.

Re: trigger and plpgsq help needed

От
Adrian Klaver
Дата:
On 09/26/2012 09:42 AM, SUNDAY A. OLUTAYO wrote:
> Dear all,
>
> I have issue with the red portion function below;
>
> This IF last_id IS NULL THEN suppose to test if last_id is NULL, that is
> the select query did not found it
> then execute the rest of the red sql but it always fail to insert the
> *NEW.amount *into*amount*, every other things fine.

Is there a NEW.amount?

>
> Kindly help out.
>
>
>
>
> Thanks,
>
> Sunday Olutayo
>
>


--
Adrian Klaver
adrian.klaver@gmail.com


Re: trigger and plpgsq help needed

От
"SUNDAY A. OLUTAYO"
Дата:
RobR,

I have tried it 'IF FOUND' but not getting the desired result

Thanks,

Sunday Olutayo



From: "Rob Richardson" <RDRichardson@rad-con.com>
To: pgsql-general@postgresql.org
Sent: Wednesday, September 26, 2012 7:14:46 PM
Subject: Re: [GENERAL] trigger and plpgsq help needed

Why not use IF FOUND?

 

RobR

 

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of SUNDAY A. OLUTAYO
Sent: Wednesday, September 26, 2012 12:43 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] trigger and plpgsq help needed

 

Dear all,

I have issue with the red portion function below;

This
IF last_id IS NULL THEN suppose to test if last_id is NULL, that is the select query did not found it
then execute the rest of the red sql but it always fail to insert the NEW.amount into amount, every other things fine.

Kindly help out.


Re: trigger and plpgsq help needed

От
"SUNDAY A. OLUTAYO"
Дата:
I have tried "IF NOT FOUND THEN" but not getting the desired result

Thanks,

Sunday Olutayo
Sadeeb Technologies Ltd
7 Mayegun Street, Ojo
Lagos State, Nigeria.

Tel: +234 1 7404524
D/L: +234 1 8169922
Cell: +234 8054600338, 234 8096441688
Fax: +234 8054895664
mail: olutayo@sadeeb.com




----- Original Message -----
From: "Chris Ernst" <cernst@zvelo.com>
To: pgsql-general@postgresql.org
Sent: Wednesday, September 26, 2012 7:10:03 PM
Subject: Re: [GENERAL] trigger and plpgsq help needed

On 09/26/2012 10:42 AM, SUNDAY A. OLUTAYO wrote:
> This IF last_id IS NULL THEN suppose to test if last_id is NULL, that is
> the select query did not found it
> then execute the rest of the red sql but it always fail to insert the
> *NEW.amount *into*amount*, every other things fine.

I believe what you're looking for is "IF NOT FOUND THEN" instead of "IF
last_id IS NULL THEN"

Cheers!

    - Chris



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: trigger and plpgsq help needed

От
"SUNDAY A. OLUTAYO"
Дата:
Thanks you all,

"IF last_id IS NULL THEN" eventually work

Thanks,

Sunday Olutayo
Sadeeb Technologies Ltd



From: "SUNDAY A. OLUTAYO" <olutayo@sadeeb.com>
To: "Rob Richardson" <RDRichardson@rad-con.com>
Cc: pgsql-general@postgresql.org
Sent: Thursday, September 27, 2012 2:04:46 PM
Subject: Re: [GENERAL] trigger and plpgsq help needed

p { margin: 0; }
RobR,

I have tried it 'IF FOUND' but not getting the desired result

Thanks,

Sunday Olutayo



From: "Rob Richardson" <RDRichardson@rad-con.com>
To: pgsql-general@postgresql.org
Sent: Wednesday, September 26, 2012 7:14:46 PM
Subject: Re: [GENERAL] trigger and plpgsq help needed

Why not use IF FOUND?

 

RobR

 

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of SUNDAY A. OLUTAYO
Sent: Wednesday, September 26, 2012 12:43 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] trigger and plpgsq help needed

 

Dear all,

I have issue with the red portion function below;

This
IF last_id IS NULL THEN suppose to test if last_id is NULL, that is the select query did not found it
then execute the rest of the red sql but it always fail to insert the NEW.amount into amount, every other things fine.

Kindly help out.