Postgres- SQL state: 22004 - query string argument of EXECUTE is null

Question

I have a table (named VGI_table) that contains a column (named match_tabl) which contains the names of other tables in the same database along with object_ids for those tables. I am trying to create a plpgsql function that loops through each row in the VGI_table and performs a query to retrieve an object from another table as shown below.

The function takes 4 parameters (all varchar), the first two are names of columns in the VGI_table, the third is the name of the VGI_table and the last parameter is the output.

vgi_match_id_col, vgi_match_table_col, vgi_table, output_table 

The code for the function is shown below, ro is used to hold the first table query, match_row holds the output of the queried external table. Distance is an output created using the PostGIS st_distance function.

DECLARE
   ro record;
   match_row record;
   distance float; 

BEGIN

for ro in EXECUTE 'select gid, geom, '||vgi_match_id_col||' as match_id, '||vgi_match_table_col||' as match_table from '||vgi_table
LOOP
    --raise notice '(%)', 'select geom from public.'||ro.match_table||' where gid = '||ro.match_id;


    execute 'select geom from public.'||ro.match_table||' where gid = '||ro.match_id into match_row;


    distance := st_distance(ro.geom, st_transform(match_row.geom,st_srid(ro.geom)));
    EXECUTE 'INSERT INTO '||output_table||' VALUES('||ro.gid||', '||distance||')';


END LOOP;

The table being queried has no null values in the match_tabl column or the object_id colum. The code identifies ro.match_table and ro.match_id as null values when attempting to perform the EXECUTE statement. I even used the RAISE NOTICE function with the same string that is used in the EXECUTE statement and the correct query is returned. If I hard code the execute string with a predefined table_name and object id the script works fine. The link below is similar but I don't think it addresses my question. Thanks for the help.

Similar Question

Answer 1

Well, clearly something you're concatenating is null.

Use the format function instead, that way you'll get more useful info.

format('select geom from public.%I ....', ro.match_table);

Use EXECUTE ... USING ... to insert literals.

e.g.

EXECUTE format('INSERT INTO %I VALUES($1, $2)', output_table) USING (ro.gid, distance);