Re: function return update count - Mailing list pgsql-general

From Misa Simic
Subject Re: function return update count
Date
Msg-id 186471732196732483@iso-8859-1msgid
Whole thread Raw
In response to function return update count  (Kevin Duffy <kevind0718@gmail.com>)
List pgsql-general
Sorry,

Option 1) is wrong answer...  :)

Option 2 should work....


Sent from my Windows Phone

From: Misa Simic
Sent: 06/01/2012 08:34
To: Kevin Duffy; pgsql-general@postgresql.org
Subject: RE: [GENERAL] function return update count

You could try:

1) return UPDATE table

OR

2) use plpsql function instead of SQL
UPDATE table
GET DIAGNOSTICS <variable> = ROW_COUNT
RETURN <variable>

Kind regards,
Misa
Sent from my Windows Phone

From: Kevin Duffy
Sent: 06/01/2012 06:21
To: pgsql-general@postgresql.org
Subject: [GENERAL] function return update count

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

pgsql-general by date:

Previous
From: Andreas Kretschmer
Date:
Subject: Re: function return update count
Next
From: ChoonSoo Park
Date:
Subject: How to code lo_creat & lo_write & lo_read in non-blocking mode