string - postgresql cannot use concatenated variables in user defined function - Stack Overflow

admin管理员组

文章数量:1316833

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).

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).

• string
• variables
• user-defined-functions
• psql

Share Improve this question Follow asked Jan 29 at 20:22 ziu fasziu fas 3144 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

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;

本文标签： stringpostgresql cannot use concatenated variables in user defined functionStack Overflow