Postgresql - PLPGSQL - How to execute dynamic query?

Parag
June 26, 2024
66 views
0 votes
2 Answers

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

Answers

Chosen as BEST ANSWER

Just wanted to post answer to this question in case anyone else gets stuck. It is not the most ideal solution and probably there is a better way to do it, but this works for me.

I was able to solve this through a two step process:

Created a pl/pgsql function to return a json text based on the client parameter passed to the above function
Wrote a python script to handle json and get the entire dataset as a pandas dataframe (since I wanted the data outside anyway).

Please find below code for pl/pgsql function:

-- FUNCTION: public.fetch_client_position_data(character varying)

-- DROP FUNCTION IF EXISTS public.fetch_client_position_data(character varying);

CREATE OR REPLACE FUNCTION public.fetch_client_position_data(
    p_client_name character varying)
    RETURNS TABLE(position_number character varying, position_name character varying, col_values jsonb) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
DECLARE
    query TEXT;
    col_list TEXT;
BEGIN
    -- Construct the list of columns to select
    SELECT string_agg('p.col_value AS ' || col_name, ', ') INTO col_list
    FROM public.tbl_client_columns
    WHERE client_name = p_client_name;

    -- Construct the dynamic SQL query
    query := 'SELECT 
                  pos.position_number,
                  pos.position_name,
                  jsonb_agg(jsonb_build_object(c.col_name, p.col_value)) as col_values
              FROM public.tbl_positions pos
              JOIN public.tbl_client_positiondata p ON pos.position_number = p.position_number
              JOIN public.tbl_client_columns c ON c.client_name = p.client_name AND c.col_name = p.col_name
              WHERE pos.client_name = $1
              GROUP BY pos.position_number, pos.position_name';

    -- Execute the dynamic SQL query
    RETURN QUERY EXECUTE query USING p_client_name;
END;
$BODY$;

ALTER FUNCTION public.fetch_client_position_data(character varying)
    OWNER TO postgres;

Python:

import pandas as pd
import psycopg2
from psycopg2 import sql
from json2table import convert
import json

# Database connection parameters
db_params = {
    'dbname': 'adhoc',
    'user': 'postgres',
    'password': 'postgres',
    'host': '127.0.0.1'
}

# Establishing the connection
try:
    conn = psycopg2.connect(**db_params)
    print("----Connection to the database established successfully.")
except Exception as e:
    print(f"----Error connecting to the database: {e}")
    exit()

# Creating a cursor object
cur = conn.cursor()

# SQL query to execute
query = sql.SQL("SELECT * FROM fetch_client_position_data(%s)")

# Parameter to pass to the query
params = ('ClientA',)

# Executing the query
try:
    cur.execute(query, params)
    print("----Query executed successfully.")
except Exception as e:
    print(f"----Error executing the query: {e}")
    cur.close()
    conn.close()
    exit()

# Fetching the data
try:
    data = cur.fetchall()
    df = pd.DataFrame(data)
    #print("-----------------------------------------------")
    newdf = pd.DataFrame(df)

    for i, rw in enumerate(df[2]):
        s = str(rw).replace("'",""")
        #print("----value of s: ", s)

        json_data=json.loads(s)
        #print("----json data: ", json_data)
        data_ = {}
        
        for item in json_data:
            for key, value in item.items():
                if key not in data_:
                    data_[key] = []
                data_[key].append(value)
                rowIndex = newdf.index[i]
                newdf.loc[rowIndex, key] = value
        #print("----data_ is ", data_)
        dt = pd.DataFrame.from_dict(data_, orient='index').transpose()

    newdf = newdf.drop(newdf.columns[2], axis=1)
    newdf = newdf.rename({0:'Position ID',1:'Title'}, axis=1)
    print('----Client data: n' , newdf)

except Exception as e:
    print(f"====Error fetching the data: {e}")

# Closing the cursor and connection
cur.close()
conn.close()
print("----Cursor and connection closed.")

Thank you.

(Edit)

- PavelStehule
- May 28, 2024 at 7:28 am
- 0 votes
0
Your example has more issues
1. It is SQL injection vulnerable! – when you want to use text parameters you should to use parameter sanitization functionality – use functions quote_literal or function format with placeholder %L.
2. When you want to use function in expression, don’t use SELECT keyword
```
DECLARE cart TEXT DEFAULT get_query('ClientA');
```
  or
```
DECLARE cart TEXT = get_query('ClientA');
```
  Using SELECT keyword has not sense there. If you want to use it, then you should to use syntax for subquery (SELECT val FROM ...)
3. DO statement cannot to return any result. So using DO as wrapper of query has not sense.
4. plpgsql statement EXECUTE doesn’t return any result – so using it in your context cannot to work. Maybe you want to use RETURN QUERY EXECUTE statement. PL/pgSQL is specific language – try to read documentation first https://www.postgresql.org/docs/current/plpgsql.html
5. DO cannot to return anything, functions or procedures cannot to return dynamic structures. When you want to read result of crosstab you should to use dynamic cursor and then use statement FETCH like http://okbob.blogspot.com/2008/08/using-cursors-for-generating-cross.html
Login or Signup to reply.

Please signup or login to give your own answer.

Click here to cancel reply.

Postgresql – PLPGSQL – How to execute dynamic query?

Answers