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.
