Re: function return update count

Kevin Duffy <kevind0718(at)gmail(dot)com> hat am 6. Januar 2012 um 00:32 geschrieben:

> Hello:
>
> I am try to get a function to return the count of the rows updated within the
> function.
> As in the following, I wan the number of rows updated to be returned.
>
> This is a simple update, other update statements that I need to write will be
> complicated.
>
> CREATE OR REPLACE FUNCTION est_idio_return_stats_update()
>   RETURNS integer AS
>
> '
> update est_idiosyncratic_return_stats set delta_avg_60 = avg_60 -
> period_61_return,  delta_avg_last_24 = avg_last_24 - period_61_return,
> delta_avg_last_18 = avg_last_18 - period_61_return,
>          delta_avg_last_12 = avg_last_12 - period_61_return,  delta_avg_last_6
> = avg_last_06 - period_61_return ;
> '
>
>   LANGUAGE SQL ;
>
> The above returns the following:
> ERROR:  return type mismatch in function declared to return integer
> DETAIL:  Function's final statement must be SELECT or INSERT/UPDATE/DELETE
> RETURNING.
> CONTEXT:  SQL function "est_idio_return_stats_update"
>
> ********** Error **********
>
> ERROR: return type mismatch in function declared to return integer
> SQL state: 42P13
> Detail: Function's final statement must be SELECT or INSERT/UPDATE/DELETE
> RETURNING.
> Context: SQL function "est_idio_return_stats_update"
>
>
> Thanks for your kind assistance.
>
>
> KD

 I think, you can rewrite this to pl/pgsql (language, not sql) and than you can
use

GET DIAGNOSTICS integer_var = ROW_COUNT;

Andreas