Postgresql function dynamic table quote_ident error

Question

I created a dynamic function. I get a part of table name with dynamically. The function is created successfully. But when I execute the function. I get an error. How can I solve this problem? I call the function with

select * from dwgcould.getlatlngcenter(2000653);


CREATE OR REPLACE FUNCTION dwgcould.getlatlngcenter(IN pro_id integer,
    OUT lat_center double precision, OUT lng_center double precision)
AS $$

BEGIN
      EXECUTE 'SELECT st_x(st_centroid( st_transform(geom,4326))) as lng_center  ,st_y(st_centroid( st_transform(geom,4326))) as lat_center
        FROM dwgcould.adpes_v1_' || quote_ident(pro_id) || '_line limit 1';
END;
$$ LANGUAGE plpgsql;

The error code is

ERROR: function quote_ident(integer) does not exist LINE 2: FROM dwgcould.adpes_v1_' || quote_ident(pro_id) || '_line... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. QUERY: SELECT 'SELECT st_x(st_centroid( st_transform(geom,4326))) as lng_center ,st_y(st_centroid( st_transform(geom,4326))) as lat_center FROM dwgcould.adpes_v1_' || quote_ident(pro_id) || '_line limit 1' CONTEXT: PL/pgSQL function dwgcould.getlatlngcenter(integer) line 4 at EXECUTE statement ********** Error ********** ERROR: function quote_ident(integer) does not exist SQL state: 42883 Hint: No function matches the given name and argument types. You might need to add explicit type casts. Context: PL/pgSQL function dwgcould.getlatlngcenter(integer) line 4 at EXECUTE statement

Also How can I check whether table exist?

if (select count(1) from pg_tables where tablename = 'tn') < 1 then ... — Vao Tsun

Vao Tsun Vao Tsun · Accepted Answer · 2017-03-01T08:39:59

better use format, eg:

CREATE OR REPLACE FUNCTION dwgcould.getlatlngcenter(IN pro_id integer,
    OUT lat_center double precision, OUT lng_center double precision)
AS $$

BEGIN
  if (select count(1) from pg_tables where tablename = format('adpes_v1_%s_line',pro_id)) < 1 then
    raise info '%','NO SUCH TABLE!';
    return;
  end if;
  EXECUTE format('SELECT * FROM dwgcould.adpes_v1_%s_line limit 1',pro_id) into lat_center,lng_center;
  return;
END;
$$ LANGUAGE plpgsql;

docs

Postgresql function dynamic table quote_ident error

1 Answers