john1717 john1717 - 21 days ago 5
SQL Question

Compare rows and return column that matches with all the record (POSTGRESQL)

I have a table (tbl_customer)

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


then I need a query that will compare all records in this table then return columns that is identical to all the records..for the example above result should be:

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


or much better if the result should look something like this..

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


thanks :)

Answer

Using hstore extension and 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;
begin
  for h in select hstore(t.*) from tbl_customer as t loop
    if r is null then
      r := h;
    else
      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 ║
╚══════════╧══════════╝