Thread: function return update count

function return update count

From

Kevin Duffy

Date:

06 January 2012, 04:20:53

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

Re: function return update count

From

Misa Simic

Date:

06 January 2012, 06:34:46

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

Re: function return update count

From

Misa Simic

Date:

06 January 2012, 06:37:41

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

Re: function return update count

From

Misa Simic

Date:

06 January 2012, 14:34:23

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

Re: function return update count

From

Andreas Kretschmer

Date:

06 January 2012, 14:36:49

Kevin Duffy <kevind0718@gmail.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

Re: function return update count

From

Misa Simic

Date:

06 January 2012, 14:37:25

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

Re: function return update count

From

David Johnston

Date:

06 January 2012, 15:45:45

In 9.1 you could use and updatable CTE and in the main query perform and return a count. I would think plpgsql would be the better option though.

On Jan 6, 2012, at 2:29, Andreas Kretschmer <andreas@a-kretschmer.de> wrote:

Kevin Duffy <kevind0718@gmail.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  
  
  
  
 

Re: function return update count

From

Richard Huxton

Date:

06 January 2012, 16:37:19

On 06/01/12 16:33, David Johnston wrote:
> In 9.1 you could use and updatable CTE and in the main query perform
> and return a count.  I would think plpgsql would be the better option
> though.

For the SQL option, it would be this (9.1 only though - I think David's
right there).

CREATE FUNCTION f1() RETURNS int AS $$
     WITH rows AS (
         UPDATE t1 SET ... WHERE ... RETURNING 1
     )
     SELECT count(*)::int FROM rows
$$ LANGUAGE SQL;

Unfortunately you can't do UPDATE ... RETURNING count(*) directly so you
need to go through this business with the CTE (WITH clause).

Oh - the cast to int is because count() returns bigint.

--
   Richard Huxton
   Archonet Ltd