postgresql cannot use concatenated variables in user defined function

Question

Creating function to extract data from views, whose name prefix is passed as function parameter:

CREATE FUNCTION t (char(2)) RETURNS text AS $$
DECLARE view_name TEXT;
BEGIN
view_name := CONCAT($1, '_any_text');
RETURN view_name;
END;
$$ LANGUAGE plpgsql;

Works as expected, returns text string 'prefix_any_text', until I add some text to function body:

CREATE FUNCTION t (char(2)) RETURNS text AS $$
DECLARE view_name TEXT;
BEGIN
   view_name := CONCAT($1, '_any_text');

   CREATE TEMP TABLE t1 AS 
      SELECT any_column FROM view_name;

RETURN view_name;
END;
$$ LANGUAGE plpgsql;

When trying to execute create (replace) I get:
SQL Error [42P01]: ERROR: relation "view_name" does not exist Where: PL/pgSQL function t(character) line # at SQL statement

Note that output was not changed. Same error persist, if developing function further, it have to return something from temporary table (having changed RETURNS respectively).

Answer 1

I think the issue you're facing happens because PostgreSQL doesn't allow the use of variables directly as table or view names in static SQL queries. When you try to use view_name in your query like SELECT any_column FROM view_name, PostgreSQL expects view_name to be a literal table or view name, not a variable that holds the name of the view.

Can you try this?

CREATE FUNCTION t (char(2)) RETURNS text AS $$
DECLARE 
    view_name TEXT;
    sql_query TEXT;
BEGIN
    view_name := CONCAT($1, '_any_text');  -- Constructing the view name dynamically

    -- Construct the dynamic SQL query
    sql_query := FORMAT('CREATE TEMP TABLE t1 AS SELECT any_column FROM %I', view_name);

    -- Execute the dynamic query
    EXECUTE sql_query;

    RETURN view_name; 
END;
$$ LANGUAGE plpgsql;