Passing a column as a function parameter that creates a table

Question

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)

Stefanov.sm · Accepted Answer · 2022-08-29 14:27:18Z

1

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;
$$;

edited Aug 29, 2022 at 14:27

answered Aug 29, 2022 at 14:12

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Frank Heikens Over a year ago

To avoid serious issues with SQL injection, you'd better use format() or quote_ident() to get the column names in safely.

Stefanov.sm Over a year ago

@FrankHeikens fair enough, answer updated

santee Over a year ago

I am not sure what is the issue, but: one of the columns i need in my table is: CONCAT('Type ', table3.col2). This is supposed to to create a column containing values: Type A or Type B. I just used the above suggestion and i got this error: SQL Error [42703]: ERROR: column "Type " does not exist Where: PL/pgSQL function check_gap(character varying) line 3 at RETURN QUERY

Stefanov.sm Over a year ago

Probably you passed NULL or an empty string as a parameter?

santee Over a year ago

i'm calling like this: select * from check_gap('sl_45'). The issue seem to be: due to me using (') before SELECT ... and again in the CONCAT(' ') ... code interprets as a break in code (i'm just assuming, of course) ... how can i bypass this issue?

|

Collectives™ on Stack Overflow

Passing a column as a function parameter that creates a table

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related