Re: CTE, lateral or jsonb_object_agg ? - Mailing list pgsql-general

From Dominique Devienne
Subject Re: CTE, lateral or jsonb_object_agg ?
Date
Msg-id CAFCRh-_R6GxFm52ixS4GJPqWWA0A7XFNtYytqJoMVS0uYXOe+A@mail.gmail.com
Whole thread Raw
In response to CTE, lateral or jsonb_object_agg ?  (Marcos Pegoraro <marcos@f10.com.br>)
List pgsql-general
On Sat, May 20, 2023 at 4:43 PM Marcos Pegoraro <marcos@f10.com.br> wrote:
I have a table like pg_settings, so records have name and value.

Hi. Maybe I'm missing something, but why aren't you simply doing:

    select name, varvalue from sys_var where name = any($1)

and binding your 4 (in your examples) or 10 or 1 name(s) as a text array (i.e. text[])?
With a prepared statement you plan only once.

You can also avoid selecting names by unnest'ing the text-array with ordinality
and join+sort on it, to get the values in name order (i.e. same order as $1 you control).
See my recent thread where I learned about that technique.

Generating SQL text dynamically when you can avoid it with proper binding
and appropriate SQL is leaving performance on the table IMHO. --DD

pgsql-general by date:

Previous
From: Tiffany Thang
Date:
Subject: Re: Profiling a function call
Next
From: Ron
Date:
Subject: Re: DBeaver postgres localhost access