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.

4
  • 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. Apr 20, 2018 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. Apr 20, 2018 at 18:57
  • 1
    I think dba.stackexchange.com/questions/158078/… answers it Apr 20, 2018 at 18:59
  • great, that did the trick! Apr 20, 2018 at 19:56

0

Browse other questions tagged or ask your own question.