the power of pgsql.

I hate writing database dependend code. It's a nightmare to change it when you need to move to another database system. My current situation is that I need to update millions of values in a table because we changed our data model.

So I was thinking writing a script in java or python, but this was to much work and would cost me 1 - 2 days. Instead I wrote a small plsql script which does what i want.

declare

cursor SAMP_CUR IS select "sample_id","class" from samples;
v_sample_id integer;
v_class varchar(256);

begin
open samp_cur;

loop
fetch samp_cur into v_sample_id,v_class;
exit when samp_cur%notfound;

update RESULT_LINK set "class" = v_class where "sample_id" = v_sample_id;
end loop;

close samp_cur;

end;

it took maybe 10 - 15 minutes and does what I want.

Sadly I hade to write some insane applications in plsql on my database to synchronize it over webservices with another database which Is a pain in the ass to move to a new system. I'm honestly thinking about making test with 3 database systems in future to find a new dbms.

H2
postgressql
mysql
cloudscape

H2 and cloudscape have the insane adavantage that they are based on java and I can develop java based code in the database to enhance it.