CODEWITHSUNDEEP
databasepostgresqlinsertplpgsql
Miksel
Miksel

Reputation: 77

Cant use id in function after insert returning into

I built a function that inserts 1 sample, then insert 4 groups, does some operations and calls another functions and by the end update the original sample with the rest of the data.

This is the function:

-- Fill time dimension from timestamp
CREATE OR REPLACE FUNCTION build_sample() RETURNS void AS $$
DECLARE 
--CONSTANTS
c_short CONSTANT INTERVAL := '5 minutes';
c_medium  CONSTANT INTERVAL := '20 minutes';
c_long CONSTANT INTERVAL := '120 minutes';
c_samplePeriod CONSTANT int := 86400;

size int;
startDateTime timestamp;
endDateTime timestamp;

timeDimensionId int;
weatherId int;

this_taxiid int;
this_sampleid int;

this_quickstops int;
this_moderatestops int;
this_longstops int;

stationarytime int;
movingtime int;

result int;
BEGIN
startDateTime := '2008-02-06 00:00:00';
endDateTime := startDateTime + INTERVAL '1 second' * c_samplePeriod;
this_taxiid := 366;
weatherId:=254;

-- Get timeid
Select distinct "refidHour" from "TimeDimension" into timeDimensionId 
where timestamp=startDateTime;

-- Insert basic data in a sample
INSERT INTO "Samples" ("period","refidTaxi","refidTime","refidWeather") 
VALUES(c_samplePeriod,this_taxiid,timeDimensionId,weatherId)
RETURNING sampleid into this_sampleid;

RAISE NOTICE 'sampleid=%',this_sampleid;

--Sample moving group
Insert into "EntryGroup" Values(this_sampleid,1); 
--Sample shortstops group
Insert into "EntryGroup" Values(this_sampleid,2); 
--Sample mediumstops group
Insert into "EntryGroup" Values(this_sampleid,3); 
--Sample longstops group
Insert into "EntryGroup" Values(this_sampleid,4); 

Select build_stops(this_taxiid, startDateTime, this_sampleid);

--#Quick stops
Select count(*) into this_quickstops from "EntryGroup" 
where "refidSample" =this_sampleid and "refidType"=2;
--#Moderate stops
Select count(*) into this_moderatestops from "EntryGroup" 
where "refidSample"=this_sampleid and "refidType"=3;
--#Long stops
Select count(*) into this_longstops from "EntryGroup" 
where "refidSample"=this_sampleid and "refidType"=4;

RAISE NOTICE 'quickstops=% moderatestops=% longstops=%',this_quickstops, this_moderatestops, this_longstops;

--Calculate stationary time and moving time
Select this_quickstops*c_short+this_moderatestops*c_medium+this_longstops*c_long into stationarytime;
Select c_samplePeriod-stationarytime into movingtime;

RAISE NOTICE 'statironarytime=% movingtime=%',statironarytime, movingtime;

-- Update sample with rest of data
UPDATE "Samples" SET ("stationarytime","movingtime","quickstops","moderatestops","longstops") = (stationarytime,movingtime,this_quickstops,this_moderatestops,this_longstops)
where sampleid=this_sampleid;

RAISE NOTICE 'period=% refidTaxi=% refidTime=%',c_samplePeriod,this_taxiid,timeDimensionId;

END;
$$ LANGUAGE plpgsql;

Im getting the following error

snowflake=# select build_sample();
NOTICE:  sampleid=3
ERROR:  insert or update on table "EntryGroup" violates foreign key constraint "fk_Entries_has_Samples_Samples1"
DETAIL:  Key (refidSample)=(1) is not present in table "Samples".
CONTEXT:  SQL statement "Insert into "EntryGroup" Values(this_sampleid,1)"

The problem occours when the function tries to insert a group with the sampleid of the previous inserted sample.

Is there a problem tansaction wise of doing what im doing, i mean, because the transaction hasn't finished does it mean that the sample isnt really inserted and i cant insert a group for that sample?

Upvotes: 0

Views: 45

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658707

Always provide a target list for persisted INSERT statements (rare exceptions apply). In your function, replace:

--Sample moving group
Insert into "EntryGroup" Values(this_sampleid,1); 
--Sample shortstops group
Insert into "EntryGroup" Values(this_sampleid,2); 
--Sample mediumstops group
Insert into "EntryGroup" Values(this_sampleid,3); 
--Sample longstops group
Insert into "EntryGroup" Values(this_sampleid,4);

With this:

INSERT INTO "EntryGroup"("refidSample", "refidType")
SELECT this_sampleid, i.type
FROM  (VALUES (1), (2), (3), (4)) i(type);

Guessing column names from the rest of the code. Use actual target column names.

While being at it, I replaced your four inserts with a single (cheaper) multi-row insert.

Upvotes: 1

klin
klin

Reputation: 121834

Is there a problem tansaction wise of doing what im doing, i mean, because the transaction hasn't finished does it mean that the sample isnt really inserted and i cant insert a group for that sample?

No. You probably mismatched columns in insert.

Upvotes: 1

Related Questions