Re: Fwd: function for trigger

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