DocuSign Insight -How to find the number of values in a specific field?

Issue

You need to know all the different ContractingParties in your data set, or there is another field that you want a count or an enumeration of all the potential values.

 

Solution

You can run the following to get the data you require.

CREATE OR REPLACE FUNCTION contractflatcountfunction()
RETURNS void AS
$func$
DECLARE
sql varchar;
sqlbegin varchar := ‘SELECT *‘;
countcolumns varchar;
sqlend varchar := ’ FROM CONTRACT_FLAT’;
columns cursor for select column_name from information_schema.columns where table_name = ‘contract_flat’ and ordinal_position > 22;
BEGIN
sql := sqlbegin;
FOR c in columns LOOP
sql := sql || ‘, array_length(’ || replace(replace(c::varchar, ‘)’,‘’),‘(’,‘’) || ‘,1) as count_of_’ || replace(replace(c::varchar, ‘)’,‘’),‘(’,‘’);
END LOOP;
sql := sql || sqlend;
EXECUTE ‘create or replace view conflatcounts as ’ || sql;
END;
$func$ LANGUAGE plpgsql;
 
Then you can just make a call to select contractflatcountfunction();