1

I wanted to create a view that sums a table common to a number of schemas, as expressed here, and includes the schema name or a derived expression as a column, so I created a view that includes the schema name as a constant value, by using the quote_literal to wrap the schema name in single quotes.

The quote_literal may not be the right function here, but whatever I do gets wrapped in the double quotes.

CREATE OR REPLACE FUNCTION create_summary_view_quoted(
   created_view text,
   common_view text,
   VARIADIC schemas text[]
) RETURNS text
   LANGUAGE sql AS
$$SELECT format('CREATE VIEW %I AS ', created_view) ||
         string_agg(
            format('SELECT %I as source_schema, * FROM %I.%I ', quote_literal(schemas[i]) , schemas[i], common_view),
            ' UNION ALL '
         )
FROM generate_series(1, cardinality(schemas)) AS i$$;
")

Applying it to the query

SELECT create_summary_view_quoted('newv', 'oldv', 's1', 's2', 's3');

produces

create view newv as
select
    "'s1'" as source_schema,
    *
from
    s1.oldv
union all
select
    "'s2'" as source_schema,
    *
from
    s2.oldv
union all
select
    "'s3'" as source_schema,
    *
from
    s3.oldv

The problem is the double-quote around the single-quoted constant which causes Postgres to treat is as a column name and it barfs.

I fixed the code generation issue by using double single quotes around the first placeholder and dropping the quote_literal function,

i.e. replace

format('SELECT %I as source_schema, * FROM %I.%I ', quote_literal(schemas[i]) , schemas[i], common_view),

with

format('SELECT ''%I'' as source_schema, * FROM %I.%I ', schemas[i] , schemas[i], common_view),

My issue is not so much with quote_literal but how the function always wraps the output of any function which wraps a single-quoted string with double-quotes.

Even when I used a function to strip off a leading suffix in the schema names because they are redundant the single quoted output gets wrapped in double quotes.

eg quote_nullable(right(schemas[i],-10)) the resulting string gets double-quoted.

I could apply the || operator somewhere there but I prefer a quoting function.

Doesn't Postgres have a built-in single quoting function?

Is there some required SQL syntax I'm missing?

Is it a Postgres quirk?

Paul White
  • 94,921
  • 30
  • 437
  • 687
vfclists
  • 1,093
  • 4
  • 14
  • 21

1 Answers1

2

The %I format type specifier tells the format() function to treat the corresponding argument value as an identifier, and since it contains single quotes (as a result of quote_literal()), it is properly enclosed in double quotes to make it a valid identifier.

However, in the SELECT list you want the schema name as a simple string literal, so the %s type specifier will be appropriate.

format(
  'SELECT %s as source_schema, * FROM %I.%I ', 
  quote_literal(schemas[i]) ,
  schemas[i],
  common_view
)...

Perhaps you can also dispense with quote_literal(), if you know the schema names' provenance and can be sure they don't contain any single quotes:

format(
  'SELECT ''%s'' as source_schema, * FROM %I.%I ', 
  schemas[i],
  schemas[i],
  common_view
)...
mustaccio
  • 28,207
  • 24
  • 60
  • 76