Postgres SELECT that doesn't work with the first function call-CodePudding

The function does its duty, that is, if there is no hub with that data it adds it and returns its ID otherwise it finds the hub ID with the data passed in input from the JSON.

CREATE OR REPLACE FUNCTION public.add_hub(
    new_hub json,
    OUT hub_id bigint)
    RETURNS bigint
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
hub_name hub.name%TYPE := new_hub->>'name';
hub_city hub.city%TYPE := new_hub->>'city';
hub_province hub.province%TYPE := new_hub->>'province';
hub_region hub.region%TYPE := new_hub->>'region';
hub_address hub.address%TYPE := new_hub->>'address';

BEGIN
    SELECT hub.id FROM hub 
        WHERE name = hub_name 
            AND city = hub_city 
            AND province = hub_province
            AND address = hub_address 
            AND region = hub_region 
            INTO hub_id;

   IF NOT FOUND THEN
         INSERT INTO public.hub(name, city, province, address, region)
            VALUES (hub_name, hub_city, hub_province, hub_address, hub_region)
            RETURNING hub.id INTO hub_id;
   END IF;
   
END;
$BODY$;

ALTER FUNCTION public.add_hub(json)
    OWNER TO postgres;

The problem arises when I run a query like this:

SELECT * FROM hub 
  WHERE hub.id = add_hub('
  {
   "name":"HUB TEST",
   "city":"Los Angeles",
   "province":"LA",
   "address":"Street 1",
   "region":"CA"
  }
');

If the hub data is not present in the table they are inserted, however the query with the SELECT returns nothing, if executed again it returns the correct hub.

The weird thing is following query always works and always returns hub id, even when data is added the first time:

SELECT add_hub('
  {
   "name":"HUB TEST",
   "city":"Los Angeles",
   "province":"LA",
   "address":"Street 1",
   "region":"CA"
  }
');

It always returns a valid value, even in the case of the first iteration, so I really don't understand where I'm wrong, if you have any advice I would be grateful.

CodePudding user response：

Modifying the table data in the WHERE clause is a very bad idea. First, the function will be called as many times as there are rows in the table. Second, while executing the query, the server cannot modify the queried dataset every time it evaluates the condition.

If you want to use the described mechanism, the easiest solution is to change the function so that it returns the entire found or inserted row and use the last query in your question.