concatenate the contents of two columns of the same table produces a name with double quotes

Question

the situation. I'm producing a function, and part of it needs to get the name of each table by concatenating schemaname.tablename to iterate. part of the code:

the part that it´s producing the error:

select schemaname||'.'||tablename as otra from pg_tables where schemaname = 'vigano' and pg_tables.tablename ~ 'ver1920_soja_filtrado$'

The hole function is this one:

CREATE OR REPLACE FUNCTION yield_summary()
RETURNS TABLE (
   promedio double precision,
   minimo double precision,
   maximo double precision
) LANGUAGE plpgsql AS
$$DECLARE
   v_sql text := '';
   v_sep text := '';
   v_tab text;
BEGIN
   FOR v_tab IN
      select schemaname||'.'||tablename as otra from pg_tables where schemaname = 'vigano' and pg_tables.tablename ~ 'ver1920_soja_filtrado$'
   LOOP
      v_sql := v_sql || v_sep ||
         format(
            'select round(avg(masa_de_re)::numeric,3) as promedio, round(min(masa_de_re)::numeric,3) as minimo, round(max(masa_de_re)::numeric,3) as maximo 
             FROM %I',
            v_tab
         );
      v_sep := ' UNION ALL ';
   END LOOP;

   RETURN QUERY EXECUTE v_sql;
END;$$;

it produces the right table schemaname.tablename, but..it doesn't recognize it because of the double quotes.

ERROR:  no existe la relación «vigano.elcerro_elmolino_ver1920_soja_filtrado»
LINE 2:     FROM "vigano.elcerro_elmolino_ver1920_soja_filtrado" UNI...
                 ^

how do I get rid of the double quotes??

if i use quote_indent()

select quote_ident(schemaname||'.'||tablename) as otra from pg_tables where schemaname = 'vigano' and pg_tables.tablename ~ 'ver1920_soja_filtrado$'

it produces this:

ERROR:  no existe la relación «"vigano.elcerro_elmolino_ver1920_soja_filtrado"»
LINE 2:     FROM """vigano.elcerro_elmolino_ver1920_soja_filtrado"""               ^

If I use quote_literal()

select quote_literal(schemaname||'.'||tablename) as otra from pg_tables where schemaname = 'vigano' and pg_tables.tablename ~ 'ver1920_soja_filtrado$'

it produces:

ERROR:  no existe la relación «'vigano.elcerro_elmolino_ver1920_soja_filtrado'»
LINE 2:     FROM "'vigano.elcerro_elmolino_ver1920_soja_filtrado'" U...
                 ^

thanks

Does this answer your question? [Postgres Dynamic Query Function](https://stackoverflow.com/questions/10639963/postgres-dynamic-query-function) — Agung Wiyono, Jun 26 '20 at 02:54
The code you have shown will **not** result in those values being quoted. Please show us the complete code of your function (or procedure) — , Jun 26 '20 at 05:03

score 0 · Answer 1 · answered Jun 26 '20 at 13:51

Don't pass schema name and table name as a single value to format(). Select two columns and pass two parameters.

FOR v_tab IN
  select schemaname, tablename as otra from pg_tables where schemaname = 'vigano' and pg_tables.tablename ~ 'ver1920_soja_filtrado$'
LOOP
  v_sql := v_sql || v_sep ||
     format(
        'select round(avg(masa_de_re)::numeric,3) as promedio, round(min(masa_de_re)::numeric,3) as minimo, round(max(masa_de_re)::numeric,3) as maximo 
         FROM %I.%I', 
         v_tab.schemaname, v_tab.tablename
     );
  v_sep := ' UNION ALL ';
END LOOP;

The way you used it, makes the format() function "think" that it's a single identifier that contains a . - which would require quoting.

Unrelated to your question, but you can simplify your code and get rid of the FOR loop completely:

select string_agg(
         format('select round(avg(masa_de_re)::numeric,3) as promedio, round(min(masa_de_re)::numeric,3) as minimo, round(max(masa_de_re)::numeric,3) as maximo 
                 FROM %I.%I', 
                 schemaname, tablename), ' UNION ALL ')
   into v_sql                      
from pg_tables 
where schemaname = 'vigano' 
and pg_tables.tablename ~ 'ver1920_soja_filtrado$';

return query 
  execute v_sql;

thanks @a_horse_with_no_name. I was trying your second approach. it throw the next error `ERROR: error de sintaxis en o cerca de «return» LINE 9: return query ^` — javier Moreira, Jun 26 '20 at 15:17

score 0 · Accepted Answer · answered Jun 26 '20 at 13:53

In FORMAT(), %I specifies an identifier, and is always quoted by double quotes...

Try this instead:

CREATE OR REPLACE FUNCTION yield_summary()
RETURNS TABLE (
   promedio double precision,
   minimo double precision,
   maximo double precision
) LANGUAGE plpgsql AS $$
DECLARE
  v_sql text := '';
  v_sep text := '';
  v_tab record;
BEGIN
  FOR v_tab IN
    SELECT schemaname, tablename FROM pg_tables WHERE schemaname = 'vigano' AND pg_tables.tablename ~ 'ver1920_soja_filtrado$'
  LOOP
    v_sql := v_sql || v_sep || format(
      'SELECT round(avg(masa_de_re)::numeric,3) AS promedio, round(min(masa_de_re)::numeric,3) AS minimo, round(max(masa_de_re)::numeric,3) AS maximo 
       FROM %I.%I',
            v_tab.schemaname, v_tab.tablename
      );
    v_sep := ' UNION ALL ';
  END LOOP;
  RETURN QUERY EXECUTE v_sql;
END;
$$;

thanks @Usagi Miyamoto, it works. The issue is that it produces a table with the three values (promedio, minimo, maximo) in the same column, inside brackets. Do you know how to produce a proper table with those column names? thanks — javier Moreira, Jun 26 '20 at 15:20
find the solution. i was doing `select yield_summary()` instead of `select * from yield_summary()` — javier Moreira, Jun 26 '20 at 17:53

concatenate the contents of two columns of the same table produces a name with double quotes

2 Answers2