Return dynamic table with unknown columns from PL / pgSQL function

Question

I need to create a function that checks the given table if the infowindow field infowindow . If it exists, the function should return select * from table , but if it is not, it should return an additional id field:

 CREATE OR REPLACE FUNCTION getxo_ocx_cincu_preparar_infowindow( guretabla character varying) RETURNS TABLE AS $BODY$ DECLARE tabla ALIAS FOR $1; BEGIN IF EXISTS (SELECT 1 FROM pg_namespace n JOIN pg_class c ON c.relnamespace = n.oid JOIN pg_attribute a ON a.attrelid = c.oid WHERE n.nspname = current_schema() -- default to current schema AND c.relname = tabla AND a.attname = 'infowindow' AND NOT a.attisdropped) THEN RETURN QUERY EXECUTE 'SELECT * from ' ||tabla ; ELSE RETURN QUERY EXECUTE 'SELECT *, ID:' || id::text ||' as infowindow from ' ||tabla ; END IF; END; $BODY$ LANGUAGE plpgsql VOLATILE; 

If I use RETURNS SETOF RECORDS when I select a function, I need to specify columns that I don't know about. And if I use RETURNS TABLE , I also need to specify the fields, so I do not know how to do this.

Answer 1

This is difficult to solve because SQL requires knowing the return type during the call.
In addition, the plpgsql function must have a clearly defined return type.

If you decide to return anonymous entries , you will receive what you defined: anonymous entries. Postgres doesn't know what's inside. Therefore, type expansion requires a column definition list.

There are various workarounds, depending on the exact requirements. If you have any way of knowing the type of return during a call, I suggest polymorphic types , as indicated in the last chapter of this answer (“Various Full Table Types”):
Refactoring the PL / pgSQL function to return the output of various SELECT queries

But this does not apply to adding another column to the return type at runtime inside the function. It is simply not possible. I would rethink the whole approach .

As for your current approach, the closest I can think of will be the temporary table ( or cursor ) that you request in the second call within a single transaction .

You have a couple of other code issues . See notes below.

Proof of concept

 CREATE OR REPLACE FUNCTION f_tbl_plus_infowindow (_tbl regclass) -- regclass! RETURNS void AS -- no direct return type $func$ DECLARE -- appending _tmp for temp table _tmp text := quote_ident(_tbl::text || '_tmp'); BEGIN -- Create temp table only for duration of transaction EXECUTE format( 'CREATE TEMP TABLE %s ON COMMIT DROP AS TABLE %s LIMIT 0', _tmp, _tbl); IF EXISTS ( SELECT 1 FROM pg_attribute a WHERE a.attrelid = _tbl AND a.attname = 'infowindow' AND a.attisdropped = FALSE) THEN EXECUTE format('INSERT INTO %s SELECT * FROM %s', _tmp, _tbl); ELSE -- This is assuming a NOT NULL column named "id"! EXECUTE format($x$ ALTER TABLE %1$s ADD COLUMN infowindow text; INSERT INTO %1$s SELECT *, 'ID: ' || id::text FROM %2$s $x$ ,_tmp, _tbl); END IF; END $func$ LANGUAGE plpgsql; 

The call must consist of one transaction. You may need to start an explicit transaction, depending on your client.

 BEGIN; SELECT f_tbl_plus_infowindow ('tbl'); SELECT * FROM tbl_tmp; -- do something with the returned rows ROLLBACK; -- or COMMIT, does not matter here 

SQL Fiddle

Alternatively, you can let the temporary table live for the entire session. However, be careful when colliding names with repeated calls.

Notes

• Use parameter names instead of the obsolete ALIAS command .

• In fact, the “default” for the current schema is using the simpler query that I show. Using regclass does the trick automatically. Details:

  • Table name as parameter of PostgreSQL function

  In addition, it also avoids syntax errors and possible SQL injection from non-standard (or malicious) table names in your source code.

• The code in your ELSE does not work at all.

• TABLE tbl; basically abbreviated for SELECT * FROM tbl; .

• Details are in format() in the manual.