Re: Cursor bug?

I have a function that reads through one table via a cursor to make updates to a different table. There is a row in this table with a column that has a null value. The following row, in that same column, is not null.

When the cursor loops through the table it seems to be treating the non null row as being null. The fetch should be inserting the new non value into the variable but doesn't appear to be.

I have attached. pg_dump of original table (knocked it down to three rows).
pg_dump of destination table
File with function
File to execute function and see result

Shouldn't the destination table have 2 rows instead of just the one.

Any help on what I might be overlooking would be appreciated.

"Mike G." <mike(at)thegodshalls(dot)com> writes:
> I have a function that reads through one table via a cursor to make updates to a different table. There is a row in this table with a column that has a null value. The following row, in that same column, is not null.
> When the cursor loops through the table it seems to be treating the non null row as being null. The fetch should be inserting the new non value into the variable but doesn't appear to be.

You did "select count(name)", which counts the number of non-null
occurrences of "name", ie 2. So the loop stops after two iterations,
and never gets to the last row.

I'd lose the count() step entirely and have the loop be driven solely by
the cursor. Actually, why use a cursor at all, as opposed to a FOR IN
SELECT loop?

regards, tom lane

thank you.

I use the cursor because I really do an update against a different table based on a value from the select in the original table. I am eagerly awaiting 8.0 and the ability to issue an Update Table1 Set Table1.col = Table2.col Using (Select y from Table2)

Mike

On Thu, Jan 13, 2005 at 04:58:06PM -0500, Tom Lane wrote:
> "Mike G." <mike(at)thegodshalls(dot)com> writes:
> > I have a function that reads through one table via a cursor to make updates to a different table. There is a row in this table with a column that has a null value. The following row, in that same column, is not null.
> > When the cursor loops through the table it seems to be treating the non null row as being null. The fetch should be inserting the new non value into the variable but doesn't appear to be.
>
> You did "select count(name)", which counts the number of non-null
> occurrences of "name", ie 2. So the loop stops after two iterations,
> and never gets to the last row.
>
> I'd lose the count() step entirely and have the loop be driven solely by
> the cursor. Actually, why use a cursor at all, as opposed to a FOR IN
> SELECT loop?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

On Thu, Jan 13, 2005 at 04:35:04PM -0600, Mike G. wrote:
> thank you.
>
> I use the cursor because I really do an update against a different
> table based on a value from the select in the original table. I am
> eagerly awaiting 8.0 and the ability to issue an Update Table1 Set
> Table1.col = Table2.col Using (Select y from Table2)

What's this USING keyword you're referring to, I can't find it in the
documentation anywhere, what does it do?

Beside, it's seems to me your entire function could be replaced by a
single query:

insert into temp_tables.delete_me
select name, file_number, question_to_change,
new_result, email
from source_table
where question_to_change = 'consultant'
and file_number is not NULL;

or something like that, i didn't check the schema. But this is probably
a cut down version. Tom's suggestion to use a FOR IN SELECT loop is a
good one.

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

On Fri, Jan 14, 2005 at 01:28:47PM +0100, Martijn van Oosterhout wrote:
> On Thu, Jan 13, 2005 at 04:35:04PM -0600, Mike G. wrote:
> > thank you.
> >
> > I use the cursor because I really do an update against a different
> > table based on a value from the select in the original table. I am
> > eagerly awaiting 8.0 and the ability to issue an Update Table1 Set
> > Table1.col = Table2.col Using (Select y from Table2)
>
> What's this USING keyword you're referring to, I can't find it in the
> documentation anywhere, what does it do?

It doesn't look like it has been added to the documentation yet. The only reference I could find to it was in the todo list (create similiar ability for delete statement).

USING allows you to add join statements to your update statement and use the data in the columns of the joined tables as input into the table the update is performed against.

http://manuals.sybase.com/onlinebooks/group-as/asg1/250e/sqlug/@Generic_BookTextView/27741

>
> Beside, it's seems to me your entire function could be replaced by a
> single query:
>
> insert into temp_tables.delete_me
> select name, file_number, question_to_change,
> new_result, email
> from source_table
> where question_to_change = 'consultant'
> and file_number is not NULL;
>
> or something like that, i didn't check the schema. But this is probably
> a cut down version. Tom's suggestion to use a FOR IN SELECT loop is a
> good one.
>
> Hope this helps,
> --
> Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> > tool for doing 5% of the work and then sitting around waiting for someone
> > else to do the other 95% so you can sue them.

On Mon, Jan 17, 2005 at 12:06:56AM -0600, Mike G. wrote:
> It doesn't look like it has been added to the documentation yet. The
> only reference I could find to it was in the todo list (create
> similiar ability for delete statement).
>
> USING allows you to add join statements to your update statement and
> use the data in the columns of the joined tables as input into the
> table the update is performed against.
>
> http://manuals.sybase.com/onlinebooks/group-as/asg1/250e/sqlug/@Generic_BookTextView/27741

This link doesn't work for me, nothing happens. Any other references?
I'm trying to understand what it allows that a FROM-clause doesn't.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.