Re: Extract constants from EXECUTE queries - Mailing list pgsql-hackers

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