Ticket #1176: seq.sql

BEGIN;
CREATE OR REPLACE FUNCTION ome_nextval(seq VARCHAR) RETURNS INT8 AS '
DECLARE
    nv   int8;
    sql  varchar;
BEGIN

      sql:= ''SELECT next_val FROM seq_table WHERE sequence_name = '''''' || seq || '''''' FOR UPDATE OF seq_table'';
      EXECUTE sql INTO nv;

      sql:= ''UPDATE seq_table SET next_val = next_val + 1 WHERE sequence_name = '''''' || seq || '''''' '';
      EXECUTE sql;

      RETURN nv;
END;' LANGUAGE plpgsql;
CREATE TABLE seq_table (sequence_name VARCHAR(255) PRIMARY KEY, next_val int8);
INSERT INTO seq_table SELECT 'seq_acquisitionmode', id + 1 FROM acquisitionmode ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_annotation', id + 1 FROM annotation ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_annotationannotationlink', id + 1 FROM annotationannotationlink ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_arctype', id + 1 FROM arctype ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_binning', id + 1 FROM binning ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_channel', id + 1 FROM channel ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_channelannotationlink', id + 1 FROM channelannotationlink ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_channelbinding', id + 1 FROM channelbinding ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_codomainmapcontext', id + 1 FROM codomainmapcontext ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_contrastmethod', id + 1 FROM contrastmethod ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_correction', id + 1 FROM correction ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_dataset', id + 1 FROM dataset ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_datasetannotationlink', id + 1 FROM datasetannotationlink ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_datasetimagelink', id + 1 FROM datasetimagelink ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_dbpatch', id + 1 FROM dbpatch ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_detector', id + 1 FROM detector ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_detectorsettings', id + 1 FROM detectorsettings ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_detectortype', id + 1 FROM detectortype ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_dichroic', id + 1 FROM dichroic ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_dimensionorder', id + 1 FROM dimensionorder ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_event', id + 1 FROM event ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_eventlog', id + 1 FROM eventlog ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_eventtype', id + 1 FROM eventtype ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_experiment', id + 1 FROM experiment ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_experimenter', id + 1 FROM experimenter ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_experimenterannotationlink', id + 1 FROM experimenterannotationlink ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_experimentergroup', id + 1 FROM experimentergroup ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_experimentergroupannotationlink', id + 1 FROM experimentergroupannotationlink ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_experimenttype', id + 1 FROM experimenttype ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_externalinfo', id + 1 FROM externalinfo ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_family', id + 1 FROM family ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_filamenttype', id + 1 FROM filamenttype ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_filter', id + 1 FROM filter ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_filterset', id + 1 FROM filterset ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_filtertype', id + 1 FROM filtertype ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_format', id + 1 FROM format ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_groupexperimentermap', id + 1 FROM groupexperimentermap ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_illumination', id + 1 FROM illumination ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_image', id + 1 FROM image ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_imageannotationlink', id + 1 FROM imageannotationlink ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_imagingenvironment', id + 1 FROM imagingenvironment ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_immersion', id + 1 FROM immersion ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_instrument', id + 1 FROM instrument ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_job', id + 1 FROM job ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_joboriginalfilelink', id + 1 FROM joboriginalfilelink ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_jobstatus', id + 1 FROM jobstatus ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_lasermedium', id + 1 FROM lasermedium ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_lasertype', id + 1 FROM lasertype ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_lightsettings', id + 1 FROM lightsettings ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_lightsource', id + 1 FROM lightsource ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_link', id + 1 FROM link ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_logicalchannel', id + 1 FROM logicalchannel ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_medium', id + 1 FROM medium ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_microbeammanipulation', id + 1 FROM microbeammanipulation ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_microbeammanipulationtype', id + 1 FROM microbeammanipulationtype ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_microscope', id + 1 FROM microscope ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_microscopetype', id + 1 FROM microscopetype ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_node', id + 1 FROM node ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_nodeannotationlink', id + 1 FROM nodeannotationlink ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_objective', id + 1 FROM objective ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_objectivesettings', id + 1 FROM objectivesettings ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_originalfile', id + 1 FROM originalfile ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_originalfileannotationlink', id + 1 FROM originalfileannotationlink ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_otf', id + 1 FROM otf ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_photometricinterpretation', id + 1 FROM photometricinterpretation ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_pixels', id + 1 FROM pixels ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_pixelsannotationlink', id + 1 FROM pixelsannotationlink ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_pixelsoriginalfilemap', id + 1 FROM pixelsoriginalfilemap ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_pixelstype', id + 1 FROM pixelstype ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_planeinfo', id + 1 FROM planeinfo ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_planeinfoannotationlink', id + 1 FROM planeinfoannotationlink ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_plate', id + 1 FROM plate ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_plateannotationlink', id + 1 FROM plateannotationlink ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_project', id + 1 FROM project ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_projectannotationlink', id + 1 FROM projectannotationlink ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_projectdatasetlink', id + 1 FROM projectdatasetlink ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_pulse', id + 1 FROM pulse ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_quantumdef', id + 1 FROM quantumdef ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_reagent', id + 1 FROM reagent ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_reagentannotationlink', id + 1 FROM reagentannotationlink ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_renderingdef', id + 1 FROM renderingdef ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_renderingmodel', id + 1 FROM renderingmodel ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_screen', id + 1 FROM screen ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_screenacquisition', id + 1 FROM screenacquisition ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_screenacquisitionannotationlink', id + 1 FROM screenacquisitionannotationlink ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_screenacquisitionwellsamplelink', id + 1 FROM screenacquisitionwellsamplelink ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_screenannotationlink', id + 1 FROM screenannotationlink ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_screenplatelink', id + 1 FROM screenplatelink ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_session', id + 1 FROM session ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_sessionannotationlink', id + 1 FROM sessionannotationlink ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_stagelabel', id + 1 FROM stagelabel ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_statsinfo', id + 1 FROM statsinfo ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_thumbnail', id + 1 FROM thumbnail ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_transmittancerange', id + 1 FROM transmittancerange ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_well', id + 1 FROM well ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_wellannotationlink', id + 1 FROM wellannotationlink ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_wellreagentlink', id + 1 FROM wellreagentlink ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_wellsample', id + 1 FROM wellsample ORDER BY id DESC LIMIT 1;
INSERT INTO seq_table SELECT 'seq_wellsampleannotationlink', id + 1 FROM wellsampleannotationlink ORDER BY id DESC LIMIT 1;
COMMIT;