Pass a Table Column Name in a PostgreSQL Function

Question

I've read some posts about using table column names in a PostgreSQL function but I couldn't make it work for me.

I have this simple function

DROP FUNCTION IF EXISTS public.benchmark(CHARACTER VARYING, CHARACTER VARYING, BIGINT, BIGINT, BIGINT);
CREATE OR REPLACE FUNCTION benchmark(params CHARACTER VARYING, colName CHARACTER VARYING, idFrom BIGINT, idTo BIGINT, testNumber BIGINT) RETURNS SETOF RECORD AS $$
DECLARE
    elemArray TEXT[] := ARRAY(SELECT colName FROM public.test WHERE test.id BETWEEN idFrom AND idTo);
    selectedElem RECORD;
    elem TEXT;
BEGIN
    FOREACH elem IN ARRAY elemArray LOOP
        raise notice 'elem Value: %', elem;
        SELECT elem INTO selectedElem;
        RETURN NEXT selectedElem;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

When I execute it with

SELECT * FROM public.benchmark('ad','name',1,2,1) AS x(Item TEXT);

I get

and what I should be getting are the name column values between idFrom and idTo. How can I use the colName variable as an actual column name in elemArray TEXT[] := ARRAY(SELECT colName FROM public.test WHERE test.id BETWEEN idFrom AND idTo);

Kaushik Nayak · Accepted Answer · 2018-12-28 15:19:59Z

1

You may use RETURNS TABLE + RETURN QUERY EXECUTE for dynamic columns.

CREATE OR REPLACE FUNCTION benchmark(params CHARACTER VARYING, colName 
CHARACTER VARYING, idFrom BIGINT, idTo BIGINT, testNumber BIGINT) 
RETURNS TABLE (colvalue TEXT)  AS 
$$
 BEGIN
  RETURN QUERY EXECUTE --dynamic query
          format('SELECT %I::TEXT FROM test WHERE test.id BETWEEN $1 AND $2',colName)
                       --dynamic cols                           --bind parameters
   USING idFrom,idTo;
 END;
$$ LANGUAGE plpgsql;

Demo

EDIT

I just want to populate it with the elements of colName and use the array later in the extended code

You could use ARRAY_AGG & load it into an array variable instead.

CREATE OR REPLACE FUNCTION benchmark(params CHARACTER VARYING, colName 
CHARACTER VARYING, idFrom BIGINT, idTo BIGINT, testNumber BIGINT) 
RETURNS void   AS 
$$
DECLARE
elemArray TEXT[];
    elem TEXT;
 BEGIN
 EXECUTE format('SELECT array_agg(%I::TEXT) FROM test 
                     WHERE test.id BETWEEN $1 AND $2',colName)
   USING idFrom,idTo INTO elemArray ;
   FOREACH elem IN ARRAY elemArray LOOP
    raise notice 'elem Value: %', elem;
    END LOOP;
 END;
$$ LANGUAGE plpgsql;


knayak=# DO $$
knayak$# BEGIN
knayak$# PERFORM benchmark('ad','name',1,2,1);
knayak$# END
knayak$# $$;
NOTICE:  elem Value: TalG
NOTICE:  elem Value: John Doe
DO

edited Dec 28, 2018 at 15:19

answered Dec 28, 2018 at 14:34

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

10 Comments

TalG Over a year ago

Thanks for the fast response. In my minimal example I do return the elements in elemArray directly, but this code is going to be extended to to some other things with the elemArray so I don't need to return it. I just want to populate it with the elements of colName and use the array later in the extended code.

Kaushik Nayak Over a year ago

@TalG : Sure, The idea is same. Instead of simple return query select column name, use array_agg and load it into an array variable. You should change the return type to whatever you want to return and also remove the return keyword in your actual example's query.

TalG Over a year ago

If doesen't cost you much effort could you please adjust your code to use this array_agg option, I'm new to PostgreSQL.

Kaushik Nayak Over a year ago

@Talg : You're welcome. I hope this answers your original question. You should consider asking a separate question(s) afresh with the details specific to one problem.

TalG Over a year ago

@RaymondNijland you are right and everything works now with

EXECUTE 'SELECT array_agg(' || quote_ident(colName)|| ') FROM test WHERE test.id BETWEEN ' || quote_literal(idFrom) || ' AND ' || quote_literal(idTo) INTO elemArray;

. Thank you both.

|

Collectives™ on Stack Overflow

Pass a Table Column Name in a PostgreSQL Function

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related