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

score 5 · Accepted Answer · answered Nov 11 '15 at 06:29

5

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);

answered Nov 11 '15 at 06:29

Craig Ringer

307,061
76
688
778

Thank you for the quick response and help. It was actually the distance function that contained the NULL output and it was the second execute that was throwing the error. Including the USING function escaped the NULL values. Thanks again for the help. – MajuS Nov 11 '15 at 16:14

score 0 · Answer 2 · edited Mar 30 '17 at 23:23

In postgres , If anything null is being passed in Dynamic DML , we are bound to get this issue."query string argument of EXECUTE is null" You can use below sample steps for insert and update .

CREATE OR REPLACE FUNCTION samplefunc(

    col1param character varying,
    col2param character varying,
    col3param character varying,
    col4param character varying,
    col5param character varying,
    col6param character varying,
    col7param character varying,
    col8param character varying

    RETURNS boolean AS

$BODY$

declare

begin

EXECUTE format( 'insert into '||tablename||' (id, col1, col2, col3, col4, col5)values($1,$2,$3,$4,$5)') USING  col1param ,col2param,col3param,col4param,col5param;


EXECUTE format('update '||tablename||' set  col1 =$1,col2 = $2,col3=$3,col4=$4,col5=$5
where col6=$6 and col7=$7 and col8=$8 ') using col1param,col2param,,col3param,col4param,col5param,col6param,col7param,col8param;

end

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

2 Answers2