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
