Re: [GENERAL] temp sequence
2010/5/5 Sim Zacks s...@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?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] temp sequence
-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 g...@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- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] temp sequence
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] temp sequence
On Saturday 02 February 2008 10:39 pm, Sim Zacks wrote: PostgreSQL 8.2.4 on i386-pc-linux-gnu, compiled by GCC i386-pc-linux-gnu-gcc (GCC) 4.1.1 (Gentoo 4.1.1) I am creating a temporary sequence in a function and it seems like it is not going away after the function finishes. The front end is in MS Access 2000 and I have a single connection. When I call the function once it works, when I call it a second time, it gives me an error that the sequence already exists. When I restart the application, I can call the function again. I solved the problem by using: alter sequence seq_linenum restart with 1; The manual states: If specified, the sequence object is created only for this session, and is automatically dropped on session exit. Existing permanent sequences with the same name are not visible (in this session) while the temporary sequence exists, unless they are referenced with schema-qualified names. I thought that a function would be considered its own session, is that incorrect? The connection is the session. The function is the transaction. If you are going to maintain the connection you will need to drop the sequence inside the function after using it. Thank you Sim ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] temp sequence
On 03/02/2008 06:39, Sim Zacks wrote: I call the function once it works, when I call it a second time, it gives me an error that the sequence already exists. When I restart the application, I can call the function again. Are you by any chance connecting via ODBC with connection pooling? If so, then the pooled connections remain open, which - as someone else has explained - causes sessions to be maintained. Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] temp sequence
On Feb 3, 2008 1:39 AM, Sim Zacks [EMAIL PROTECTED] wrote:
PostgreSQL 8.2.4 on i386-pc-linux-gnu, compiled by GCC i386-pc-linux-gnu-gcc
(GCC) 4.1.1 (Gentoo 4.1.1)
I am creating a temporary sequence in a function and it seems like it is not
going away after the function finishes.
The front end is in MS Access 2000 and I have a single connection. When I call
the function once it works, when I call it a second time, it gives me an error
that the sequence already exists. When I restart the application, I can call
the
function again.
create, and use the sequence through EXECUTE
EXECUTE 'create temp sequence seq1';
EXECUTE 'select nextval(' || quote_literal('seq') || ') ';
the same apply for all kind of temp objects, in 8.3 this no longer
will be an issue
--
regards,
Jaime Casanova
Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning.
Richard Cook
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
