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

Postgresql – Function to return a specific column from a table based on value

I have a query (PostgreSQL 15.4) like this:

Select p."prodId", p."prodDesc", p."areaName", x."location1" as "Location"
From ops."Products" p
Inner Join ops."Locations" x on p."prodId" = x."prodId" 
                             and p."areaName" = x."areaName1"

Here’s the caveat: in the table Locations, there are three locations, location1, location2, and location3. In addition, there are three columns areaName1, areaName2, and areaName3.

I need to return the location column as either location1, location2, or location3, based on the column areaName in the Products table being equal to one of the corresponding areaName columns in Locations: areaName1, areaName2, or areaName3.

My thinking was to be able to use some sort of function where I could join the function to the rest of my query and have the function handle all of the above. In a land and time far away, I probably would’ve handled this as dynamic SQL, but wondering if there is a better way to go?

Tags:

2

Answers


  1. 0

    You should change the locations table. Don’t have the three (areaname, location) pairs in one row, have them in three rows.

    You can do that inline in the query, but it will be slower (and less maintainable) than just fixing the data structure.

    SELECT
  p."prodId",
  p."prodDesc",
  p."areaName",
  l."location"
FROM
  ops."Products"   p
INNER JOIN
(
  SELECT "prodId", 1 AS rowid, "areaName1" AS "areaName", location1 AS location FROM ops."Locations"
  UNION ALL
  SELECT "prodId", 2 AS rowid, "areaName2" AS "areaName", location2 AS location FROM ops."Locations"
  UNION ALL
  SELECT "prodId", 3 AS rowid, "areaName3" AS "areaName", location3 AS location FROM ops."Locations"
)
  AS l
    ON  p."prodId"   = l."prodId" 
    AND p."areaName" = l."areaName";
    Login or Signup to reply.
  2. 0

    You can use a case to switch between them and an in/=any(array[]) to join on any one.
    demo at db-fiddle

    Select p."prodId"
     , p."prodDesc"
     , p."areaName"
     , case p."areaName" 
       when x."areaName1" then x."location1" 
       when x."areaName2" then x."location2" 
       when x."areaName3" then x."location3"
       end as "Location"
From ops."Products"  as p
Join ops."Locations" as x 
  on p."prodId"   =     x."prodId" 
 and p."areaName" in (  x."areaName1"
                      , x."areaName2"
                      , x."areaName3");
    prodId prodDesc areaName Location
    1 prodDesc1 areaName1_1 Location1_1
    2 prodDesc2 areaName2 Location2
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search