Return dynamic table with unknown columns from PL/pgSQL function

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;

This is hard to solve, because SQL demands to know the return type at call time.
Also, a plpgsql function needs to have a well defined return type.

If you choose to return anonymous records, you get what you defined: anonymous records. Postgres does not know what's inside. Therefore, a column definition list is required to decompose the type.

There are various workarounds, depending on exact requirements. If you have any way of knowing the return type at call time, I suggest polymorphic types as outlined in the last chapter of this answer ("Various complete table types"):

Refactor a PL/pgSQL function to return the output of various SELECT queries

But that does not cover adding another column to the return type at runtime inside the function. That's just not possible. I would rethink the whole approach.

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

You have a couple of other problems in your code. See notes below.

Proof of concept

CREATE OR REPLACE FUNCTION f_tbl_plus_infowindow (_tbl regclass)  -- parameter name
  RETURNS void  -- no direct return type
  LANGUAGE plpgsql AS
$func$
DECLARE
   _tmp text := quote_ident(_tbl::text || '_tmp');  -- apending _tmp for temp table
BEGIN
   -- Create temp table only for duration of transaction
   EXECUTE format(
      'CREATE TEMP TABLE %s ON COMMIT DROP AS
       SELECT * FROM %s LIMIT 0'
      ,_tmp, _tbl
     );

   IF EXISTS (
      SELECT 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($$
         ALTER  TABLE %1$s ADD COLUMN infowindow text;
         INSERT INTO %1$s
         SELECT *, 'ID: ' || id::text
         FROM   %2$s$$
        ,_tmp, _tbl
        );
   END IF;
END
$func$;

The call has to be in a single transaction. You may have 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

db<>fiddle here
_{Old sqlfiddle}

Alternatively you could let the temporary table live for the duration of the session. Be wary of naming collisions with repeated calls, though.

Notes

Use parameter names instead of the outdated ALIAS command.

To actually "default" to the current schema, use the simpler query I display. Using regclass does the trick automatically. Details:

Table name as a PostgreSQL function parameter

In addition, this also avoids syntax errors and possible SQL injection from non-standard (or maliciously malformed) table names in your original code.

The code in your ELSE clause wouldn't work at all.

TABLE tbl; is basically short for SELECT * FROM tbl;.

Details on format() in the manual.

Proof of concept

Notes

Recommended topics

Hot tags