Dynamic query splicing error,a fuction use falie

Question

I want to use the cursor to get the count(data) in all tables under the schema I need.

But I am unfamiliar with SQL, and I still cannot pass the following code:


CREATE OR REPLACE FUNCTION check_tool.get_nae(v_dbName character varying)
 RETURNS numeric
 LANGUAGE edbspl
 SECURITY DEFINER
AS $function$

    numInsert numeric;
    numCal numeric;
    v_result numeric;
    query TEXT DEFAULT '';

    cursor c_pj is 
        select t.table_schema::text as tableSchema,
            t.table_name::text as tableName
        from information_schema.tables t
        where t.table_catalog = v_dbName
        and t.table_type = 'BASE TABLE'
        and t.table_schema in (select schema_name from check_tool.img_schema where dbName = v_dbName);

BEGIN
    v_result := -1;
    numInsert := 0;
    for r_pj in c_pj loop
        query := 'select count(*) from '||tableSchema||'.'||tableName||';';     -- select count(*) from "item"."project";
        execute query into numCal;

        insert into check_tool.img_result(schema_name,table_name,num) values (r_pj.tableSchema,r_pj.tableName,numCal);

        numInsert := numInsert + 1;
        if numInsert > 1000 then
            numInsert := 0;
            commit;
        end if;
    end loop;
    commit;

    v_result := 0;
    RETURN v_result;
EXCEPTION
    WHEN others THEN
        RETURN v_result;
END get_nae$function$
;
/

I also tried concat() and quote_ident(), but the result is not ideal.

error in [execute query into numCal;] Error-Message:syntax error at or near "query". — Nova_Crystallis, Jan 05 '21 at 10:36
That looks correct to me - at least in PL/pgSQL. What is this `LANGUAGE edbspl` you are using? That's not a standard Postgres language. You also can't use `commit` inside a function (at least not in standard Postgres). — , Jan 05 '21 at 10:42
T w T ,edbsql is EDB Postgres,We are now moving from oracle to edb.The content of the project needs to be kept secret,this is the same kind of SQL I wrote based on the original logic. — Nova_Crystallis, Jan 05 '21 at 10:50

score 0 · Accepted Answer · answered Jan 05 '21 at 10:47

Dynamic SQL should be constructed using format() to better handle identifiers. In Postgres you can't commit inside a function, only in a procedure. Committing in a loop rarely improves the performance to begin with, so I would just skip that. I also wouldn't hide the real error (by just returning -1 or 0) but simply let any exception reach the caller of the procedure. And language edbspl is nothing I know, but in PL/pgSQL I would write it like this:

CREATE OR REPLACE PROCEDURE check_tool.get_nae(v_dbName character varying)
 LANGUAGE plpgsql
AS 
$body$
declare --<< required in PL/pgSQL to declare variables
  numcal numeric;
  query TEXT DEFAULT '';
  l_rec record;
BEGIN
  for l_rec in  select t.table_schema::text as tableschema, t.table_name::text as tablename
                from information_schema.tables t
                where t.table_catalog = v_dbName
                and t.table_type = 'BASE TABLE'
                and t.table_schema in (select schema_name from check_tool.img_schema where dbName = v_dbName)
  loop
    query := format('select count(*) from %I.%I', l_rec.tableschema, l_rec.tablename);
    execute query into numcal;

    insert into check_tool.img_result(schema_name,table_name,num) values (l_rec.tableschema, l_rec.tablename, numcal);
  end loop;
  commit;
  
END;
$body$
;

Note that the condition t.table_catalog = v_dbName is actually useless, because you can't query tables that are not in the current database anyway.

Note that you don't really need a stored procedure to do this. You can use query_to_xml() to do this in a single SQL query by adjusting this answer

insert into check_tool.img_result(schema_name,table_name,num) 
select table_schema, 
       table_name, 
       (xpath('/row/cnt/text()', xml_count))[1]::text::int as row_count
from (
  select table_name, table_schema, 
         query_to_xml(format('select count(*) as cnt from %I.%I', table_schema, table_name), false, true, '') as xml_count
  from information_schema.tables
  where t.table_type = 'BASE TABLE'
  and t.table_schema in (select schema_name from check_tool.img_schema)
) t;

Dynamic query splicing error,a fuction use falie

1 Answers1