PSQL queries creating multiple select queries based on condition to filter out values-CodePudding

WITH final AS (
    SELECT 'a' AS gname, 'b' AS pname, 'c' AS name, 1 AS gid, 2 AS pid, 3 AS did, 3 AS inp
    UNION
    SELECT 'a1' AS gname, 'b1' AS pname, 'c1' AS name, 11 AS gid, 21 AS pid, 31 AS did, 3 AS inp
)

Now I want to write a query depending on the inp column value

SELECT 
    (CASE final.inp 
    WHEN 1
        THEN 
        (SELECT DISTINCT(final.gid) AS id, final.gname as name FROM
        final ORDER BY final.gname)
    WHEN 2
        THEN 
        (SELECT DISTINCT(final.pid) AS id, final.pname as name FROM
        final WHERE final.gid = $1 ORDER BY final.pname)
    WHEN 3 
        THEN 
        (SELECT DISTINCT(final.did) AS id, final.name as name FROM
        final WHERE final.pid = $1 ORDER BY final.name)

    END)
        FROM (%s) final;

I know this is not a correct way of writing it.

But this will give you an idea what I am trying to do.

if inp = 1 -> get unique gid's and gname and order by gname

if inp = 2 -> get unique pid's and pname where gid = and order by pname.

I want to write this logic in psql.

I know case When can have only one column so I have to create two cases for a single final.inp=1 for name and id.

I ain't able to write this logic because of this part

(SELECT DISTINCT(final.pid) AS id, final.pname as name FROM
final WHERE final.gid = $1 ORDER BY final.pname)

WHERE ORDER BY <--- and passing the same final value to it. I am not able to implement it. Can anyone help?

CodePudding user response：

Only assumption

select distinct 
case when inp = 1 then gid
       when inp = 2 then pid
       when inp = 3 then did
  end new_id,
  case when inp = 1 then gname
       when inp = 2 then pname
       when inp = 3 then name
  end as name
from final
order by 2

DB Fiddle playground