sql - 比较行并返回与所有记录匹配的列(POSTGRESQL)

Question

我有一张表(tbl_customer)

id | name   | birthday | address  | gender
-------------------------------------------
1  | JOSEPH | 19920413 | NEW YORK | M
2  | JAKE   | 19920413 | LONDON   | M
3  | JOHN   | 19920413 | GERMANY  | M

然后我需要一个查询来比较该表中的所有记录，然后返回与所有记录相同的列。对于上面的示例，结果应该是:

 birthday | gender
-------------------
 19920413 | M
 19920413 | M
 19920413 | M

如果结果看起来像这样，那就更好了..

 column_name | value
--------------------------
 birthday    | 19920413 
 gender      | M

谢谢:)

Answer 1

使用 hstore扩展和 plpgsql :

create function foo(out f_name text, out f_value text) returns setof record language plpgsql immutable as $$
declare
  h hstore;
  r hstore := null;
  n text[];
begin
  for h in select hstore(t.*) from tbl_customer as t loop
    if r is null then
      r := h;
    else
      /* -- To ignore NULLs so the null values does not affects to the result
      select array_agg(key) into n from each(r) where value is null;
      r := r || coalesce(slice(h, n), '');
      select array_agg(key) into n from each(h) where value is null;
      h := h || coalesce(slice(r, n), '');
      */ -- I believe that there is much more elegant solution is possible 
      r := r - akeys(r - h);
      exit when r = '';
    end if;
  end loop;
  raise info '%', r;
  return query select * from each(r);
end $$;

select * from foo();

INFO:  "gender"=>"M", "birthday"=>"19920413"
╔══════════╤══════════╗
║  f_name  │ f_value  ║
╠══════════╪══════════╣
║ gender   │ M        ║
║ birthday │ 19920413 ║
╚══════════╧══════════╝