Casting issue in call of custom aggregate function in dynamic SQL

Question

I am trying to make a check function which checks whether a tsrange is contained within an another one. Consider the complete example in this fiddle.

The last section is doing the "magic", here is an MVP of the functions. An actual execution can be found above in the example.

-- Setting up init stuff
CREATE TABLE country(entity_id BIGSERIAL PRIMARY KEY, valid tsrange, registration tsrange);
CREATE TABLE airport(entity_id BIGSERIAL PRIMARY KEY, valid tsrange, registration tsrange,
country_key bigint references country (entity_id));


-- persisting data into tables
INSERT INTO country 
VALUES(1,'[2016-06-22 19:10:25-07, 2019-01-01 00:00:00)', tsrange(now()::timestamp, '9999-01-01 00:00:00'));
INSERT INTO airport VALUES(1,'[2018-06-22 19:10:25-07, 2020-01-01 00:00:00)', tsrange(now()::timestamp, '9999-01-01 00:00:00'), 1);

-- range_merge wrapper
CREATE OR REPLACE AGGREGATE range_merge(anyrange)
(
    sfunc = range_merge,
    stype = anyrange
);


-- Aggregate function for aggregating all validity periods from select statement. 
CREATE OR REPLACE FUNCTION aggregate_validity(entity_name regclass, entry bigint) returns tsrange AS
$$
DECLARE
    result tsrange;
BEGIN
   EXECUTE format('select range_merge(valid) from %I where entity_id = %s', entity_name, entry) into result;
   return result;
END
$$ LANGUAGE plpgsql;


- Detect integrity breach function 
CREATE OR REPLACE FUNCTION ingoing_outgoing_reference_integrity_breach(altered_table_name text, entity_ids bigint[])  
RETURNS TABLE(
       source_entity text,
       source_entry_id bigint,
       source_entry_validity tsrange,
       is_included_in_timeline boolean,
       aggregated_foreing_entry_validity tsrange,
       foreing_entry_id bigint,
       foreing_entity text,
       is_reference_ingoing boolean
      )
AS $$
DECLARE
    foreign_key  record;
    is_reference_ingoing boolean;
BEGIN

    FOR foreign_key  IN (
        --  Using the information schema from postgres, all constraints of type 
        --  FOREIGN_KEY, is extracted. For each of the foreing key constraint, the 
        --  source_table and foreing_table which is linked by this constraint is then extracted.  
        --  to ensure both ingoing references and outgoing references are extracted from this query, is
        --  condition provided as such that either the source_table = altered_table_name OR foreing_table_name = altered_table_name||'_registration'
        SELECT
            tc.table_name AS source_table, 
            kcu.column_name as column_attribute,
            ccu.table_name AS foreign_table
        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.table_name = altered_table_name OR tc.table_name = altered_table_name) and
            ccu.column_name != 'row_id' and 
            kcu.column_name != 'entity_id') 
    LOOP
        DROP TABLE IF EXISTS lookup_breach;
        EXECUTE format('CREATE TEMP TABLE lookup_breach ON COMMIT DROP AS
                       SELECT '||foreign_key.source_table||'.entity_id AS source_entry_id, -- The Id of the source entry
                    '||foreign_key.source_table||'.'||foreign_key.column_attribute||' AS foreing_entry_id, -- The id of the foreing entry
                    '||foreign_key.source_table||'.valid AS source_entry_valid, -- the validity period of the source entry
                    aggregate_validity('||foreign_key.foreign_table||', '||foreign_key.source_table||'.'||foreign_key.column_attribute||') AS foreing_entry_valid, -- the validity period of the foreing entry
                    aggregate_validity('||foreign_key.foreign_table||', '||foreign_key.source_table||'.'||foreign_key.column_attribute||') <@ '||foreign_key.source_table||'.valid as in_lifespan
                    FROM '||foreign_key.foreign_table||', '||foreign_key.source_table||'
                    WHERE '||foreign_key.source_table||'.'||foreign_key.column_attribute||' = ANY($1)
                    AND '||foreign_key.source_table||'.'||foreign_key.column_attribute||' = '||foreign_key.foreign_table||'_registration.entity_id
                    AND '||foreign_key.source_table||'.registration @> now()::timestamp
                    GROUP BY '||foreign_key.source_table||'.'||foreign_key.column_attribute||', 
                    '||foreign_key.source_table||'.entity_id,'||foreign_key.source_table||'.valid,
                    '||foreign_key.foreign_table||'.valid' ) using entity_ids;

        RETURN QUERY SELECT foreign_key.source_table::text, lookup_breach.source_entry_id, lookup_breach.source_entry_valid, lookup_breach.in_lifespan , lookup_breach.foreing_entry_valid,
        lookup_breach.foreing_entry_id, foreign_key.foreign_table::text, foreign_key.foreign_table::text= altered_table_name AS is_reference_ingoing
        FROM lookup_breach;
    END LOOP;
END
$$ LANGUAGE plpgsql;

I get this error when I call the function:

select * from ingoing_outgoing_reference_integrity_breach('country'::text, '{1}')

ERROR:  function aggregate_validity(country, bigint) does not exist
LINE 5:      aggregate_validity(country, airport.country_key) AS for...
             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  CREATE TEMP TABLE lookup_breach ON COMMIT DROP AS
                       SELECT airport.entity_id AS source_entry_id, -- The Id of the source entry
                    airport.country_key AS foreing_entry_id, -- The id of the foreing entry
                    airport.valid AS source_entry_valid, -- the validity period of the source entry
                    aggregate_validity(country, airport.country_key) AS foreing_entry_valid, -- the validity period of the foreing entry
                    aggregate_validity(country, airport.country_key) <@ airport.valid as in_lifespan
                    FROM country, airport
                    WHERE airport.country_key = ANY($1)
                    AND airport.country_key = country_registration.entity_id
                    AND airport.registration @> now()::timestamp
                    GROUP BY airport.country_key, 
                    airport.entity_id,airport.valid,
                    country.valid
CONTEXT:  PL/pgSQL function ingoing_outgoing_reference_integrity_breach(text,bigint[]) line 33 at EXECUTE

Seems like I need some sort of casting? But I can't figure out how to do it.

Answer 1

You defined your custom function as aggregate_validity(regclass, bigint).

A value of type country is not the same as type regclass. That's what the error message complains about:

ERROR:  function aggregate_validity(country, bigint) does not exist
LINE 5:      aggregate_validity(country, airport.country_key) AS for...

You might cast the string literal 'country' to regclass when passing to the aggregate function.

More importantly, you already use format(), so don't concatenate the SQL string manually, introducing a vector for SQL injection. Identifiers are not safe to concatenate literally and execute! See:

• Define table and column names as arguments in a plpgsql function?

You also don't need to create a temporary table for every iteration of the loop.

And no separate RETURN QUERY. Merge that into a single instance of RETURN QUERY EXECUTE.

Something like:

CREATE OR REPLACE FUNCTION ingoing_outgoing_reference_integrity_breach(_altered_table_name text, _entity_ids bigint[])  
  RETURNS TABLE(source_entity                      text
              , source_entry_id                    bigint
              , source_entry_validity              tsrange
              , is_included_in_timeline            boolean
              , aggregated_foreign_entry_validity  tsrange
              , foreign_entry_id                   bigint
              , foreign_entity                     text
              , is_reference_ingoing               boolean)
  LANGUAGE plpgsql AS
$func$
DECLARE
   foreign_key record;
   is_reference_ingoing boolean;

BEGIN
   FOR foreign_key IN
      SELECT tc.table_name   AS source_table
           , kcu.column_name
           , ccu.table_name  AS foreign_table
      FROM   information_schema.table_constraints       tc 
      JOIN   information_schema.key_column_usage        kcu USING (constraint_name)
      JOIN   information_schema.constraint_column_usage ccu USING (constraint_name)
      WHERE  tc.constraint_type = 'FOREIGN KEY'
      AND    _altered_table_name IN (ccu.table_name, tc.table_name)
      AND    ccu.column_name <> 'row_id'
      AND    kcu.column_name <> 'entity_id'

   LOOP
      RETURN QUERY EXECUTE format(
         'SELECT %1$L                                              -- AS source_entity
               , %1$I.entity_id                                    -- AS source_entry_id     -- the ID of the source entry
               , %1$I.valid                                        -- AS source_entry_valid, -- the validity period of the source entry
               , aggregate_validity(%3$L::regclass, %1$I.%2$I) <@ %1$I.valid -- AS in_lifespan
               , aggregate_validity(%3$L::regclass, %1$I.%2$I)               -- AS foreign_entry_valid -- the validity period of the foreign entry
               , %1$I.%2$I                                         -- AS foreign_entry_id    -- the ID of the foreign entry
               , $1                                                -- AS foreign_entity
               , $2                                                -- AS is_reference_ingoing
          FROM   %3$I, %1$I
          WHERE  %1$I.%2$I = ANY($3)
          AND    %1$I.%2$I = %3$I.entity_id  -- %3$I_registration.entity_id ???
          AND    %1$I.registration @> now()::timestamp
          GROUP  BY %1$I.entity_id
                  , %1$I.valid
                  , %1$I.%2$I
                  , %3$I.valid'  -- WHY this one ???
      , foreign_key.source_table    -- %1$I and %1$L
      , foreign_key.column_name     -- %2$I
      , foreign_key.foreign_table   -- %3$I and %3$L
      )
      USING foreign_key.foreign_table::text                        -- $1
          , foreign_key.foreign_table::text = _altered_table_name  -- $2
          , _entity_ids;                                           -- $3;
   END LOOP;
END
$func$;

db<>fiddle here

I fixed all instances of misspelled 'foreign'.

Note the explicit cast to regclass. It's not strictly needed as the correctly quoted sting literal (format specifier L in %3$L) would allow function resolution to kick in and cast to regclass automatically now. (You can try without the cast.)

About format specifiers in format():

• Format specifier for integer variables in format() for EXECUTE?

Might be simplified further, I didn't dig deeper.

Answer 2

I Don't know what you are trying to do with above code. But you are getting the error because you are calling the function ingoing_outgoing_reference_integrity_breach with wrong parameter (as per your fiddle). You are providing the table name as 'coutry', but you should provide it as '"country"'. So your select statement should be like below:

select * from ingoing_outgoing_reference_integrity_breach('"country"'::text, '{1}')