CODEWITHSUNDEEP
postgresqlplpgsql
kafka
kafka

Reputation: 733

Declare a Table as a variable in a stored procedure?

I am currently working a stored procedure capable of detecting continuity on a specific set of entries.. The specific set of entries is extracted from a sql query

The function takes in two input parameter, first being the table that should be investigated, and the other being the list of ids which should be evaluated.

For every Id I need to investigate every row provided by the select statement.

DROP FUNCTION IF EXISTS GapAndOverlapDetection(table_name text,  entity_ids bigint[]);

create or replace function GapAndOverlapDetection ( table_name text, enteity_ids bigint[]  )  
returns table ( entity_id bigint, valid tsrange, causes_overlap boolean, causes_gap boolean)
as $$
declare 
    x bigint;
    var_r record;
begin   
    FOREACH x in array $2
    loop
        EXECUTE format('select entity_id, valid from' ||table_name|| '
                          where entity_id = '||x||' 
                          and registration @> now()::timestamp 
                          order by valid ASC') INTO result;
        for var_r in result
        loop
        end loop;
    end loop ;
end 
$$ language plpgsql;

select *  from GapAndOverlapDetection('temp_country_registration', '{1,2,3,4}')

I currently get an error in the for statement saying

ERROR: syntax error at or near "$1" LINE 12: for var_r in select entity_id, valid from $1

Upvotes: 1

Views: 1842

Answers (2)

user330315
user330315

Reputation:

You can iterate over the result of the dynamic query directly:

create or replace function gapandoverlapdetection ( table_name text, entity_ids bigint[])  
  returns table (entity_id bigint, valid tsrange, causes_overlap boolean, causes_gap boolean)
as $$
declare 
    var_r record;
begin   
  for var_r in  EXECUTE format('select entity_id, valid 
                                from %I 
                                where entity_id = any($1)
                                  and registration > now()::timestamp 
                                order by valid ASC', table_name) 
                    using entity_ids
  loop
   ... do something with var_r

    -- return a row for the result
    -- this does not end the function
    -- it just appends this row to the result
    return query 
      select entity_id, true, false; 
  end loop;
end 
$$ language plpgsql;

The %I injects an identifier into a string and the $1 inside the dynamic SQL is then populated through passing the argument with the using keyword

Upvotes: 2

Jonathan Jacobson
Jonathan Jacobson

Reputation: 1518

Firstly, decide whether you want to pass the table's name or oid. If you want to identify the table by name, then the parameter should be of text type and not regclass.

Secondly, if you want the table name to change between executions then you need to execute the SQL statement dynamically with the EXECUTE statement.

Upvotes: 1

Related Questions