Re: Table lookup in Postgresql

Mehmet Yunt wrote
> Hi All
>
> I have a table that contains arrays of integers. These integers
> are code names for quantities. Each quantity (hence integer) has also
> an associated index in a bigger array.
>
> For example
>
> Row in Table A
> {5,6,7,8,9}
>
> Table B contains the look up for the indices
>
> Val Idx
> 5 10
> 6 1
> 7 9
> 8 2
> 9 3
>
> What I would like to do is to create a row in table C for all rows in
> table
> A that contains a vector where the indices corresponding to the elements
> in
> the A table are 1 and the remainder are 0 like
>
> { 0,1, 1,0,0,1,0,0,1,1}
>
> where the maximum length of this vector is known
>
> How can I best do it in Postgresql? Should I loop using PL/PgSQL?
>
> Thanks
> Mehmet

You example result does not appear to match the true result given the input
data you have provided. The true result should be {1,1,1,0,0,0,0,0,1,1}
assuming the maximum index is 10.

WITH tbl_a AS ( SELECT ARRAY[5,6,7,8,9]::integer[] AS actuals ) /* your
array input above */
, tbl_b AS ( SELECT * FROM (VALUES(5,10),(6,1),(7,9),(8,2),(9,3)) t_b
(in_int, out_int) ) /* the mapping table above */
, tbl_master AS (SELECT generate_series(1,10) AS out_int_master) /*
integer sequence to get known indexes */
, tbl_combine_raw AS (

SELECT *
FROM (SELECT * FROM tbl_a CROSS JOIN tbl_b WHERE in_int = ANY(actuals))
actuals_lookup
RIGHT JOIN tbl_master ON (out_int_master = out_int)

) /* use " = ANY " to select rows from the mapping that exist in the input
array and also right-join so that every index from master is represented.
*/
SELECT ARRAY_AGG(CASE WHEN out_int IS NULL THEN 0 ELSE 1 END ORDER BY
out_int_master) AS mapped
FROM tbl_combine_raw
/* use a case statement to query whether each index is matched (1) or not
(0) and combined the results using array_agg with an order by clause to make
sure ordering is correct

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Table-lookup-in-Postgresql-tp5749327p5749345.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.