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

我有一张桌子（待定）

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

谢谢：）

最佳答案

使用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 ║
╚══════════╧══════════╝