Creating filter for aws_pricing_product using jsonb_build_object is not working

[mmaravich]

I am trying to dynamically create the filter for querying the aws_pricing_product table. I was experimenting with ways of creating the filter dynamically and discovered it does not really work.

For example, if I do this, everything works as expected and returns about 21 rows in a second:

select 
	*
from 
	aws_pricing_product
where
	service_code = 'AmazonEC2'
	AND filters = '{"tenancy": "Shared",
	"locationType": "AWS Region",
	"preInstalledSw": "NA",
	"capacitystatus": "Used",
	"instanceType": "r7i.xlarge",
	"regionCode": "us-east-1",
	"operatingSystem": "Linux"}'::jsonb;

But, suppose I want to use the jsonb_build_object function to dynamically build a filter (this example has static values, but I could easily use values from JOINs or subselects, or whatever):

select 
	*
from 
	aws_pricing_product
where
	service_code = 'AmazonEC2'
	AND filters = jsonb_build_object(
		'tenancy' , 'Shared',
		'locationType' , 'AWS Region',
		'preInstalledSw' , 'NA',
		'capacitystatus', 'Used',
		'instanceType' , 'r7i.xlarge',
		'regionCode' , 'us-east-1',
		'operatingSystem', 'Linux'
		)

This query will timeout. If I am reading the debug logs correctly (I am new at trying to troubleshooting), this generates requests that do not contain any filter - and of course that makes for a lot of requests...

Steamppipe version: Steampipe v2.0.1
AWS Plugin version: 1.17.0

[ParthaI]

Hi @mmaravich,
Apologies for the delayed response, and thank you for your patience. I was able to successfully replicate the issue on my end. Based on my findings, it doesn’t appear to be a problem with the plugin itself. The root cause seems to be related to how PostgreSQL parses the query parameters—this might be something for our core team to look into.

@pskrbasu, would you mind taking a look when you have a chance?

Here are my observations:

• Without jsonb_build_object: The filter value is being passed correctly—I verified this by logging the value in the plugin.
• With jsonb_build_object: The filter value appears to be empty.

[pskrbasu]

Hi @mmaravich, Thank you for raising this and bringing it to our attention. This is currently a limitation of the Steampipe Postgres FDW; it does not support functions.

We will look into resolving this(adding support for functions), but currently, we don't have an exact timeline for when this feature could be released.

[pskrbasu]

Summary

Add support for PostgreSQL functions.