Postgresql Function. Procedural script

Postgresql can be use with procedural call also ..

example as below.

CREATE FUNCTION update_aqsense_id (v_oldid varchar, v_newid varchar, vlayer varchar,geortd_object_type varchar)
returns setof varchar AS $$
DECLARE
nismos RECORD;
nismos_id integer;
nismos_aq varchar;
nismos_aqformat varchar;
nismos_sendonconnect varchar;
nismos_sendonhide varchar;
nismos_sendondisplay varchar;
station_id integer NOT NULL DEFAULT 0;
new_aq varchar;
new_aqformat varchar;
new_sendonconnect varchar;
new_sendonhide varchar;
new_sendondisplay varchar;
-- this function internal variable
status varchar;
BEGIN
IF geortd_object_type = 'label' THEN
FOR nismos IN select label_id,aqsense_id,aqsense_format,sendonconnect,sendondisplay,sendonhide from geortd_label where label_name ILIKE vlayer ORDER BY label_id LOOP
-- Temporary set the variable from old one.
nismos_id := nismos.label_id;
nismos_aq := nismos.aqsense_id;
nismos_aqformat := nismos.aqsense_format;
nismos_sendonconnect := nismos.sendonconnect;
nismos_sendondisplay := nismos.sendondisplay;
nismos_sendonhide := nismos.sendonhide;
station_id := station_id +1;
-- Replacing with new product_id
new_aq := regexp_replace (nismos_aq,v_oldid,v_newid);
new_aqformat := regexp_replace (nismos_aqformat,v_oldid,v_newid);
new_sendonconnect := regexp_replace (nismos_sendonconnect,v_oldid,v_newid);
new_sendonhide := regexp_replace (nismos_sendonhide,v_oldid,v_newid);
new_sendondisplay := regexp_replace (nismos_sendondisplay,v_oldid,v_newid);
UPDATE geortd_label SET aqsense_id = new_aq ,aqsense_format = new_aqformat , sendonconnect = new_sendonconnect, sendonhide = new_sendonhide , sendondisplay = new_sendondisplay where label_id = nismos_id;
-- Return the status.
status := 'updated nismos(' || station_id || ' ) at record number : ' || nismos_id || ' \n';
RETURN NEXT status;
END LOOP;
END IF;

-- windindicator updater
IF geortd_object_type = 'windindicator' THEN
FOR nismos IN select windindicator_id,aqsense_id,sendonconnect,sendondisplay,sendonhide from geortd_windindicator where windindicator_name ILIKE vlayer ORDER BY windindicator_id LOOP
-- Temporary set the variable from old one.
nismos_id := nismos.windindicator_id;
nismos_aq := nismos.aqsense_id;
nismos_sendonconnect := nismos.sendonconnect;
nismos_sendondisplay := nismos.sendondisplay;
nismos_sendonhide := nismos.sendonhide;
station_id := station_id +1;
-- Replacing with new product_id
new_aq := regexp_replace (nismos_aq,v_oldid,v_newid);
new_sendonconnect := regexp_replace (nismos_sendonconnect,v_oldid,v_newid);
new_sendonhide := regexp_replace (nismos_sendonhide,v_oldid,v_newid);
new_sendondisplay := regexp_replace (nismos_sendondisplay,v_oldid,v_newid);
-- Update the table
UPDATE geortd_windindicator SET aqsense_id = new_aq , sendonconnect = new_sendonconnect, sendonhide = new_sendonhide , sendondisplay = new_sendondisplay where windindicator_id = nismos_id;
-- Return the status.
status := 'updated nismos(' || station_id || ' ) at record number : ' || nismos_id || ' \n';
RETURN NEXT status;
END LOOP;
END IF;
-- image table updater.
IF geortd_object_type = 'image' THEN
FOR nismos IN select image_id,aqsense_id,sendonconnect,sendondisplay,sendonhide from geortd_image where image_name ILIKE vlayer ORDER BY image_id LOOP
-- Temporary set the variable from old one.
nismos_id := nismos.image_id;
nismos_aq := nismos.aqsense_id;
nismos_sendonconnect := nismos.sendonconnect;
nismos_sendondisplay := nismos.sendondisplay;
nismos_sendonhide := nismos.sendonhide;
station_id := station_id +1;
-- Replacing with new product_id
new_aq := regexp_replace (nismos_aq,v_oldid,v_newid);
new_sendonconnect := regexp_replace (nismos_sendonconnect,v_oldid,v_newid);
new_sendonhide := regexp_replace (nismos_sendonhide,v_oldid,v_newid);
new_sendondisplay := regexp_replace (nismos_sendondisplay,v_oldid,v_newid);
-- Update the table
UPDATE geortd_image SET aqsense_id = new_aq , sendonconnect = new_sendonconnect, sendonhide = new_sendonhide , sendondisplay = new_sendondisplay where image_id = nismos_id;
-- Return the status.
status := 'updated nismos(' || station_id || ' ) at record number : ' || nismos_id || ' \n';
RETURN NEXT status;
END LOOP;
END IF;
END;
$$ LANGUAGE plpgsql;


to use.. just need one liner each time..


SELECT * from update_aqsense_id ('1050','105050','LabelRainHourly%','label');


how nice..