Not able to execute query in function using input parameter?

Question

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.

Laurenz Albe · Accepted Answer · 2020-10-07 15:27:25Z

1

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.

answered Oct 7, 2020 at 15:27

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

2 Comments

kafka Over a year ago

how do i select the columns from the execute statement, and return them?

Laurenz Albe Over a year ago

EXECUTE $$SELECT ...$$ INTO row_variable;

Collectives™ on Stack Overflow

Not able to execute query in function using input parameter?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related