Thread: Test to see if currval will fail?

Is there a test to see if a call to currval will fail? I would like to
call nextval one time per session inside a trigger. I am working on
other solutions, but I searched the docs and didn't find what I was
looking for.

Thanks,
Kelly


--
Kelly McDonald
Systems Analyst/Programmer
VistaShare LLC
www.vistashare.com

Kelly,

> Is there a test to see if a call to currval will fail? I would like
> to
> call nextval one time per session inside a trigger. I am working on
> other solutions, but I searched the docs and didn't find what I was
> looking for.

You could do "select last_value from <sequence name>" instead.
 However, I'm not sure that that method of getting the currval is
session-protected in the way that currval is.

-Josh Berkus

Josh,

Actually, I'm not really concerned about what the last value was or is,
but I am trying to find out if nextval has already been called within
the current session. so I can do something like:

if currval_wont_puke('sequence') then
   select currval('sequence')
else
   select nextval('sequence')

the part that I'm looking for is the currval_wont_puke() function.

I have already tried every way that I can think of to catch an exception
by currval, (and learned more since then) and I'm certain that there is
no way to catch an exception, no matter what language you write it in.

I know how to create a function like this, but I'd have to tinker with
backend source (sequence.c) to make it work. - I thought I'd see if
there is an easier way before I go do something crazy like that.

Thanks,
Kelly.



On Thu, 2002-12-05 at 17:56, Josh Berkus wrote:
> Kelly,
> You could do "select last_value from <sequence name>" instead.
>  However, I'm not sure that that method of getting the currval is
> session-protected in the way that currval is.
>
> -Josh Berkus

Kelly,

> I have already tried every way that I can think of to catch an
> exception
> by currval, (and learned more since then) and I'm certain that there
> is
> no way to catch an exception, no matter what language you write it
> in.

Unfortunately, you've hit on one of the longstanding TODOs in Postgres
... the lack of nested transactions, which forces all exceptions to be
fatal.  For that matter, the isfinite() function is pretty much useless
since it raises an exception if the value passed is not a valid date.


Still, I'm surprise that there isn't a way around fatal exceptions in C
triggers.  It seems that you should be able to do this in C.

Bruce is working on nested transactions, and we may even have them for
7.4, 2Q or 3Q 2003.   However, there's no good way around your problem
without C hacking right now.

What I personally would love to see in /contrib, as a shortcut, would
be a container C function that returns false if the containerized
function fails, thus:
IF err_container('currval(''some_sq'')') THEN
    yugo := currval('some_sq');
END IF;

... but I'm not sure of the praticality of implementing it.


-Josh

Hi!
I want to make a field in a table which is check sum of the the content of
others fields.
Is in the postgres a function in this sens?

Thanks!

On Fri, 2002-12-06 at 13:18, Kelly McDonald wrote:
> Josh,
>
> Actually, I'm not really concerned about what the last value was or is,
> but I am trying to find out if nextval has already been called within
> the current session. so I can do something like:
>
> if currval_wont_puke('sequence') then
>    select currval('sequence')
> else
>    select nextval('sequence')
>
> the part that I'm looking for is the currval_wont_puke() function.

Since it is in the _current_ session, surely you can know whether you
have called nextval() without asking the backend?  You need to set a
flag in your program when you use it or do an insert that will trigger
it from a serial column, and test that.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Go ye therefore, and teach all nations, baptizing them
      in the name of the Father, and of the Son, and of the
      Holy Ghost; Teaching them to observe all things
      whatsoever I have commanded you; and, lo, I am with
      you alway, even unto the end of the world. Amen."
            Matthew 28:19,20