Dynamically change column names in query in PostgreSQL

Question

There is a table with a bunch of columns. I need to capture the column name and dynamically generate queries based on a given column.

Table definition:

id int,
at_11 charcater varying(50),
at_12 charcater varying(50),
at_13 charcater varying(50),
at_14 charcater varying(50)

And the sample rows in the table are:

1,0,0,0,0
2,0,'Low',0,0
3,'High',0,0,0
CREATE OR REPLACE FUNCTION return_at_query(p_column character varying)
 RETURNS TABLE(at_id character varying, v_id numeric)
 LANGUAGE plpgsql
AS $function$
declare 
 v_cnt int; 
begin
 RETURN QUERY EXECUTE
 format('SELECT 1,ドル id
 FROM tab1 WHERE 1ドル <> 0'
 )
 USING p_column;
end;$function$;

When I call the function by passing in a column name value 'at_11', I expect to see the results as 'High',3. But I am getting the whole list of rows instead. How can I accomplish this?

• McNets: Hi and welcome to the forum, please tag your PostgreSQL version.

Answer 1

I've changed v_id from numeric to int, because in your sample data id is integer.

Them IMHO you don't need to use USING, you can simply format your sentence and execute it.

Finally you should escape single quotes around last 0.

CREATE OR REPLACE FUNCTION return_at_query(p_column text)
 RETURNS TABLE(at_id character varying, v_id int)
 LANGUAGE plpgsql
AS $function$
declare 
 ret text; 
begin
 RETURN QUERY EXECUTE
 FORMAT('SELECT %s, id FROM tab1 WHERE %s <> ''0''', p_column, p_column);
end;
$function$;

select return_at_query('at_11')

| return_at_query |
| :-------------- |
| (High,3) |

db<>fiddle here