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, Commented Mar 1, 2017 at 8:43

Vao Tsun · Accepted Answer · 2017-03-01 08:53:01Z

0

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

edited Mar 1, 2017 at 8:53

answered Mar 1, 2017 at 8:39

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alexis Over a year ago

İt is working but I get empty value. İf I execute directly 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_2000653_line limit 1 I get result.

Vao Tsun Over a year ago

add ` into lat_center,lng_center;` after execute

Collectives™ on Stack Overflow

Postgresql function dynamic table quote_ident error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related