Multi-value dataset parameter - what exactly is being sent to sql direct query?

[dan.carlson]

Hi
im trying to debug my dataset parameter. I need to know what is being sent as the actual value from the multi-select control to my sql query. Or where can i find the logs containing the error?

for example - my parameter is a list of tags:
tag1
tag2
etc
if i select both tags, what is the value of the dataset parameter?

is it
‘tag1’, ‘tag2’ ?

or is it
‘tag1’,
‘tag2’

or is it
‘tag1,tag2’

thanks in advance

when i select a single value, my parameter works. when i select more than one it fails with the following error:

[Giridhar.Prabhu]

Hi @dan.carlson

I guess it is a list of strings in quotes separated by commas.

Is your definition in the dataset similar to the following?

If yes, it should work. Can you share additional details of your dataset setting for us to review and comment?

[dan.carlson]

Thanks Giridhar,
i can write custom SQL with an IN clause and it does work, however my query is more complex and so i am writing as a function that returns a table. This works with a single parameter but i need to know what the value is so i can update my function to accept the same. I assumed it was a comma delimited string, but maybe it is a string delimited by new line?

here is my dataset query
parameter tags. Value multi-select

select * from dx.get_jobs_with_all_tags(<<$tags>>);

which i thought would end up like this:
select * from dx.get_jobs_with_all_tags('ONGOING, HOME_GENERAL, OLDER_ADULT');

And here is a very simplified version of my function
The key is that i need to convert the parameters to an array so i can get a count of the arg’s for the HAVING clause

-- Function to find jobs that have ALL specified tags (AND condition)
-- Takes a comma-delimited string of tags and returns jobs that have all of them

CREATE OR REPLACE FUNCTION dx.get_jobs_with_all_tags(tag_string TEXT)
RETURNS TABLE (
    job_post_id UUID,
    name TEXT,
    hours INT,
    job_status TEXT,
    geo_data_id UUID
) AS $$
DECLARE
    tag_array TEXT[];
    tag_count INT;
BEGIN
    -- Convert comma-delimited string to array and trim whitespace
    tag_array := string_to_array(tag_string, ',');
    tag_array := array(SELECT trim(unnest(tag_array)));

    -- Get the count of tags for the HAVING clause
    tag_count := array_length(tag_array, 1);

    -- Return jobs that have all specified tags
    RETURN QUERY
    SELECT
        jt.job_post_id,
        jt.name,
        jt.hours,
        jt.job_status,
        jt.geo_data_id
    FROM job_tag jt
    WHERE jt.tag = ANY(tag_array)
    GROUP BY jt.job_post_id, jt.name, jt.hours, jt.job_status, jt.geo_data_id
    HAVING COUNT(DISTINCT jt.tag) = tag_count;
END;
$$ LANGUAGE plpgsql;

Thanks!

[dan.carlson]

@Giridhar.Prabhu
i was able to find the failing query in CloudWatch. It looks like each string was being treated as a separate argument, instead of as a single comma delimited string.

So in my case, if this helps anyone, was a simple fix

-- Function to find jobs that have ALL specified tags (AND condition)
-- Takes a variable number of string of tags and returns jobs that have all of them

CREATE OR REPLACE FUNCTION dx.get_jobs_with_all_tags(VARIADIC tag_array TEXT[])
RETURNS TABLE (
    job_post_id UUID,
    name TEXT,
    hours INT,
    job_status TEXT,
    geo_data_id UUID
) AS $$
DECLARE
    tag_count INT;
BEGIN
    -- trim whitespace (just in case - may not be needed)
    tag_array := array(SELECT trim(unnest(tag_array)));

    -- Get the count of tags for the HAVING clause
    tag_count := array_length(tag_array, 1);

    -- Return jobs that have all specified tags
    RETURN QUERY
    SELECT
        jt.job_post_id,
        jt.name,
        jt.hours,
        jt.job_status,
        jt.geo_data_id
    FROM job_tag jt
    WHERE jt.tag = ANY(tag_array)
    GROUP BY jt.job_post_id, jt.name, jt.hours, jt.job_status, jt.geo_data_id
    HAVING COUNT(DISTINCT jt.tag) = tag_count;
END;
$$ LANGUAGE plpgsql;