0

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.

  • Side-note: Your query 'SELECT *,AVG('||some_col||') AS '|| new_col_name || ' FROM '||from_tbl is not valid SQL – Lennart Mar 21 at 6:18
  • ah my bad, i meant a reference to any generic function. – Zeruno Mar 21 at 6:25
  • I don't at all understand your scenario, but you can declare void as return type for your function. – Lennart Mar 21 at 6:26
  • I would like to be able to use CTAS syntax on the function. I don't think that would work then? – Zeruno Mar 21 at 6:27
  • I assume this is some kind of migration, and I suggest that you do that from your favorite host language that can access the database. The above is really stretching the limit for the purpose of a function – Lennart Mar 21 at 6:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.