Return dynamic table with unknown columns from PL/pgSQL function
dynamic-sql, plpgsql, polymorphism, postgresql, return-type
Solution
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.
Problem
I need to create a function that checks on a given table if the `infowindow` field exists. If it exists the function must return `select * from table` but if it does not, it must 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 do the select to the function I need to specify the columns, which I don't know. And if I use `RETURNS TABLE` I need to specify the fields too, so I don't know how to do it.