CODEWITHSUNDEEP
postgresql
kafka
kafka

Reputation: 733

Not able to execute query in function using input parameter?

I have this function, which for some reason is causing an issue with thw input parameter i pass to its execute statement.

DROP FUNCTION IF EXISTS ingoing_outgoing_reference_intigrity_breach(altered_table_name text,  entity_ids bigint[]);

DROP TYPE IF EXISTS detection_intgrity_result;

CREATE or REPLACE AGGREGATE  range_merge(anyrange)
(
    sfunc = range_merge,
    stype = anyrange
);


create type detection_intgrity_result 
   as (source_entity_id bigint, 
       source_valid tsrange, 
       causes_intigrity_breach boolean, 
       depending_entity_internal_name text, 
       depending_entity_id bigint, 
       depending_valid tsrange 
      );
   
CREATE OR REPLACE FUNCTION ingoing_outgoing_reference_intigrity_breach(altered_table_name text,  entity_ids bigint[])  
RETURNS setof detection_intgrity_result
AS $$
DECLARE
    i record;
    result_row detection_intgrity_result;
BEGIN
    FOR i IN (
        SELECT
            tc.table_name, kcu.column_name, 
            ccu.table_name AS foreign_table_name
        FROM 
            information_schema.table_constraints AS tc 
            JOIN 
                information_schema.key_column_usage AS kcu
            ON 
                tc.constraint_name = kcu.constraint_name
            JOIN 
                information_schema.constraint_column_usage AS ccu
            ON 
                ccu.constraint_name = tc.constraint_name
        WHERE 
            constraint_type = 'FOREIGN KEY' and 
            ccu.column_name != 'id' and 
            kcu.column_name != 'entity_id' and
            ccu.table_name = altered_table_name OR tc.table_name = altered_table_name 
            and ccu.table_name != tc.table_name) LOOP
  
EXECUTE format('SELECT '||i.table_name||'.entity_id, '||i.table_name||'.'||i.column_name||',
            range_merge('||i.foreign_table_name||'_registration.valid) @> range_merge('||i.table_name||'.valid) as lifespan,
            range_merge('||i.foreign_table_name||'_registration.valid) <@ range_merge('||i.table_name||'.valid) as reverse_lifespan
            from '||i.foreign_table_name||'_registration, '||i.table_name||'
            where '||i.table_name||'.'||i.column_name||' in $2
            and '||i.table_name||'_registration.registration @> now()::timestamp
            GROUP BY '||i.table_name||'.'||i.column_name||', '||i.table_name||'_registration.entity_id') using entity_ids;
            
END LOOP;
END
$$ LANGUAGE plpgsql;

select *  from ingoing_outgoing_reference_intigrity_breach('country', '{1,12,1}')

Am i not able to pass input parameters to a execute statement?

The function look up tables which have ingoing and outgoing refereneces and ensure that data between those two is still valid.

Upvotes: 0

Views: 109

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247830

There are three problems with that EXECUTE statement:

  • You use parameter $2, which corresponds to the second argument, but there is only a single argument.

  • You are trying to pass an array as IN list, which won't work. Use the equivalent, but correct

    WHERE colname = ANY ($1)

  • You construct an SQL string with ||, which makes the code vulnerable to SQL injection. Use the format function with the %I pattern to make the code safe and more readable.

Upvotes: 1

Related Questions