I have defined this function, to trim trailing whitespace:
create or replace function trim_trailing_whitespace(value text) returns text as $$ begin return regexp_replace(value, '\s+$', ''); end; $$ language plpgsql immutable;
It works correctly when used in queries like this one:
select trim_trailing_whitespace(SomeColumn), count(*) from MyTable group by SomeColumn;
However, it fails when I try to utilize it with a wildcard, like so:
select trim_trailing_whitespace(*) from MyTable;
LINE 1: select trim_trailing_whitespace(*) from MyTable;
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
How can I execute a function on all columns within a select query? In my case, I want to trim the trailing whitespace off of each column when performing the selection.
If possible, define a second VARIADIC version of your function. Iterate the args with a FOREACH and execute your current function on each argument.
Good example here: https://www.depesz.com/2008/07/31/waiting-for-84-variadic-functions/
You can use dynamic sql or expand * yourself manually.
SELECT FORMAT(
'SELECT %s FROM %I.%I.%I;',
string_agg(
FORMAT(
'trim_trailing_whitespace(%I)',
column_name
),
', '
)
, table_catalog, table_schema, table_name
)
FROM information_schema.columns
WHERE table_catalog = current_catalog
AND table_schema = current_schema
AND table_name = 'MyTable'
GROUP BY table_catalog, table_schema, table_name;