Thread: calling currval() before nextval() patch adding currval_isset()

Hi list,

attached, cvs context diff that adds currval_isset('sequence_name');

This avoids the warning messages you get when calling currval before
nextval in situations where the program flow does not allow you to
predetermine if the current session has called nexval yet.

I have for example, a general db_insert function, that needs to return
the currval value, or 0 if the table that was inserted on doesn't have a
sequence (sortof emulating mysql's last_insert_id function).

With this patch, I can now call currval_isset to determine if I need to
call currval. Previously, I just called currval, but with the result of
filling up the server log with warnings.

Kind Regards,

John Hansen

PS I picked OID 1295 for this function, as it's currently available
according to unused_oids.

On Sun, 2004-11-07 at 17:21, John Hansen wrote:
> Hi list,
>
> attached, cvs context diff that adds currval_isset('sequence_name');


> With this patch, I can now call currval_isset to determine if I need to
> call currval. Previously, I just called currval, but with the result of
> filling up the server log with warnings.

This might do what you're looking for on 8.0.

CREATE OR REPLACE FUNCTION currval_isset(text) RETURNS bigint AS '
DECLARE
  var integer;
BEGIN
  SELECT currval($1) INTO var;

  RETURN var;

EXCEPTION
  WHEN     OBJECT_NOT_IN_PREREQUISITE_STATE THEN
    RETURN 0;
END;
' LANGUAGE plpgsql;

SELECT isset('tst_seq');

John Hansen <john@geeknet.com.au> writes:
> attached, cvs context diff that adds currval_isset('sequence_name');

> This avoids the warning messages you get when calling currval before
> nextval in situations where the program flow does not allow you to
> predetermine if the current session has called nexval yet.

I would argue that a program written that way is broken by definition,
and we should not encourage programmers to write broken applications.

            regards, tom lane

> This might do what you're looking for on 8.0.
>
> CREATE OR REPLACE FUNCTION currval_isset(text) RETURNS bigint AS '
> DECLARE
>   var integer;
> BEGIN
>   SELECT currval($1) INTO var;
>
>   RETURN var;
>
> EXCEPTION
>   WHEN     OBJECT_NOT_IN_PREREQUISITE_STATE THEN
>     RETURN 0;
> END;
> ' LANGUAGE plpgsql;
>
> SELECT isset('tst_seq');
>

k, i don't actually have an 8.0 installation to play with here,. but
won't this still produce a warning?

> I would argue that a program written that way is broken by definition,
> and we should not encourage programmers to write broken applications.

Hmmm,.... is mysql's last_insert_id behaviour really that much to ask
for?

This is basically what I'm trying to emulate, to make porting mysql
applications easier. That is, to ADD postgresql support to applications,
that have backend specific code abstracted.... Many such applications
use this particular method of getting the id. the function doing the
insert only knows the tablename (by parsing the query string).

Agreed those applications should probably be completely rewritten, but
for many, including myself, that would be an effort that goes in to the
too hard basket, and thus will never see support for postgresql. Which
in my opinion is a shame.

Should there maybe instead be a .conf option that allows you to supress
the warning message given by a call to currval() before nextval() ?

currval_no_warning = true ?

... John

> Hmmm,.... is mysql's last_insert_id behaviour really that much to ask
> for?

Come to think of it,.... maybe the attached last_insert_id() patch is a
better option. Actually copying the behaviour of mysql's counterpart.

... John

> Hi list,
>
> attached, cvs context diff that adds currval_isset('sequence_name');
>

Updated patch attached, didn't think to update the docs. Thanks oicu!

Kind Regards,

John Hansen