CODEWITHSUNDEEP
postgresql
vipin p
vipin p

Reputation: 111

Array position comparison in PostgreSQL

I have 2 arrays, each has 2 elements.

For example: The arrays are array['M','UM'] and '{0,1}'.

I want to do the following:

update table set value=0 where code='M';
update table set value=1 where code='UM';

Upvotes: 2

Views: 67

Answers (1)

Thanos Darkadakis
Thanos Darkadakis

Reputation: 1729

I haven't tested it. The idea is something like:

CREATE OR REPLACE FUNCTION array_translate(varchar[], int[])
NULL
AS
$$
DECLARE
   arrStr ALIAS FOR $1;
   arrInt ALIAS FOR $2;
BEGIN
   FOR I IN array_lower(arrStr, 1)..array_upper(arrStr, 1) LOOP
    update table set value=arrInt[I] where code=arrStr[I];
   END LOOP;
RETURN NULL;
END;
$$
LANGUAGE plpgsql 
   STABLE 
RETURNS NULL ON NULL INPUT;

Upvotes: 2

Related Questions