Table name as a PostgreSQL Parameter - syntax error using %I

Question

I have a (rather lengthy) select query that I need to run on tables of unknown names to return another table. Is there a way to do this using dynamic commands?

I keep getting a syntax error on %I:

Function:

CREATE OR REPLACE FUNCTION angles(table_name TEXT)
  RETURNS TABLE (id int, name varchar, polygon_num int, point_order int) AS 
$BODY$
BEGIN
  RETURN QUERY EXECUTE 'select id,
       name,
       polygon_num,
       point_order as vertex,
       --
       case when point_order = 1
         then last_value(ST_Astext(ST_Makeline(sp,ep))) over (partition by id, polygon_num)
         else lag(ST_Astext(ST_Makeline(sp,ep)),1) over (partition by id, polygon_num order by point_order)
       end ||' - '||ST_Astext(ST_Makeline(sp,ep)) as lines,
       --
       abs(abs(
       case when point_order = 1
         then last_value(degrees(ST_Azimuth(sp,ep))) over (partition by id, polygon_num)
         else lag(degrees(ST_Azimuth(sp,ep)),1) over (partition by id, polygon_num order by point_order)
       end - degrees(ST_Azimuth(sp,ep))) -180 ) as ang
from (-- 2.- extract the endpoints for every 2-point line segment for each linestring
      --     Group polygons from multipolygon
      select id,
             name,
             coalesce(path[1],0) as polygon_num,
             generate_series(1, ST_Npoints(geom)-1) as point_order,
             ST_Pointn(geom, generate_series(1, ST_Npoints(geom)-1)) as sp,
             ST_Pointn(geom, generate_series(2, ST_Npoints(geom)  )) as ep
      from ( -- 1.- Extract the individual linestrings and the Polygon number for later identification
             select id,
                    name,
                    (ST_Dump(ST_Boundary(the_geom))).geom as geom,
                    (ST_Dump(ST_Boundary(the_geom))).path as path -- To identify the polygon
              from %I ) as pointlist ) as segments';


END;
$BODY$
LANGUAGE plpgsql;

Query:

SELECT angles('poly_and_multipoly');

score 3 · Accepted Answer · answered Mar 18 '16 at 03:18

3

You're neglecting the format function to format your string.

%I is an argument to the format function, here you're just trying to execute a literal string that happens to have %I in it.

EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname)
    USING newvalue, keyvalue;

Here, you can see colname is an argument to the format function, while newvalue and keyvalue are arguments to the result SQL query.

So, wrap your string in the format function with the proper arguments and you should be good to go.

answered Mar 18 '16 at 03:18

Will Hartung

115,893
19
128
203

Thanks! hm. Looking at the [http://www.postgresql.org/docs/current/static/functions-string.html#FUNCTIONS-STRING-FORMAT] I'm not sure what I'm missing. I put in: 'from format('%I', table_name)' and got a **ERROR: type "I" does not exist** – user14696 Mar 18 '16 at 16:49
You need to wrap the entire statement, not just a portion of it. EXECUTE only work on a simple string. The format function does the actual substitution resulting in a simple string that can then be used by EXECUTE. Notice in my example, I have the entire statement being passed to format. – Will Hartung Mar 18 '16 at 17:38
Format() seems like a beautiful thing...but the frustration caught up with me. I ended up passing a regular concatenated string **from ' || $1 || ') as pointlist)** ... but I'll keep working with it. Thanks! – user14696 Mar 18 '16 at 18:32

Table name as a PostgreSQL Parameter - syntax error using %I

1 Answers1