Thread: Extract constants from EXECUTE queries

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.

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