geofranzi
commented
3 years ago CREATE OR REPLACE FUNCTION public.update_variables_order()
RETURNS void
LANGUAGE 'plpgsql'
VOLATILE
PARALLEL UNSAFE
COST 100
AS $BODY$
DECLARE
datasets_id int [];
id_ int;
jsonvariablevalues_ text[];
values_json json;
length_ int;
var_id int;
values_text text;
orderno_null text[];
ordering_ int ;
BEGIN
ALTER TABLE variables DISABLE TRIGGER ALL;
datasets_id:= ARRAY(
select id
from datasets
order by id
);
for x in 1..array_upper(datasets_id,1)
LOOP
id_ = datasets_id[x];
raise notice 'dataset id to be updated = % ', id_ ;
jsonvariablevalues_:= ARRAY(
select jsonvariablevalues
from datatuples
where datasetversionref in ( select id
from datasetversions
where datasetref = id_)
order by datatuples.datasetversionref
);
IF array_length(jsonvariablevalues_, 1)>0
Then
for y in 1..array_upper(jsonvariablevalues_,1)
LOOP
values_text = jsonvariablevalues_[y];
if Length(values_text)>0
Then
values_text = REPLACE( values_text, '"v": ""' ,'"v": " "');
values_text = REPLACE( values_text, '""' ,'"');
values_text = REPLACE( values_text, '\a', '' );
values_text = REPLACE( values_text, ',"' ,'');
values_text = REPLACE( values_text, E'\n' ,'');
values_text = REPLACE( values_text, '
-
' ,'-');
values_text = REPLACE( values_text, '"A"' ,'A');
values_text = REPLACE( values_text, '"v": A' ,'"v": "A');
values_text = REPLACE( values_text, '"v": "A}' ,'"v": "A"}');
--raise notice 'values text : % ', values_text ;
values_json = values_text::json;
length_ = JSON_ARRAY_LENGTH(values_json);
--raise notice 'values json : % ', values_json ;
--raise notice 'variables length : % ', length_ ;
ordering_ = 0;
for z in 0..length_-1
loop
ordering_ = ordering_ +1 ;
var_id = values_json->z->>'vid';
orderno_null:= ARRAY(
select id
from variables
where (orderno = 0 and id = var_id)
order by id
);
--raise notice 'orderno_null : % ', orderno_null ;
if array_length(orderno_null, 1) > 0
then
raise notice 'id going to be updated in variables : % ', var_id::int ;
raise notice ' order % ', ordering_ ;
UPDATE ONLY variables
SET orderno = ordering_::int
WHERE id = var_id::bigint ;
end if;
end loop;
end if;
END LOOP;
END IF;
end loop;
ALTER TABLE variables ENABLE TRIGGER ALL;
--COMMIT;
END
$BODY$;
update_displayname_idiv.txt update_titel_desc_idiv.txt update_idiv.txt update_json_idiv.txt
[x] set entities
[x] delete double persons (parties)
[x] add entityreference to operations
[x] set operations_id_seq to 66
[x] run update script
[x] update values
[ ] correct wrong set order
[x]
update entitypermissions set entityref = 2 where id in (select id from datasets where metadatastructureref in (select id from metadatastructures where extra::text ilike '%<entity name="Publication"%'))