Cfrgtkky

I have a function that needs to dynamically validate the input based on the value type. It does this by finding the constraint in another table, but for simplicity I provide the constraint as well in the function below.

This other table contains (value_type, value_constraint), where value_constraint is a text field that contains e.g. value::int > 0. I need to dynamically check this constraint within my insert function. I was trying to do that using EXECUTE as seen below, but it's not working.

How do I dynamically execute a condition statement and get the value as a boolean into v_successful_insert?

CREATE OR REPLACE FUNCTION insert_value(p_value_type text, p_value_constraint text, p_value text) RETURNS boolean

    AS $$

    DECLARE

        v_successful_insert bool;

    BEGIN

        EXECUTE p_value_constraint INTO v_successful_insert;



        IF v_successful_insert THEN

            INSERT INTO my_table (value_type, value)

            VALUES (p_value_type, p_value);

        END IF;

        RETURN v_successful_insert;

    END;

    $$

    LANGUAGE plpgsql volatile;

The code is run on Postgresql 10.6.

You need to do a SELECT in the execute. For example:

DO $$

DECLARE

    p_value TEXT := 'x';

    p_value_constraint TEXT := '::int > 0';

    result BOOLEAN;

BEGIN

    BEGIN

        EXECUTE 'SELECT $1' || p_value_constraint

        INTO result

        USING p_value;

    EXCEPTION

        WHEN INVALID_TEXT_REPRESENTATION THEN

            result := FALSE;

    END;

    RAISE NOTICE '%', result;

END $$

Prints FALSE