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

Question

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.

klin · Accepted Answer · 2022-06-17 14:01:23Z

2

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.

edited Jun 17, 2022 at 14:01

answered Jun 17, 2022 at 13:50

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

roothunter Over a year ago

Yes in fact, I arrived at the solution of the function that returns all the data of the hub and not only the ID, it is convenient for me since I use this function in a bulk call from nodejs, so I find all the data for each element of the array

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related