CODEWITHSUNDEEP
postgresql
TalG
TalG

Reputation: 748

Pass a Table Column Name in a PostgreSQL Function

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

enter image description here

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

Upvotes: 0

Views: 328

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

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

Upvotes: 1

Related Questions