Thread: Report of some problem under PL/PGSQL 7.4.7 & 8.0.1
Hi everyone, I made a tetris under pl/pgsql and i encounter some problem with this non commun use of pl/pgsql. For each problem, i didn't see information about them, so my report : - Array problem (7.4.7 & 8.0.1) : I got a lot of problem with using array, like i saw under the ML, multidimensional array are not friendly to use so i used 1 dimension array but i needed to put data at point (x,y). The problem is how the array is created and how i can put data into it. I use this little test function : ------------------------------ CREATE OR REPLACE FUNCTION test_array() RETURNS integer AS ' DECLARE a integer; b integer; c integer; array varchar[]; BEGIN array := ''{}''; a := 1; WHILE a < 17 LOOP b := 1; WHILE b < 17 LOOP c:= a + b * 16; RAISE NOTICE ''%'', c; array[c] := ''&''; b := b + 1; END LOOP; a := a + 1; END LOOP; return 0; END; ' LANGUAGE plpgsql; ---------------------------- Error message : ---------------------------- tetris=# select test_array(); NOTICE: 17 NOTICE: 33 ERROR: invalid array subscripts CONTEXT: PL/pgSQL function "test_array" line 16 at assignment ---------------------------- To correct this error message, i need to make 2 init, the first with "array := ''{}'';" and a second one by insert data into it with incremential pointer : a := 0; WHILE a <= 999 LOOP array[a] := '' ''; END LOOP; Then my function test_array() work properly. But i dont understand why PL allow me to assign 2 times data into my array with "random" pointer and not the 3rd times. BTW, i dont really understand why i need to make "array := ''{}'';", some people who test it from pgsqlfr-general ML try without making this init and get not problem with "random" pointer. But array was <<null>>. - Problem with table refresh For my game, i need to detect keystroke, so i made an infinit loop waiting for key to be press. I have two case for 7.4.7 & 8.0.1, under 8.0.1 it seems to work properly. Under 7.4.7, i need to make : WHILE a = 0 LOOP for rGetkey IN SELECT * FROM getkey LOOP a := 1; END LOOP; select into a count(key) FROM getkey; END LOOP; Or i will allways have "a = 0" (maybe i miss something). But under 8.0.1, its ok with : WHILE a = 0 LOOP select into a count(key) FROM getkey; END LOOP; - Some features : I was surprise to see that i cant put any "'" after --. I thought it was detect as comment, so all after it on the line will not be compile Same as table refresh, i didnt understand why the function now() (7.4.7 & 8.0.1) dont refresh it self under a PL function and needed to use timeofday(); The test function : ------------------------------------------ CREATE OR REPLACE FUNCTION test_now() RETURNS varchar AS ' DECLARE a timestamp; b integer; BEGIN b := 1; while b <> 0 LOOP select into a now(); RAISE NOTICE ''%'', a; END LOOP; return ''a''; END; ' LANGUAGE plpgsql; ------------------------------------------ That's all, Regards,
On Thu, 24 Feb 2005, Froggy / Froggy Corp. wrote: > tetris=# select test_array(); > NOTICE: 17 > NOTICE: 33 > ERROR: invalid array subscripts > CONTEXT: PL/pgSQL function "test_array" line 16 at assignment > ---------------------------- > > To correct this error message, i need to make 2 init, the first with > "array := ''{}'';" and a second one by insert data into it with > incremential pointer : > > a := 0; > WHILE a <= 999 LOOP > array[a] := '' ''; > END LOOP; > > Then my function test_array() work properly. > > But i dont understand why PL allow me to assign 2 times data into my > array with "random" pointer and not the 3rd times. It appears to allow you to assign once (the notice came before the error so the 33 is where it fails). In the first one there's no array bounds set yet and so the index used generates the initial array bounds, but it looks like after that you can only extend an index after that by indexing to adjacent or overlapping areas, not distinct ones. From the docs: "A stored array value can be enlarged by assigning to an element adjacent to those already present, or by assigning to a slice that is adjacent to or overlaps the data already present." > BTW, i dont really understand why i need to make "array := ''{}'';", > some people who test it from pgsqlfr-general ML try without making this > init and get not problem with "random" pointer. But array was <<null>>. Because array if unset is NULL and PostgreSQL treats indexing into a NULL array similarly to adding NULL integers or concatenating NULL strings and thus returns a NULL output. It's fairly annoying, but having it do something else would be marginally inconsistent. > - Problem with table refresh > > For my game, i need to detect keystroke, so i made an infinit loop > waiting for key to be press. > I have two case for 7.4.7 & 8.0.1, under 8.0.1 it seems to work > properly. > > Under 7.4.7, i need to make : > WHILE a = 0 LOOP > for rGetkey IN SELECT * FROM getkey LOOP > a := 1; > END LOOP; > select into a count(key) FROM getkey; > END LOOP; > > Or i will allways have "a = 0" (maybe i miss something). > > But under 8.0.1, its ok with : > WHILE a = 0 LOOP > select into a count(key) FROM getkey; > END LOOP; Between 7.4 and 8.0 there were alot of changes into what visibility statements inside functions have, so it doesn't surprise me that they'd be different. I think the 7.4 case was seeing the same snapshot with the simple loop while 8.0 would for volatile functions get new snapshots between statements and for static/immutable functions it won't get a new snapshot even with the for loop (I'm not sure why that works in 7.4). > - Some features : > I was surprise to see that i cant put any "'" after --. I thought it was > detect as comment, so all after it on the line will not be compile The main parser won't consider the -- as a comment beginning because it's in a literal string, but the function language's parser won't get it until after the main parser tries to figure out the function end. I'm not sure how this could be made better without the main parser having knowledge about each pl language. From 8.0 onwards it's probably best to use the dollar-quoting to minimize random strangeness with single quotes. > Same as table refresh, i didnt understand why the function now() (7.4.7 > & 8.0.1) dont refresh it self under a PL function and needed to use > timeofday(); Because now() is defined as transaction start time which doesn't change within a transaction.