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.
