Declare a Table as a variable in a stored procedure?

Question

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

There is no such thing as a table variable in PL/pgSQL. And I don't see the reason to use one. You could simply use `for var_r in select entity_id ... from temp... loop ...` there is no need to store the result of that query anywhere. Btw: your function isn't returning a proper result. — , Oct 02 '20 at 10:01
@a_horse_with_no_name good approach, I am just running into a different issue now.. — kafka, Oct 02 '20 at 10:25
You can't use a variable for a table name (or identifiers in general), you need [dynamic SQL](https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN) for this. You also don't need to reference parameters using numbers - you can use their names — , Oct 02 '20 at 10:27
"*insert them into return table.*" - there is no such thing as a "return table" - you just return the values: https://www.postgresql.org/docs/current/plpgsql-control-structures.html#id-1.8.8.8.3.4 — , Oct 02 '20 at 10:28
Don't forget to put a space before the table name, and to put the result into two separate variables. — Jonathan Jacobson, Oct 02 '20 at 11:02

score 2 · Accepted Answer · 2020-10-02T12:31:14.103

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

Hmm I am getting ERROR: cannot assign non-composite value to a record variable I assume it is for the var_r type? — kafka, Oct 02 '20 at 15:53

score 1 · Answer 2 · answered Oct 02 '20 at 10:26

1

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.

answered Oct 02 '20 at 10:26

Jonathan Jacobson

1,441
11
20

how with text? i cant seem to use that? – kafka Oct 02 '20 at 10:37
Given the state of your code, you can't use it any kind of parameter. First rewrite your function to use dynamic SQL and then you'll see which data type suits you best. – Jonathan Jacobson Oct 02 '20 at 10:40
Jacobsen, it has been changed to a dynamic query, but how do then store the the select statement into variable? – kafka Oct 02 '20 at 10:55
@kafka Use the `INTO` keyword as described in the docs. – Jonathan Jacobson Oct 02 '20 at 11:00
I guess it returns one value and not multiple row which the select statement should return? – kafka Oct 02 '20 at 11:04
If it might return multiple rows then read a_horse_with_no_name's answer. – Jonathan Jacobson Oct 02 '20 at 11:07

Declare a Table as a variable in a stored procedure?

2 Answers2