In general code flow:
main() -> SQL_init_db_connection() -> SQL_get_RIAT_size()

globals:
char SelectSizeName[11];
char SelectSizeCommand[150];
int  SelectSizeNParams;
Oid  SelectSizeParamTypes[2];



//SQL_init_db_connection() which initiates connection and creates procedures e.t.c

SQL_init_db_connection():
{

//some code

res = PQexec(conn, "CREATE OR REPLACE PROCEDURE sql_select_size_procedure(hexid text, rtp_in integer, INOUT size_data text) LANGUAGE plpgsql AS $$ BEGIN SELECT size FROM riat WHERE id = hexid AND rtp = rtp_in INTO size_data; END; $$;");
if(PQresultStatus(res) != PGRES_COMMAND_OK)
{
        LOG_ERROR("CREATE sql_select_size_procedure failed! %s", PQerrorMessage(conn));
        SQL_exit_nicely(conn,res);
}
PQclear(res);


// some other code

sprintf(SelectSizeName,"%s","SelectSize");
if(SQL_vsn10) {
        sprintf(SelectSizeCommand,"%s","SELECT size FROM riat WHERE id = $1 AND rtp = $2");
} else {
        sprintf(SelectSizeCommand,"%s","CALL SQL_select_size_procedure($1, $2, NULL)");
}

SelectSizeNParams       = 2;
SelectSizeParamTypes[0] = 25;  // {text}
SelectSizeParamTypes[1] = 23;  // {int}

//some other code

res = PQprepare(conn,
                SelectSizeName,
                SelectSizeCommand,
                SelectSizeNParams,
                SelectSizeParamTypes);
LOG_DEBUG("%s() PREPARE SelectSize PQresultStatus = %s",__func__,PQresStatus(PQresultStatus(res)));
if(PQresultStatus(res) != PGRES_COMMAND_OK)
{
        LOG_ERROR("PREPARE failed for RIAT! %s", PQerrorMessage(conn));
        SQL_exit_nicely(conn,res);
}
PQclear(res);
}

//But in SQL_get_RIAT_size():

/*-------------------------------------------------------------
 return block size from RIAT table for record ID and RTP
 --------------------------------------------------------------*/
void SQL_get_RIAT_size(unsigned int ID, int rtp, int *BlkSize)
{
int blkSz = BLOCK_L4;  // default to 4k if RIAT table does not contain the blocksize for the Record ID and rtp
char *size = NULL;

char *rtpVal = (char *)&rtp;
char hexId[9] = {0};
char *hexIdVal = (char *)&hexId;

const char *paramValues[2] = {hexIdVal, rtpVal};
int paramLengths[2] = {4, sizeof(rtp)};
int paramFormats[2] = {1, 1};
int resultFormat = 1;

int nFields = 0;
int nTuples = 0;
PGresult *res = NULL;

		sprintf (hexId, "%04X", ID);
		LOG_TRACE("%s() - ID %s rtp %i ---------------------------------------",__func__,hexId,rtp);

		if(sql_db) 
		{
			LOG_DEBUG("%s() SelectSizeCommand = %s '%s' %d",__func__,SelectSizeCommand,hexId,rtp);
			LOG_DEBUG("%s() SeelctSizeNParams = %i",__func__,SelectSizeNParams);
			res = PQexecPrepared (conn, 
					      SelectSizeName, 
					      SelectSizeNParams,
					      paramValues, 
					      paramLengths, 
					      paramFormats,
					      resultFormat);
		}
		else
		{
			char queryStmt[100] = {0};
			sprintf (queryStmt, "SELECT size FROM riat WHERE id = '%s' AND rtp = %d", hexId, rtp);
			LOG_DEBUG("%s() FILE path - %s",__func__,queryStmt);
			PQclear(res);
			res = PQexec (conn, queryStmt);
		}
		LOG_DEBUG("%s() res = %s",__func__,PQresStatus(PQresultStatus(res)));

		if ( PQresultStatus(res) != PGRES_TUPLES_OK ) {
			LOG_ERROR("SELECT failed: %s", PQerrorMessage(conn));
			PQclear(res);
		} else {
			nFields = PQnfields(res);
			nTuples = PQntuples(res);
			LOG_DEBUG("%s() nFields=%i nTuples=%i",__func__,nFields,nTuples);
			if (nFields == 0 || nTuples == 0) {
				LOG_ERROR("SELECT failed: NO ROWS/COLUMNS RETURNED");
				PQclear(res);
			} else if ( nTuples > 1 ) {
				LOG_ERROR("More than 1 row is returned");
				PQclear(res);
			} else {
				size = PQgetvalue (res, 0, 0);
				LOG_DEBUG("%s() - size = %s",__func__,size);                  //See the below logs size is showing empty, even if there are records in db

				if ( memcmp (size, "Small", 5) == 0 ) {
					blkSz = BLOCK_L1;
				} else if ( memcmp (size,  "Large", 5) == 0 ) {
					blkSz = BLOCK_L2;
				} else if ( memcmp (size, "4K", 2) == 0)  {
					blkSz = BLOCK_L4;
				}
			}
		}
	*BlkSize = blkSz;
}

Logs:
----

<7>0490218-SQL_init_db_connection() Connection to shc_data database SUCCESSFUL
<6>0490218-[INFO] PostgreSQL Server Version = 1200.16 protocol 3
<7>0490218-SQL_init_db_connection() PREPARE AdvisoryLockData PQresultStatus = PGRES_COMMAND_OK
<7>0490218-SQL_init_db_connection() PREPARE AdvisoryUnLockData PQresultStatus = PGRES_COMMAND_OK

<7>0490218-SQL_init_db_connection() CREATE sql_select_size_procedure PQresultStatus = PGRES_COMMAND_OK
<7>0490218-SQL_init_db_connection() PREPARE SelectSize PQresultStatus = PGRES_COMMAND_OK

<7>0490218-SQL_init_db_connection() CREATE sql_select_data_procedure PQresultStatus = PGRES_COMMAND_OK
<7>0490218-SQL_init_db_connection() PREPARE SelectData PQresultStatus = PGRES_COMMAND_OK
<7>0490218-SQL_init_db_connection() CREATE sql_insert_data_procedure PQresultStatus = PGRES_COMMAND_OK
<7>0490218-SQL_init_db_connection() PREPARE InsertData PQresultStatus = PGRES_COMMAND_OK
<7>0490218-SQL_init_db_connection() CREATE sql_update_data_procedure PQresultStatus = PGRES_COMMAND_OK
<7>0490218-SQL_init_db_connection() PREPARE UpdateData PQresultStatus = PGRES_COMMAND_OK

<7>0490218-SQL_get_RIAT_size() SelectSizeCommand = CALL SQL_select_size_procedure($1, $2, NULL) 'C1C1' 4
<7>0490218-SQL_get_RIAT_size() SeelctSizeNParams = 2
<7>0490218-SQL_get_RIAT_size() res = PGRES_TUPLES_OK
<7>0490218-SQL_get_RIAT_size() nFields=1 nTuples=1
<7>0490218-SQL_get_RIAT_size() - size =                                    //See this line and in DB i could see the record but size is null or empty

DB:
--
[shc_user@cucmtpccu1 ~]$ shc_data
psql (16.1, server 15.4)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

shc_data=> select size from riat where ID='C1C1' and rtp=4;
 size
-------
 Large
(1 row)

//whereas when rtp is 0, it fetches the correct values in the logs and DB

<7>0002301-SQL_get_RIAT_size() SelectSizeCommand = CALL SQL_select_size_procedure($1, $2, NULL) 'D6D4' 0
<7>0002301-SQL_get_RIAT_size() SeelctSizeNParams = 2
<7>0002301-SQL_get_RIAT_size() res = PGRES_TUPLES_OK
<7>0002301-SQL_get_RIAT_size() nFields=1 nTuples=1
<7>0002301-SQL_get_RIAT_size() - size = Large                          //look this line it fetches the correct value of size

shc_data=> select size from riat where ID='D6D4' and rtp=0;
 size
-------
 Large
(1 row)

I dnt understand why the procedure only works for rtp=0.. Is something missing while calling the procedure or some logical errors in the code. Please
assist