Náhrada hash tabulek

Z PostgreSQL
Přejít na: navigace, hledání

Vhodné pouze pro malý počet prvků cca do 100.

create type keyvalue as (key text, value text);

create or replace function keyvalue_in(text) 
returns keyvalue as $$
declare 
  x text[] := string_to_array($1,'=>'); 
  r keyvalue; 
begin 
  if array_upper(x,1) <> 2 then
    raise exception 'unexpected format for keyvalue value "%"', $1;
  end if;
  r.key := lower(x[1]); r.value := x[2]; 
  return r; 
end; 
$$ language plpgsql immutable strict;

create or replace function keyvalue(variadic varchar[]) 
returns keyvalue[] as $$
  select array(select keyvalue_in(s) 
                  from unnest($1) g(s)
              )::keyvalue[]; 
$$ language sql;

postgres=# select keyvalue('a=>10','b=>20','c=>40');
           keyvalue           
------------------------------
 {"(a,10)","(b,20)","(c,40)"}
(1 row)

create or replace function valueof(keyvalue[], text) 
returns text as $$
  select $1[i].value 
     from generate_subscripts($1,1) g(i) 
    where $1[i].key = $2 
    limit 1
$$ language sql;

postgres=# select valueof(keyvalue('a=>10','b=>20','c=>40'),'a');
 valueof 
---------
 10
(1 row)

postgres=# select valueof(array(select (proname, prosrc)::keyvalue 
                                   from pg_proc
                               ),
                          'valueof');
                  valueof                  
-------------------------------------------
 
   select $1[i].value 
      from generate_subscripts($1,1) g(i) 
     where $1[i].key = $2 
     limit 1
 
(1 row)