looping through a table type variable

Question

I have a function that has one parameter of a table type, i want to loop through all the fields and return the column that has a null or empty value.

CREATE OR REPLACE FUNCTION toned.function_validate(varmember tbl_member)
  RETURNS text AS
$BODY$
...

The definition of tbl_member below

CREATE TABLE toned.tbl_member
(
  code smallint NOT NULL DEFAULT nextval('toned.seq_code'::regclass),
  name text NOT NULL,
  email text,
  address text,
  city text,
  country text
);

can anyone help me to loop through the variable? I dont know how to refer to the field names. can i do something like varmember[0] ??

Answer 1

Typically I do as much as I can with declarative stuff in the table definition. Then I may use an exception block if I can't just trust my application to detect and handle standard exceptions normally.

Doing what you are trying to do in plpgsql is likely to be extremely difficult because of the limits of plpgsql expressiveness. While we can find the attributes, generating a query to check them is not possible without resorting to temporary tables or the like. So let's talk about temporary tables as an option. You could:

CREATE TEMPORARY TABLE temp_tblmember OF TYPE toned.tbl_member;

Then you could insert this into that temporary table.

You could then look up the attributes in pg_attribute (joins pg_class on pg_attribute.relid = pg_class.oid). Then you could use EXECUTE to check the attributes.

Unfortunately this isn't likely to perform very well and it is likely to be very cumbersome to code.

Fortunately you could look at using something pl/perl to do the looping, where you can get access, and then loop through the keys of the hashref.

CREATE OR REPLACE FUNCTION validate(tbl_member) returns int immutable language plperl as
$$
  my ($ref) = @_;
  for my $k (keys %$ref){
      if (!defined $ref->{$k}){
          elog(ERROR, 'tbl_member.' . $k . ' not defined!');
       }
  }
   return 1;
$$;

Answer 2

In the SQL world the columns of a table, view, table variable, materialized view...etc are named and called by their specific name. Eg:

Select *
from toned.tbl_member as tm
where tm.**email** = 'x@y.com'

There's no dynamic, array like name.

PS: in that case I'd return a table with a structure like the following:

select
    case when name is null then 1 else 0 end as NameIsNull,
    case when email is null then 1 else 0 end as EmailIsNull,
    case when address is null then 1 else 0 end as AddressIsNull,
    case when city is null then 1 else 0 end as CityIsNull,
    case when country is null then 1 else 0 end as CountryIsNull
from dbo.tbl_member

All columns returned all the time, and only the value 1 for the case the column is null.

A second way would be to build a string with only column names that have null value for the input row. And in code you parse the string and get the list of columns split by some delimiter.

Another way would be to return a base 2 number. Eg: when all columns are not null, then return 11111, else, when only email is not null, return 11011..and so on.

But, what is more important, why would you need to input a record and return only NULL columns? Why is it needed?