I want to try using PostgreSQL to create a function which given a table name, an existing column name, and a new column name, it selects all data from this table, applies a function to this column name, and adds this new column. The caveat is that I can't specify a "fixed" return type in the sense that the structure of the table will always be different. Although if it helps, I am managing some metadata about these tables which I can probably supply.
Anyway, keeping my example simple, here it is in PLPGSQL:
CREATE OR REPLACE FUNCTION add_new_col(from_tbl anyelement,some_col anyelement, new_col_name anyelement) RETURNS SETOF anyelement AS $$ BEGIN RETURN QUERY EXECUTE 'SELECT *,transformation_func('||some_col||') AS '|| new_col_name || ' FROM '||from_tbl; END $$ LANGUAGE plpgsql;
Yeah this is basically a wrapper to a select statement, but I am just using it to highlight my point. If it is really needed to specify the schema of the table which the function will return, can I do this within the function itself? I am not in a position to create a function for each different table structure.
I understand that it is very important for the query to know the table structure in advance. Is there a way to specify it outside the function call? Again, my point is not to create many functions for each table structure.
Is what I am trying to achieve impossible through a function? I get the error
ERROR: could not determine polymorphic type because input has type unknown.
If it helps, I am not limited to PLPGSQL and can even use C. But I really need to specify a function/procedure.
EDIT: I found this Are there other ways to select a dynamic list of columns? to be quite similar to my requirements, I am however quite curious if something changed within these 7 years! I hope to call such a function from a CTE, and I also hope to actually be able to CREATE TABLE the result. I wonder how I can achieve that?
In fact if I could do something with CTAS syntax like CREATE TABLE AS select add_new_col(...) would be very helpful.