Re: Noticed a Bug with stored procedures

Hi,

I have a question in PostGreSQL, please go through below mail:

When I send update query command from front end then PostGreSql is
responding with number of rows affected. But when I send same update
query which is embedded in stored procedure (as listed below) then
PostGreSql doesn't respond with a value of -1.


CREATE OR REPLACE FUNCTION samplepro5(deptid int)
RETURNS void AS
$BODY$
UPDATE EmailLoginUsers SET LoginID = 'abc(at)sample(dot)com Where
UserCheckedMailID = $1;
$BODY$
LANGUAGE 'sql' VOLATILE
COST 100;


>From front end, I have called above listed stored procedure by using
below listed code:

objNpgSQLCommand.CommandText = "samplepro5";

objNpgSQLCommand.CommandType = CommandType.StoredProcedure;

NpgsqlParameter objParameter;

objparameter.ParameterName = "deptid";

objparameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer;

objparameter.Value = 1;

objparameter.Direction = ParameterDirection.Input;

objNpgSQLCommand.Parameters.Add(objParameter);

int numberOfAffectedRows = objNpgSQLCommand.ExecuteNonQuery();

After excuting above code, the value of numberOfAffectedRows was -1 but
database was updated with one record.

Please tell me what's wrong.

Thanks,

Sridhar G

Gudala, Sridhar (GE EntSol, Intelligent Platforms) wrote:
>
> Hi,
>
> I have a question in PostGreSQL, please go through below mail:
>
> When I send update query command from front end then PostGreSql is
> responding with number of rows affected. But when I send same update
> query which is embedded in stored procedure (as listed below) then
> PostGreSql doesn't respond with a value of -1.

What makes you think that's a bug?

First: Pg doesn't have stored procedures. It supports user-defined
*functions* that're callable within SQL and may have side effects.

Some ODBC/JDBC/Whatever drivers try to make these functions accessible
as if they were stored procedures, by executing the SQL:

SELECT function_name(args)

when invoked. IMO this is a mistake, since when Pg does get real stored
procedures, drivers won't know if you want SELECT funcname(args) or CALL
procname(args), two things with very different meanings.

Er, anyway, the thing to understand is that you're running:

SELECT samplepro5(arg)

which is a SELECT statement returning void, ie no result. The row count
of that result is clearly invalid/meaningless, and you're getting -1
when you ask for it for that reason.

The work your stored function does is a SIDE EFFECT, which should not be
reported in the row count or other measures. After all, what if you did
two different UPDATEs in your stored function? Or if you called:

SELECT samplepro5(dept.deptid) FROM departments dept;

? What row count would you expect then?

Anyway, if you want an affected row count, you could probably re-write
your function in PL/PgSQL and use GET DIAGNOSTICS to retrieve the
affected row count, then RETURN that as an integer return value from the
function. Your row count would always be 1, and the value returned would
be the "real" row count.

--
Craig Ringer