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