ijustlovemath ijustlovemath - 2 months ago 7
SQL Question

OUT parameters from plpgsql function are NULL in function, but show when unit tested

Using PostgreSQL 9.5.

Here's the code in question, from job_parameters():

SELECT
j.controller_id
, j.model_id
, (
SELECT cp.parameters
FROM commit_schema.controller_parameters cp
WHERE cp.parameters_id = j.controller_parameters_id
AND cp.controller_id = j.controller_id
) AS _ctrl_par
, (
SELECT mp.parameters
FROM commit_schema.model_parameters mp
WHERE mp.parameters_id = j.model_parameters_id
AND mp.model_id = j.model_id
) AS _mod_par
, j.initial_glucose_id
INTO
controller_id
, model_id
, controller_parameters
, model_parameters
, initial_glucose_id
FROM
commit_schema.job j
-- INNER JOIN
-- commit_schema.model_parameters mp
-- ON j.model_parameters_id = mp.parameters_id
-- INNER JOIN
-- commit_schema.controller_parameters cp
-- ON j.controller_parameters_id = cp.parameters_id
WHERE j.sim_id = sim_id;


When running this query with a fixed sim_id (used in the last
WHERE
clause) in a separate window, I get all of the rows I expect to be returned. The
SELECT INTO
moves the results of this query directly into the
OUT
parameters of this plpgsql function, but when this function is run, all columns except for the
sim_id
are
NULL
, which is due to the fact that the
sim_id
is selected earlier in the function.

At first I thought this was due to the keys I was using for the Inner Join causing the rows to show up NULL, so I opted for subqueries instead. This works when running this query on its own, but integrating it into the rest of the function causes it to fail.

I've tried to troubleshoot using
RAISE NOTICE '%'
followed by individual parameters, but it doesn't seem to print to the console.

The full code for all relevant functions is here:

DROP DOMAIN IF EXISTS computer_name CASCADE;
CREATE DOMAIN computer_name AS varchar(50);

DROP TYPE IF EXISTS error_cluster CASCADE;
CREATE TYPE error_cluster AS (
error_code integer
, error_msg varchar(100)
);

DROP FUNCTION IF EXISTS select_client_id(computer_name);
CREATE FUNCTION select_client_id (
IN clean_name computer_name DEFAULT 'none'
, OUT client_id integer
)
AS $$
BEGIN
SELECT c.client_id INTO client_id
FROM commit_schema.client c
WHERE c.client_name = $1
LIMIT 1;
END;
$$ LANGUAGE plpgsql;

DROP FUNCTION IF EXISTS get_client_id(computer_name, error_cluster);
CREATE FUNCTION get_client_id (
IN clean_name computer_name DEFAULT 'none'
, IN error_in error_cluster DEFAULT (0,'')
, OUT _client_id integer
, OUT error_out error_cluster)
AS $$
BEGIN
SELECT client_id INTO _client_id
FROM select_client_id(clean_name);
IF _client_id IS NULL THEN
INSERT INTO commit_schema.client (client_name) VALUES (clean_name);
SELECT client_id INTO _client_id
FROM select_client_id(clean_name);
END IF;
IF _client_id IS NULL THEN
SELECT
-10000
, 'No Client ID found after Client Name insert.'
INTO
error_out.error_code
, error_out.error_msg;
ELSE
SELECT
error_in.error_code
, error_in.error_msg
INTO
error_out.error_code
, error_out.error_msg;
END IF;
END;
$$ LANGUAGE plpgsql;

/*
Get a simulation ID
*/

DROP FUNCTION IF EXISTS get_sim_id(error_cluster);
CREATE FUNCTION get_sim_id(
IN error_in error_cluster
, OUT _sim_id integer
, OUT error_out error_cluster)
AS $$
BEGIN
-- SELECT
-- MIN(j.sim_id)
-- INTO
-- _sim_id
-- FROM commit_schema.job j
-- WHERE j.job_status_id = 0;
UPDATE commit_schema.job j
SET job_status_id = 1
FROM (
SELECT sim_id
FROM commit_schema.job
WHERE job_status_id = 0
ORDER BY sim_id
LIMIT 1
FOR UPDATE SKIP LOCKED
) sub
WHERE j.sim_id = sub.sim_id
RETURNING j.sim_id INTO _sim_id;
IF NOT FOUND THEN
SELECT
-10000
, 'No more jobs left to run!'
, -1
INTO
error_out.error_code
, error_out.error_msg
, _sim_id;
ELSE
SELECT
error_in.error_code
, error_in.error_msg
INTO
error_out.error_code
, error_out.error_msg;
END IF;
END;
$$ LANGUAGE plpgsql;

DROP FUNCTION IF EXISTS set_job_status(integer, smallint, error_cluster);
CREATE FUNCTION set_job_status(
IN sim_id integer
, IN status smallint
, IN error_in error_cluster
, OUT error_out error_cluster)
AS $$
BEGIN
UPDATE commit_schema.job j
SET j.job_status_id = $2
WHERE j.sim_id = $1;
IF NOT FOUND THEN
SELECT
-10000
, 'No job with sim_id='||$1
INTO
error_out.error_code
, error_out.error_msg;
ELSE
SELECT
error_in.error_code
, error_in.error_msg
INTO
error_out.error_code
, error_out.error_msg;
END IF;
END;
$$ LANGUAGE plpgsql;

DROP FUNCTION IF EXISTS set_job_status_to_pending(integer, error_cluster);
CREATE FUNCTION set_job_status_to_pending(
IN sim_id integer
, IN error_in error_cluster
, OUT error_out error_cluster)
AS $$
BEGIN
SELECT
sj.error_out.error_code
, sj.error_out.error_msg
INTO
error_out.error_code
, error_out.error_msg
FROM set_job_status($1, 1, error_in.*) sj;
END;
$$ LANGUAGE plpgsql;

DROP FUNCTION IF EXISTS set_job_status_to_running(integer, error_cluster);
CREATE FUNCTION set_job_status_to_running(
IN sim_id integer
, IN error_in error_cluster
, OUT error_out error_cluster)
AS $$
BEGIN
SELECT
sj.error_out.error_code
, sj.error_out.error_msg
INTO
error_out.error_code
, error_out.error_msg
FROM set_job_status($1, 2, error_in.*) sj;
END;
$$ LANGUAGE plpgsql;

DROP FUNCTION IF EXISTS reset_job_status(integer, error_cluster);
CREATE FUNCTION reset_job_status(
IN sim_id integer
, IN error_in error_cluster
, OUT error_out error_cluster)
AS $$
BEGIN
SELECT
sj.error_out.error_code
, sj.error_out.error_msg
INTO
error_out.error_code
, error_out.error_msg
FROM set_job_status($1, 0, error_in.*) sj;
END;
$$ LANGUAGE plpgsql;

DROP FUNCTION IF EXISTS job_parameters(computer_name);
CREATE FUNCTION job_parameters (
IN computer_id computer_name DEFAULT 'none'
, OUT controller_id integer
, OUT sim_id integer
, OUT controller_parameters integer ARRAY
, OUT model_id integer
, OUT model_parameters integer ARRAY
, OUT initial_glucose_id integer
, OUT final_error_code integer
, OUT final_error_msg varchar(100))
AS $$
DECLARE
-- get rid of garbage in the name we receive
clean_name computer_name := lower(regexp_replace(computer_id, '\W+', '', 'g'));
current_error error_cluster;
error_out error_cluster;
BEGIN
SELECT
0
, ''
INTO
current_error.error_code
, current_error.error_msg;
-- -- See if the incoming client already has a client_id
-- -- If it does, use that. Else give it a new one.
SELECT
error_out.error_msg
, error_out.error_code
INTO
current_error.error_msg
, current_error.error_code
FROM get_client_id(clean_name, current_error.*);

-- Get a simulation ID
SELECT
_sim_id
, error_out.error_msg
, error_out.error_code
INTO
sim_id
, current_error.error_msg
, current_error.error_code
FROM get_sim_id(current_error.*);

IF current_error <> (0,'') THEN
SELECT
current_error.error_code
, current_error.error_msg
INTO
final_error_code
, final_error_msg;
RETURN;
END IF;

-- -- Set the job to pending
-- SELECT
-- error_out.error_code
-- , error_out.error_msg
-- INTO
-- current_error.error_code
-- , current_error.error_msg
-- FROM set_job_status_to_pending(sim_id, current_error.*);

-- IF current_error <> (0,'') THEN
-- SELECT
-- current_error.error_code
-- , current_error.error_msg
-- INTO
-- final_error_code
-- , final_error_msg;
-- RETURN;
-- END IF;

-- Get the parameters
SELECT
j.controller_id
, j.model_id
, (
SELECT cp.parameters
FROM commit_schema.controller_parameters cp
WHERE cp.parameters_id = j.controller_parameters_id
AND cp.controller_id = j.controller_id
) AS _ctrl_par
, (
SELECT mp.parameters
FROM commit_schema.model_parameters mp
WHERE mp.parameters_id = j.model_parameters_id
AND mp.model_id = j.model_id
) AS _mod_par
, j.initial_glucose_id
INTO
controller_id
, model_id
, controller_parameters
, model_parameters
, initial_glucose_id
FROM
commit_schema.job j
-- INNER JOIN
-- commit_schema.model_parameters mp
-- ON j.model_parameters_id = mp.parameters_id
-- INNER JOIN
-- commit_schema.controller_parameters cp
-- ON j.controller_parameters_id = cp.parameters_id
WHERE j.sim_id = sim_id;
RAISE NOTICE 'mp: % cp: % simid: %', model_parameters, controller_parameters, sim_id;

IF NOT FOUND THEN
SELECT
current_error.error_msg || 'No row found for sim_id ' || sim_id ||'. '
, -10000
INTO
final_error_msg
, final_error_code;
RETURN;
END IF;

IF controller_id IS NULL THEN
SELECT
current_error.error_msg || 'No Controller ID found for this sim_id. '
, -10000
INTO
current_error.error_msg
, current_error.error_code;
END IF;

IF model_id IS NULL THEN
SELECT
current_error.error_msg || 'No Model ID found for this sim_id. '
, -10000
INTO
current_error.error_msg
, current_error.error_code;
END IF;

IF controller_parameters IS NULL THEN
SELECT
current_error.error_msg || 'No Controller Parameters found for this sim_id. '
, -10000
INTO
current_error.error_msg
, current_error.error_code;
END IF;

IF model_parameters IS NULL THEN
SELECT
current_error.error_msg || 'No Model Parameters found for this sim_id. '
, -10000
INTO
current_error.error_msg
, current_error.error_code;
END IF;

IF current_error = (0,'') THEN
-- If everything went well, set job to running
SELECT
error_out.error_code
, error_out.error_msg
INTO
current_error.error_code
, current_error.error_msg
FROM set_job_status_to_running(sim_id, current_error.*);
ELSE
-- Otherwise we reset the simid
SELECT
error_out.error_code
INTO current_error
FROM reset_job_status(sim_id, current_error.*);
END IF;

SELECT
current_error.error_msg
, current_error.error_code
INTO
final_error_msg
, final_error_code;

END;
$$ LANGUAGE plpgsql;

Answer

The function set_job_status(...) has a syntax error:

CREATE FUNCTION set_job_status(
    IN sim_id integer
    , IN status smallint
    , IN error_in error_cluster
    , OUT error_out error_cluster)
    AS $$
    BEGIN
        UPDATE commit_schema.job j
        SET j.job_status_id = $2  -- invalid!!
        WHERE j.sim_id = $1;

You cannot table-qualify column names in the SET clause of an UPDATE. Must be:

        SET job_status_id = $2

Not sure if that's all, I stopped looking there.