CREATE OR REPLACE FUNCTION safetytr.func_masterdata
(IN line integer,
IN shift integer,
IN points integer,
IN firstinput timestamp WITHOUT TIME ZONE,
IN actualtime timestamp WITHOUT TIME ZONE
)
RETURNS SETOF integer AS
$$
begin[ident] WITH new_values (var1, var2, var3, var4, var5)
as (
VALUES (line,shift,points,firstinput,actualtime)
),
upsert as
(
UPDATE safetytr.triggerinputmaster m SET m.var5 = nv.var5
FROM new_values nv
WHERE m.var1 =nv.var1
AND m.var2 = nv.var2
RETURNING m.*
)
INSERT INTO safetytr.triggerinputmaster (var1, var2, var3, var4, var5)
SELECT var1, var2, var3, var4, var5
FROM new_values
WHERE NOT EXISTS (SELECT 1FROM upsert up
WHERE up.var1 =new_values.var1
AND up.var2 = new_values.var2)
end
$$
So sieht das gerade aus, hoffe das ist verständlicher.
Lesezeichen