Search Postgresql Archives

plpgsql cursor syntax question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Ok guys – there is something here that I am not seeing …. Can someone please set me straight.

I am trying to create a cursor loop with an “update where current of “ statement, but my syntax is wrong.

Would one of you be able to tell me what I’m doing.

Also: adding “for update” to the curs1 cursor declaration didn’t fix it.

Thanks in advance.
mr

 

 

 

 

   Table "public.tmp_hotel_load"

   Column    |  Type   | Modifiers

-------------+---------+-----------

site        | text    |

property_id | text    |

hotel_name  | text    |

addrs1      | text    |

city        | text    |

state       | text    |

zip         | text    |

country     | text    |

latitude    | text    |

longitude   | text    |

phone       | text    |

room_count  | text    |

batch_name  | text    |

hotel_id    | integer |

id          | integer |

Indexes:

    "thl_u" UNIQUE, btree (site, property_id)

 

 

--first try it with the cursor opened – this fails

 

 

mrostron=> \i f4.sql

create or replace function tmp_htl() returns void as

$$

declare

--

    r record;

    i integer;

    curs1 cursor is select id from tmp_hotel_load;

--

begin

--

--

    i := 0;

    for r in curs1

    loop

        i := i + 1;

        update tmp_hotel_load set id = i where current of curs1;

    end loop;

--

--

    return;

--

--

end

$$ language 'plpgsql';

psql:f4.sql:25: ERROR:  syntax error at or near "$1"

LINE 1:   $1

          ^

QUERY:    $1

CONTEXT:  SQL statement in PL/PgSQL function "tmp_htl" near line 11

 

 

 

 

--now comment out the ‘for’ line, and it works….. ??

 

 

mrostron=> \i f4.sql

create or replace function tmp_htl() returns void as

$$

declare

--

    r record;

    i integer;

    curs1 cursor is select id from tmp_hotel_load;

--

begin

--

--

    i := 0;

--    for r in curs1

    loop

        i := i + 1;

        update tmp_hotel_load set id = i where current of curs1;

    end loop;

--

--

    return;

--

--

end

$$ language 'plpgsql';

CREATE FUNCTION

mrostron=>

 

 

 


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux