Concatenation with variable in function gives an error

May 20, 2022

So I am trying to run this function.

CREATE OR REPLACE FUNCTION TableIteration() 
  RETURNS TABLE(table_schema text, table_name text)
  LANGUAGE plpgsql
AS
$$
DECLARE 
    tgt_schema varchar;
    list text[] := ARRAY[
    "text1",
    "text2",
    "text3",
    "text4",
    "text5",
    "text6",
    "text7",
    "text8",
    "text9"
];
BEGIN
    FOREACH tgt_schema IN ARRAY list
        LOOP
        RETURN QUERY EXECUTE
        'SELECT t.table_schema :: text, t.table_name::text from information_schema.tables t
        WHERE t.table_schema :: text='||tgt_schema; 
        END LOOP;
    END
$$

But somehow concatenation gives me not what I was expected.

Everytime I launch function I am getting error:

‘ERROR: column "text1" does not exist’

Why postgres interprets my variable as column?

>Solution :

Use single quotes for declared array elements and the function format() with a proper placeholder for literals %L:

    ...
    RETURN QUERY EXECUTE format(
    'SELECT 
        t.table_schema :: text, 
        t.table_name::text 
    FROM information_schema.tables t
    WHERE t.table_schema :: text = %L', tgt_schema); 
    ...