Обсуждение: temp sequence

I am using 8.2.14

I am trying to use a temp sequence in a function and I'm having a lot of
trouble.
I create the temp sequence and then I have to drop it at the end of the
function, because it stays alive for the whole session and not just the
function.
I want to use the nextval function in an update statement so it gives a
sequence number to each row it updates.
If I drop the sequence and recreate it I get an OID not found error.
I tried to run the update statement dynamically, but part of the update
stmt is an int array variable and it won't cast it to text.

My goal in the end is that every row that is updated will be numbered
sequentially per update.

Do you have any ideas?

Sim

2010/5/5 Sim Zacks <sim@compulab.co.il>:
> I am using 8.2.14
>
> I am trying to use a temp sequence in a function and I'm having a lot of
> trouble.
> I create the temp sequence and then I have to drop it at the end of the
> function, because it stays alive for the whole session and not just the
> function.
> I want to use the nextval function in an update statement so it gives a
> sequence number to each row it updates.
> If I drop the sequence and recreate it I get an OID not found error.
> I tried to run the update statement dynamically, but part of the update
> stmt is an int array variable and it won't cast it to text.
>
> My goal in the end is that every row that is updated will be numbered
> sequentially per update.
>
> Do you have any ideas?

Can you manipulate the sequence instead of dropping it?  i.e. select
setval('seqname',1); kind of thing?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


> I am trying to use a temp sequence in a function and I'm having a lot of trouble.
> I create the temp sequence and then I have to drop it at the end of the
> function, because it stays alive for the whole session and not just the function.
> I want to use the nextval function in an update statement so it gives a
> sequence number to each row it updates.
> If I drop the sequence and recreate it I get an OID not found error.
> I tried to run the update statement dynamically, but part of the update
> stmt is an int array variable and it won't cast it to text.

This last bit is probably solveable: can you post the code? You should be
able to cast the other side if not the array itself.

If you are inside a function, you already have built in iteration tools,
so you may not even need to use a sequence at all. Again, seeing some
code would go a long way here.

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201005050928
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkvhcwIACgkQvJuQZxSWSshkmACgtAXOeMoRaED4hKMk3SCHS4bf
cDIAoNvJnWZ+FtNUZiUXm7YCW8gksZuu
=DU+w
-----END PGP SIGNATURE-----

On 5/5/2010 2:36 AM, Sim Zacks wrote:
> I am using 8.2.14
>
> I am trying to use a temp sequence in a function and I'm having a lot of
> trouble.
> I create the temp sequence and then I have to drop it at the end of the
> function, because it stays alive for the whole session and not just the
> function.
> I want to use the nextval function in an update statement so it gives a
> sequence number to each row it updates.
> If I drop the sequence and recreate it I get an OID not found error.
> I tried to run the update statement dynamically, but part of the update
> stmt is an int array variable and it won't cast it to text.
>
> My goal in the end is that every row that is updated will be numbered
> sequentially per update.
>
> Do you have any ideas?
>
> Sim
>

Perhaps you can rewrite to use generate_series instead?

http://www.postgresql.org/docs/8.0/interactive/functions-srf.html

-Andy