How to implement PostgreSQL function which gets the whole row?

Question

For instance I want to count null fields for each row in a table.

What argument type declaration should I use?

I tried composite type (table name as a type):

CREATE FUNCTION count_null (row my_table)
  RETURNS INTEGER
AS $$
  return len([x for x in row if x is None])
$$ LANGUAGE plpythonu;

But it doesn't match argument type if I call it like:

SELECT count_null(*) FROM my_table;

score 1 · Answer 1 · answered May 05 '17 at 12:39

Assuming you are using Postgres >= 9.3, you can accomplish this using a json conversion, like so:

create or replace function count_null(
    _r json
) returns integer as $$
    select
        count(1)::integer
    from
        (
            select
                row_to_json(json_each(_r)) as condensed_record
        ) as __base
    where
        condensed_record->>'value' is null;
$$ language sql immutable;

---------

select
    count_null(row_to_json(my_table))
from
    my_table;

This has the added benefit of using language SQL rather than plpythonu, so the query planner can optimise it to some degree.

How to implement PostgreSQL function which gets the whole row?

1 Answers1