Passing a column as a function parameter that creates a table-CodePudding

I need to create a function that will generate a table. This table will have columns brought with several left joins. At some point i need to filter the data based on the value of a dynamic column (i need to have: WHERE table_name.dynamic_column_name = 1)(that column has 1s and 0s, i need to keep only the 1s) So when I 'call' the function, user should type like this: SELECT * FROM function_name(dynamic_column_name)

What i did:

CREATE OR REPLACE FUNCTION check_gap (_col_name varchar)
   RETRUNS TABLE ( ... here i have several columns with their type ...)
   LANGUAGE plpsql
AS $function$
     BEGIN
        RETURN QUERY
        SELECT ... a bunch of columns ...
        FROM ... a bunch of tables left joined ...
        WHERE _col_name = 1;
    END;
$function$
;

I even tried with table_name._col_name .. though it wasn't necessary, the query (select from) works just as fine without ** I found some solutions for dynamic value but not for a dynamic column *** I am using PostgreSQL (from DBeaver)

CodePudding user response：

You need dynamic SQL for that. Pls. note that the code below is SQL injection prone.

CREATE OR REPLACE FUNCTION check_gap (col_name varchar)
  RETURNS TABLE (... several columns with their type ...) LANGUAGE plpgsql AS
$$
BEGIN
  RETURN QUERY EXECUTE format
    (
     'SELECT ... a bunch of columns ...
      FROM ... a bunch of tables left joined ...
      WHERE %I = 1;', col_name
    );
END;
$$;