I have a requirement to dynamically execute a query string. For this I have written a function which generates this query string.

The function successfully returns the right query string depending upon the client name that I pass, however, I am struggling to execute the query string.

The function:

CREATE or REPLACE FUNCTION get_query(
clientname varchar(100)
)
RETURNS TEXT AS
$func$
DECLARE
    str text:= '';
strquery text;
DECLARE col_names CURSOR
FOR
    SELECT col_name, col_dtype FROM tbl_client_columns WHERE client_name = clientname;
    col_record RECORD;
BEGIN
    OPEN col_names;
    LOOP
    FETCH NEXT FROM col_names INTO col_record;
        EXIT WHEN NOT FOUND;
        str = str || ', "' || col_record.col_name || '" ' || col_record.col_dtype ;
    END LOOP;
    CLOSE col_names;
    strquery = 'select * from crosstab(
    ''select tp.position_number, cc.col_name, cp.col_value from tbl_positions tp
        INNER JOIN tbl_client_columns cc on tp.client_name = cc.client_name
        INNER JOIN tbl_client_positiondata cp on cc.client_name = cp.client_name and cc.col_name = cp.col_name
        and tp.position_number = cp.position_number
        WHERE tp.client_name = ''''' || clientname || ''''''')
        AS tmp(position_number varchar(255)' || str || ')';

RETURN strquery;

END;
$func$
LANGUAGE PLPGSQL;

Table structure and data examples:

create table public.tbl_positions
                (client_name varchar(50),
                position_number varchar(255),
                position_name varchar(512));

create table public.tbl_client_columns
                (client_name varchar(50),
                col_name varchar(50),
                col_dtype varchar(50),
                col_mandatory boolean);

create table public.tbl_client_positiondata
                (client_name varchar(50),
                col_name varchar(50),
                position_number varchar(255),
                col_value varchar(255));

INSERT INTO tbl_positions VALUES('ClientA','POSA0001','Engineer');   
INSERT INTO tbl_positions VALUES('ClientA','POSA0002','Sr. Engineer');
INSERT INTO tbl_positions VALUES('ClientA','POSA0003','Manager');
INSERT INTO tbl_positions VALUES('ClientA','POSA0004','Sr. Manager');
INSERT INTO tbl_positions VALUES('ClientB','POSB0001','Analyst');
INSERT INTO tbl_positions VALUES('ClientB','POSB0002','Sr. Analyst');

INSERT INTO tbl_client_columns VALUES ('ClientA','Workday_Position','varchar(50)','No');
INSERT INTO tbl_client_columns VALUES ('ClientA','Cost Center','varchar(50)','No');
INSERT INTO tbl_client_columns VALUES ('ClientA','Supervisory Org','varchar(100)','No');
INSERT INTO tbl_client_columns VALUES ('ClientB','Grade','varchar(10)','No');
INSERT INTO tbl_client_columns VALUES ('ClientB','Position Type','varchar(20)','No');

INSERT INTO tbl_client_positiondata VALUES ('ClientA', 'Workday_Position', 'POSA0001','WDPOS0001');
INSERT INTO tbl_client_positiondata VALUES ('ClientA', 'Workday_Position', 'POSA0002','WDPOS0002');
INSERT INTO tbl_client_positiondata VALUES ('ClientA', 'Workday_Position', 'POSA0003','WDPOS0003');
INSERT INTO tbl_client_positiondata VALUES ('ClientA', 'Workday_Position', 'POSA0004','WDPOS0004');
INSERT INTO tbl_client_positiondata VALUES ('ClientA', 'Cost Center', 'POSA0001','CC0001');
INSERT INTO tbl_client_positiondata VALUES ('ClientA', 'Cost Center', 'POSA0002','CC0001');
INSERT INTO tbl_client_positiondata VALUES ('ClientA', 'Cost Center', 'POSA0003','CC0002');
INSERT INTO tbl_client_positiondata VALUES ('ClientA', 'Cost Center', 'POSA0004','CC0002');
INSERT INTO tbl_client_positiondata VALUES ('ClientA', 'Supervisory Org', 'POSA0001','SO0001');
INSERT INTO tbl_client_positiondata VALUES ('ClientA', 'Supervisory Org', 'POSA0002','SO0001');
INSERT INTO tbl_client_positiondata VALUES ('ClientA', 'Supervisory Org', 'POSA0003','SO0001');
INSERT INTO tbl_client_positiondata VALUES ('ClientA', 'Supervisory Org', 'POSA0004','SO0001');
INSERT INTO tbl_client_positiondata VALUES ('ClientB', 'Grade', 'POSB0001','Grade 1');
INSERT INTO tbl_client_positiondata VALUES ('ClientB', 'Grade', 'POSB0002','Grade 2');
INSERT INTO tbl_client_positiondata VALUES ('ClientB', 'Position Type', 'POSB0001','Permanent');
INSERT INTO tbl_client_positiondata VALUES ('ClientB', 'Position Type', 'POSB0002','Temporary');

Thanks in advance for your help.

How am I calling this function:

DO
$$
    BEGIN
        DECLARE cart TEXT:= select get_query('ClientA');
        EXECUTE('$1',cart);
    END;
$$ LANGUAGE PLPGSQL;

When i execute the above code, I get the following error:

ERROR:  syntax error at or near "select"
LINE 4:   DECLARE cart TEXT:= select get_query('ClientA');
                              ^ 

SQL state: 42601
Character: 36