CODEWITHSUNDEEP
postgresqlstored-procedures
mchouhan
mchouhan

Reputation: 1873

Postgres insert procedure throws foreign key exception

I have two tables vendor_service_place and places with places.id as a foreign key in vendor_service_table. I have created a procedure which first inserts an entry into places table and then takes that id from LASTVAL() and insert an entry into vendor_service_table. But when I am executing this function I am getting 

insert or update on table "vendor_service_place" violates foreign key 
constraint "fk_places"
DETAIL:  Key (place_id)=(2057) is not present in table "places".
CONTEXT:  SQL function "insert_data" statement 2

This is my insert procedure:

CREATE FUNCTION insert_data(vendorid integer,
                        serviceid integer,
                        name text,
                        address text,
                        latitude text,
                        longitude text,
                        contact_info text,
                        rating numeric,
                        description text) RETURNS bigint AS $$
    INSERT INTO places(name,address,latitude,longitude,contact_info,created_at)
    VALUES (name,address,latitude,longitude,contact_info,current_timestamp);

    INSERT INTO vendor_service_place(vendor_id,service_id,place_id,description,rating,created_at)
    VALUES (vendorid,serviceid,LASTVAL(),description,rating,current_timestamp);
    SELECT LASTVAL() as result;
$$ LANGUAGE SQL;

I am suspecting that Postgres performs some kind of batching where it executes both these statements together, that's probably why Its not able to find the id in places table. Any ideas on how to do it properly?

Upvotes: 0

Views: 673

Answers (1)

mchouhan
mchouhan

Reputation: 1873

Seems using lastval() to get last insert id is not recommend if you are doing multiple inserts. Postgres not returning lastval() properly. Procedure is working fine after replacing LastVal() with return id statement.

      DECLARE
              insert_id bigint;
      BEGIN
      INSERT INTO places(name,address,latitude,
                         longitude,contact_info,
                         created_at,coordinates)
      VALUES (name,address,latitude,
              longitude,contact_info,
              current_timestamp,
              ST_SetSRID(ST_MakePoint(cast (longitude as numeric),cast (latitude as numeric)),4326))
              returning id into insert_id;

      INSERT INTO vendor_service_place(vendor_id,service_id,place_id,
                                       description,rating,created_at)
      VALUES (vendorid,serviceid,insert_id,
              description,rating,current_timestamp);
      return insert_id;
      END

Upvotes: 1

Related Questions