Postgres Pro
Downloads
Home   >   mailing lists

Thread: Fwd: function for trigger

Fwd: function for trigger

From
avpro avpro
Date:
Hi,

I have this function

CREATE OR REPLACE FUNCTION sumlast_func()
RETURNS trigger AS
begin
CASE
WHEN idcar = (select idcar FROM selectedcar) THEN
JOIN selectedcar ON selectedcar.idcar = actuals.idcar)
update actuals
set
comb = comb + (select combor from combor_view
where idlog = (select max(idlog) from combor_view)),
END CASE;
return new;
end

I'm not sure where to create the boolean condition; the options in the function above or in the trigger below

CREATE TRIGGER update_actuals_tg
  AFTER INSERT
  ON combor
  FOR EACH ROW
  EXECUTE PROCEDURE sumlast_func();

and then, is the boolean condition wrong?
thanks for any tips


Re: Fwd: function for trigger

From
Adrian Klaver
Date:
On 11/07/2014 07:07 AM, avpro avpro wrote:
> Hi,
>
> I have this function
>
> CREATE OR REPLACE FUNCTION sumlast_func()
> RETURNS trigger AS
> begin
> CASE
> WHEN idcar = (select idcar FROM selectedcar) THEN
> JOIN selectedcar ON selectedcar.idcar = actuals.idcar)
> update actuals
> set
> comb = comb + (select combor from combor_view
> where idlog = (select max(idlog) from combor_view)),
> END CASE;
> return new;
> end
>
> I'm not sure where to create the boolean condition; the options in the
> function above or in the trigger below
>
> CREATE TRIGGER update_actuals_tg
>    AFTER INSERT
>    ON combor
>    FOR EACH ROW
>    EXECUTE PROCEDURE sumlast_func();
>
> and then, is the boolean condition wrong?
> thanks for any tips

The function you show above will not run for several reasons:

1) I do not see a language specified. I am assuming plpgsql.

2) The the statements in your CASE are not ended with ;

3) end does not have a ;

I am also not sure that you can use SQL statements in the WHEN. Then
there is this:

THEN
JOIN selectedcar ON selectedcar.idcar = actuals.idcar)
update actuals
set
comb = comb + (select combor from combor_view
where idlog = (select max(idlog) from combor_view)),

I am not quite sure what is going on there, in particular where is is
actuals.idcar coming from?

I would suggest looking at the plpgsql trigger docs:

http://www.postgresql.org/docs/9.3/interactive/plpgsql-trigger.html

I believe you want to declare some variables and SELECT INTO them and
then use those to do the boolean testing. FYI plpgsql SELECT INTO is not
the same as the SQL version. For more information see:

http://www.postgresql.org/docs/9.3/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW

>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Fwd: function for trigger

From
David G Johnston
Date:
avpro avpro wrote
> Hi,
>
> I have this function
>
> CREATE OR REPLACE FUNCTION sumlast_func()
> RETURNS trigger AS
> begin
> CASE
> WHEN idcar = (select idcar FROM selectedcar) THEN
> JOIN selectedcar ON selectedcar.idcar = actuals.idcar)
> update actuals
> set
> comb = comb + (select combor from combor_view
> where idlog = (select max(idlog) from combor_view)),
> END CASE;
> return new;
> end
>
> I'm not sure where to create the boolean condition; the options in the
> function above or in the trigger below
>
> CREATE TRIGGER update_actuals_tg
>   AFTER INSERT
>   ON combor
>   FOR EACH ROW
>   EXECUTE PROCEDURE sumlast_func();
>
> and then, is the boolean condition wrong?
> thanks for any tips

What you wrote makes no sense.

David J.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/Fwd-function-for-trigger-tp5826063p5826066.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.