a question about dynamic queries

[sentriz]

hello again 👋

in my application a feature would be to allow the user to sort the results of a search by some or multiple columns. eg

create table screenshots (
   id        serial      primary key,
   timestamp timestamptz not null,
   width     int         not null
);

the user may like to search screenshots by timestamp ascending, width descending, or whatever other column

as far as I know with postgres, it is not possible to make a prepared query for the order by clause
eg ORDER BY $1
https://www.postgresql-archive.org/ORDER-BY-in-prepared-statements-td5834944.html

and so since we have no idea of a query builder etc here, one idea might be to do

-- name: SearchScreenshots :many
select
  *
from
  screenshots
order by
  case
    when pggen.arg('timestamp_asc')  then timestamp end asc
    when pggen.arg('timestamp_desc') then timestamp end desc
    when pggen.arg('width_asc')      then width end asc
    when pggen.arg('width_desc')     then width end desc
  end;

to pass a bunch of bools to the generated query and switch case inside of it ( though this performance of doing this might not be great - but not sure )

so my question is, what would you do in this situation?
perhaps there is a better trick? or a change to pggen?

thanks!

[jschaf]

For the short term, the easiest option is to use 2 separate queries, one for each column. SQL has pretty limited abstractions for dealing with idents. Then use an if-statement in Go to choose which query to run.

Alternately, you can use pl/pgsql to create a function that takes a string you use to format as an ident in a query.

Long term, I want to add something like pggen.ident to cover this use case. Similarly, I'd like something for arbitrary predicates.