Re: [SQL] cursor and for update
Hello Wiebe, Tuesday, March 28, 2006, 2:42:20 AM, you wrote: >> WC> Fist, what's the error you get? ERROR: missing ".." at end of SQL expression CONTEXT: compile of PL/pgSQL function "uporzadkuj_reguly" near line 7 WC> Second, what does "rekord" do? You don't seem to use it. Yes, i don't need it. WC> Third, can you quote the language? I always say plpgsql without the WC> quotes, but I'm not sure if using quotes is not allowed (and I'm too WC> lazy to check :)). WC> Also, why don't you have a "RETURNS VOID" function, which you can end WC> with "RETURN;" OK WC> But what you might be stuck on is that you have a column and variable WC> with the same name. Try to give the variables "tabela" and "lancunch" WC> different names. OK WC> Lastly, if you use postgres 8, you can quote your function with $$ WC> instead of ' (meaning ...$$ AS DECLARE BEGIN END;$$...), so that you WC> won't get the ugly syntax highligting that most editors will give you WC> when quoting with '. I have postgres 7.4 create or replace function uporzadkuj_reguly(text,text) RETURNS VOID AS ' DECLARE licznik integer:=1; reguly CURSOR FOR SELECT * from firewall ORDER BY id_firewall WHERE tabela=$1 and lancuch=$2 for UPDATE; BEGIN for i in reguly LOOP UPDATE firewall SET id_firewall=licznik WHERE CURRENT OF reguly; licznik:=licznik+1; END LOOP; return; END;' LANGUAGE 'plpgsql'; -- Best regards, Maciejmailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] cursor and for update
On 03/28/06 11:13, Maciej Piekielniak wrote: >create or replace function uporzadkuj_reguly(text,text) RETURNS VOID AS >' >DECLARE > licznik integer:=1; > > reguly CURSOR FOR SELECT * from firewall ORDER BY id_firewall WHERE > tabela=$1 and lancuch=$2 for UPDATE; >BEGIN > for i in reguly LOOP > UPDATE firewall SET id_firewall=licznik WHERE CURRENT OF reguly; > licznik:=licznik+1; > END LOOP; > > return; >END;' >LANGUAGE 'plpgsql'; > Looping in postgres goes differently, at least, it does in version 8. I use loops like this (there are more ways, but I think what you're doing is wrong): FOR row IN (query) LOOP END LOOP; Where "row" is a variable of type RECORD. I'm not sure how this works with CURSOR, or if postgres 7.4 has different loop constructs, but you might want to look into that, in the documentation. It has a section on loops for plpgsql. signature.asc Description: OpenPGP digital signature
[SQL] Update question
Hi all: I have a table like this: Table cost Id edge_id cost 1 30 101.4 2 30 null 3 40 500.2 4 40 null 5 45 300.7 6 45 null I want to set cost value with same edge_id by same value. The existing values are calculated by calling a function (calculate edge length). I can calculate the value by same function, but the function was slow. I wander if I can use update statement to set the second value with same edge_id. Thanks in advance for your help. Fay
Re: [SQL] Update question
Hi, Fay, Fay Du wrote: > Table cost > > Id edge_idcost > 1 30 101.4 > 2 30 null > 3 40 500.2 > 4 40 null > 545 300.7 > 645 null > I want to set cost value with same edge_id by same value. The existing > values are calculated by calling a function (calculate edge length). I > can calculate the value by same function, but the function was slow. I > wander if I can use update statement to set the second value with same > edge_id. Thanks in advance for your help. It should go like: UPDATE cost SET edge_id = (SELECT cost FROM cost innr WHERE innr.edge_id = edge_id AND innr.cost is not null) WHERE cost is null; HTH, Marku -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Update question
Markus Schaber wrote: Hi, Fay, Fay Du wrote: Table cost Id edge_idcost 1 30 101.4 2 30 null 3 40 500.2 4 40 null 545 300.7 645 null I want to set cost value with same edge_id by same value. The existing values are calculated by calling a function (calculate edge length). I can calculate the value by same function, but the function was slow. I wander if I can use update statement to set the second value with same edge_id. Thanks in advance for your help. It should go like: UPDATE cost SET edge_id = (SELECT cost FROM cost innr WHERE innr.edge_id = edge_id AND innr.cost is not null) WHERE cost is null; HTH, Marku This is ok, but this is redundant, i guess. you should put edge_id and cost into another table and use join to retrieve cost for each id. put a key on edge_id, most likely a primary (unique) key. -- Üdvözlettel, Gábriel Ákos -=E-Mail :[EMAIL PROTECTED]|Web: http://www.i-logic.hu=- -=Tel/fax:+3612367353|Mobil:+36209278894=- ---(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
Re: [SQL] Update question
Markus and Gábriel: Thank you very much for the help. Yes, the table is redundant. What I am trying to do is using pgdijstra module from postGIS which is on the top of postgreSQL. And the module asks for entire network is directional or non-directional. But my network part is directional and other part is non-directional. I made it is directional by duplicate all non-directional edges into 2 directional edges and it is where redundant come from. And the module only accept data from one table, so I have to keep all data in one table. Thanks again. Fay -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Gábriel Ákos Sent: Tuesday, March 28, 2006 12:34 PM To: pgsql-sql@postgresql.org Subject: Re: [SQL] Update question Markus Schaber wrote: > Hi, Fay, > > Fay Du wrote: >> Table cost >> >> Id edge_idcost >> 1 30 101.4 >> 2 30 null >> 3 40 500.2 >> 4 40 null >> 545 300.7 >> 645 null > >> I want to set cost value with same edge_id by same value. The existing >> values are calculated by calling a function (calculate edge length). I >> can calculate the value by same function, but the function was slow. I >> wander if I can use update statement to set the second value with same >> edge_id. Thanks in advance for your help. > > It should go like: > > UPDATE cost SET edge_id = (SELECT cost FROM cost innr WHERE innr.edge_id > = edge_id AND innr.cost is not null) WHERE cost is null; > > HTH, > Marku > This is ok, but this is redundant, i guess. you should put edge_id and cost into another table and use join to retrieve cost for each id. put a key on edge_id, most likely a primary (unique) key. -- Üdvözlettel, Gábriel Ákos -=E-Mail :[EMAIL PROTECTED]|Web: http://www.i-logic.hu=- -=Tel/fax:+3612367353|Mobil:+36209278894=- ---(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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Bitfields always atomic? Other way to store attributes?
> If I have two threads modifying the same "bit" field: > thread1=> update table set bf=bf | '01000' > thread2=> update table set bf=bf | '1' > Will this operation always be safe (e.g. result in bf='11000')? Or Won't this always result in bf='11xxx', depending on the original values of bf? TJ www.gnova.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Bitfields always atomic? Other way to store attributes?
TJ O'Donnell wrote: > > If I have two threads modifying the same "bit" field: > > thread1=> update table set bf=bf | '01000' > > thread2=> update table set bf=bf | '1' > > Will this operation always be safe (e.g. result in bf='11000')? Or > > Won't this always result in bf='11xxx', depending on the > original values of bf? Not even. Consider: thread1=> update table set bf=bf | '01000' thread3=> update table set bf=bf & '10111' thread2=> update table set bf=bf | '1' Now you get bf=10xxx. They're thread safe in that all transformations will be applied as-if serially, so no bit sets or unsets will be lost, but you can't guarantee that another client won't interfere with the results. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
