PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Avoid repetition of strings in SQL query

I have a SQL query where the list of strings is repeated twice: ('foo', 'bar', 'baz', 'bletch'). What is the most maintainable method to avoid such repetition and make this code more DRY?

I am not optimizing query performance, only maintainability.

select
    *
from
    gizmo_details
where
    _gizmo_name in ('foo', 'bar', 'baz', 'bletch')
    or gizmo_id in
    (select id from gizmos where
        gizmo_name in ('foo', 'bar', 'baz', 'bletch'));
Tags:

3

Answers


  1. 0

    You can move that to an array literal in a single-row subquery and re-use its name: demo

    select * from gizmo_details, 
(select array['foo', 'bar', 'baz', 'bletch']::text[] string_arr)_ 
where _gizmo_name=any(string_arr) 
  or gizmo_id in(select id from gizmos where gizmo_name=any(string_arr));

    To hide it away completely, you can save that to a temp table, constant-returning function or a custom variable, then join/read that:

    set my.var='{foo,bar,baz,bletch}';

select * from gizmo_details 
where _gizmo_name=any(current_setting('my.var')::text[]) 
  or gizmo_id in(select id from gizmos 
                 where gizmo_name=any(current_setting('my.var')::text[]));

    Psql also lets you set it as its own variable:

    set string_arr $$'{foo,bar,baz,bletch}'$$

select * from gizmo_details
where _gizmo_name=any(:string_arr)
  or gizmo_id in(select id from gizmos where gizmo_name=any(:string_arr));
    Login or Signup to reply.
  2. 0

    Using a CTE:

    WITH knownGizmos AS (
    SELECT name
    FROM
    (
        VALUES
            ( 'foo' ), ( 'bar' ), ( 'baz' ), ( 'bletch' )
    ) gn ( name )
)
SELECT
    *
FROM
    gizmo_details AS gd
    INNER JOIN gizmos AS g ON g.id = gd.gizmo_id

    INNER JOIN knownGizmos ON g.gizmo_name = knownGizmos.name

    Unfortunately ISO SQL’s VALUES() table-constructor is painfully verbose compared to a Postgres array literal.

    An alternative to using VALUES() is a good ol’ fashioned UNION:

    WITH knownGizmos AS (
    SELECT 'foo'    AS name UNION
    SELECT 'bar'    AS name UNION
    SELECT 'baz'    AS name UNION
    SELECT 'bletch' AS name
)
SELECT
    *
FROM
    gizmo_details AS gd
    INNER JOIN gizmos AS g ON g.id = gd.gizmo_id

    INNER JOIN knownGizmos ON g.gizmo_name = knownGizmos.name
    Login or Signup to reply.
  3. 0

    With a CTE it would look like this

    with t as (
  select array['foo', 'bar', 'baz', 'bletch'] arr)
select gd.* from gizmo_details gd, t
where _gizmo_name = any (t.arr)
or gizmo_id 
in (select id from gizmos where gizmo_name = any(t.arr));
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search