0

I have written a function which sums columns together (if there is a built in for this, that would also be good to know) and another which calls that function and replaces the sum with a different value if it is zero or null. When trying to call the second function I get the following error:

ERROR:  function enc_sum_columns(numeric[]) does not exist
LINE 1: SELECT            enc_sum_columns(summed_values::numeric[])
HINT:  No function matches the given name and argument types. You might need to add explicit type casts....

The sum columns function is as follows:

create or replace function enc_sum_columns(VARIADIC cols_to_sum numeric[], OUT total numeric)
RETURNS numeric AS
$BODY$
BEGIN
 SELECT INTO total SUM(cols_to_sum[i])
   FROM generate_subscripts(cols_to_sum, 1) g(i);
END;  
$BODY$
  LANGUAGE plpgsql 

And the function that calls it:

CREATE OR REPLACE FUNCTION public.enc_sum_with_choice_for_zero(
    IN replacer numeric,
    VARIADIC summed_values numeric[],
    OUT total numeric)
  RETURNS numeric AS
$BODY$
 BEGIN
   SELECT INTO total enc_sum_columns(summed_values::numeric[]);
   IF total=0 OR total IS NULL THEN
   total = replacer;
   END IF;
 END;
$BODY$
  LANGUAGE plpgsql VOLATILE

And an example of calling the function :

SELECT cov_s_fib, cov_s_cop, enc_sum_with_choice_for_zero(500::numeric, cov_s_fib::numeric, cov_s_cop::numeric) from test_dynamo_with_geom1

Here is some expected output from the query above:

|---------------------|------------------|------------------|
|     cov_s_fib       |     cov_s_cop    |      total       |
|---------------------|------------------|------------------|
|          1          |         3        |          4       |
|---------------------|------------------|------------------|
|          0          |         0        |          500     |
|---------------------|------------------|------------------|
|                     |                  |          500     |
|---------------------|------------------|------------------|
|          0          |                  |          500     |
|---------------------|------------------|------------------|
|                     |        1         |          1       |
|---------------------|------------------|------------------|

Can I not pass a variadic varaible to another function like that? Did I just mess up something syntactically? Originally, I had the code in enc_sum_columns inside of enc_sum_with_choice_for_zero and it worked fine.

  • 1
    Can you provide some sample data and expected result? I get a feeling that what your trying to do can be done in a simpler way, but I'm not sure I understand the scenario. – Lennart Apr 20 '18 at 18:42
  • I added sample data and the two columns being passed to the function into the example query. The sample data is the results of the (slightly) modified query. – Albert Rothman Apr 20 '18 at 18:57
  • 1
    I think dba.stackexchange.com/questions/158078/… answers it – Daniel Vérité Apr 20 '18 at 18:59
  • great, that did the trick! – Albert Rothman Apr 20 '18 at 19:56