Extract constants from EXECUTE queries

Started by ISHAN CHHANGANIabout 1 year ago2 messages
#1ISHAN CHHANGANI
f20200230h@alumni.bits-pilani.ac.in

Hi hackers, I wanted to extract constants hard coded within prepared statements.
ex:-

PREPARE stmt(text, int) as SELECT * FROM test_table WHERE a = $1 AND b = 99 AND c = $2;
EXECUTE stmt('abc', 1);

I can easily get the parameter values ('abc' and 1) from queryDesc->params, but I need to also extract the constant value (99) from within the queryDesc structure during EXECUTE.

I've tried traversing the plan tree like this:

List *constants = NIL;
extract_constants_from_plan(queryDesc->plannedstmt->planTree, &constants);

ListCell *lc;
foreach(lc, constants)
{
Const *c = (Const *) lfirst(lc);

if (!c->constisnull)
{
char *valueStr = NULL;

switch (c->consttype)
{
case BOOLOID:
valueStr = DatumGetBool(c->constvalue) ? "true" : "false";
break;

case INT2OID:
valueStr = psprintf("%d", DatumGetInt16(c->constvalue));
break;

case INT4OID:
valueStr = psprintf("%d", DatumGetInt32(c->constvalue));
break;

case INT8OID:
valueStr = psprintf("%ld", DatumGetInt64(c->constvalue));
break;

case FLOAT4OID:
valueStr = psprintf("%f", DatumGetFloat4(c->constvalue));
break;

case FLOAT8OID:
valueStr = psprintf("%f", DatumGetFloat8(c->constvalue));
break;

case TEXTOID:
case VARCHAROID:
case BPCHAROID:
valueStr = TextDatumGetCString(c->constvalue);
break;

default:
/* For unknown types, try to convert to string using output function */
valueStr = OidOutputFunctionCall(c->consttype, c->constvalue);
break;
}

FILE* fptr = fopen("/Users/abc/test.txt","a");
fprintf(fptr, "Constant value: %s\n", valueStr);
fclose(fptr);
}
}

But this does not seems to work for select statements, though this works for fine for prepared insert statements. Is there a general/ simpler way to do this?

--
The information contained in this electronic communication is intended
solely for the individual(s) or entity to which it is addressed. It may
contain proprietary, confidential and/or legally privileged information.
Any review, retransmission, dissemination, printing, copying or other use
of, or taking any action in reliance on the contents of this information by
person(s) or entities other than the intended recipient is strictly
prohibited and may be unlawful. If you have received this communication in
error, please notify us by responding to this email or telephone and
immediately and permanently delete all copies of this message and any
attachments from your system(s). The contents of this message do not
necessarily represent the views or policies of BITS Pilani.

#2Tomas Vondra
tomas@vondra.me
In reply to: ISHAN CHHANGANI (#1)
Re: Extract constants from EXECUTE queries

On 11/12/24 12:28, ISHAN CHHANGANI wrote:

Hi hackers, I wanted to extract constants hard coded within prepared
statements.

ex:-

 

PREPARE stmt(text, int) as SELECT * FROM test_table WHERE a = $1 AND b =
99 AND c = $2;

EXECUTE stmt('abc', 1);

 

I can easily get the parameter values ('abc' and 1) from queryDesc-

params, but I need to also extract the constant value (99) from within

the queryDesc structure during EXECUTE.

I've tried traversing the plan tree like this:

 

    List*constants =NIL;

    extract_constants_from_plan(/queryDesc/->plannedstmt-

planTree,&constants);

 

    ListCell*lc;

    foreach(lc,constants)

    {

        Const*c =(Const*)lfirst(lc);

 

        if(!c->constisnull)

        {

            /char/*valueStr =NULL;

 

            switch(c->consttype)

            {

                caseBOOLOID:

                    valueStr =DatumGetBool(c->/constvalue/)?"true":"false";

                    break;

 

                caseINT2OID:

                    valueStr =psprintf("%d",DatumGetInt16(c->/constvalue/));

                    break;

 

                caseINT4OID:

                    valueStr =psprintf("%d",DatumGetInt32(c->/constvalue/));

                    break;

 

                caseINT8OID:

                    valueStr =psprintf("%ld",DatumGetInt64(c->/
constvalue/));

                    break;

 

                caseFLOAT4OID:

                    valueStr =psprintf("%f",DatumGetFloat4(c->/
constvalue/));

                    break;

 

                caseFLOAT8OID:

                    valueStr =psprintf("%f",DatumGetFloat8(c->/
constvalue/));

                    break;

 

                caseTEXTOID:

                caseVARCHAROID:

                caseBPCHAROID:

                    valueStr =TextDatumGetCString(c->/constvalue/);

                    break;

 

                default:

/                    /* For unknown types, try to convert to string
using output function *//

                    valueStr =OidOutputFunctionCall(c->/consttype/,c->/
constvalue/);

                    break;

            }

 

            FILE*fptr =fopen("/Users/abc/test.txt","a");

            fprintf(fptr,"Constant value: %s\n",valueStr);

            fclose(fptr);

        }

    }

But this does not seems to work for select statements, though this works
for fine for prepared insert statements. Is there a general/ simpler way
to do this?

I think you'll need to provide much more information. We have no idea
what extract_constants_from_plan() does, it doesn't seem to be a
function defined in Postgres code. Yet it seems to be the part doing the
important stuff.

FWIW I suspect it'd be easier to do this kind of stuff on the parsetree,
i.e. much earlier in query processing.

regards

--
Tomas Vondra