How return SELECT * in my plpgsql

Question

i need help in my plpgsql, must return a temporary table that has dynamic columns, how can I do this? as the name of the columns may vary, I do not know how to finish this procedure Sorry, google translator :D

CREATE OR REPLACE FUNCTION getreport(reportid INTEGER, userId VARCHAR)
RETURNS SETOF RECORD AS 
$$
    DECLARE
        recordResultadoFinal RECORD;            
        recordResultadoNomeEspecificos RECORD;      
        varGetSqlRelatorio VARCHAR;         
        varAreaId queryreports.f_area%TYPE;
        varClientId queryreports.f_client%TYPE;
        varTableNameTemp VARCHAR := 'temp'||userId; 
        varSqlAlterTable VARCHAR := '';
        varSqlUpdateTemp VARCHAR := '';
        varNomeColunaSpecificData VARCHAR := '';
    BEGIN
        SELECT f_sql,f_area,f_client INTO varGetSqlRelatorio,varAreaId,varClientId FROM queryreports WHERE f_id = reportid;
        EXECUTE 'DROP TABLE IF EXISTS '||varTableNameTemp;
        EXECUTE 'CREATE TEMP TABLE '||varTableNameTemp||' AS '||varGetSqlRelatorio;
        EXECUTE 'CREATE INDEX processid_idx ON '||varTableNameTemp||' USING btree (processid)';
        FOR recordResultadoNomeEspecificos IN EXECUTE '
                                SELECT DISTINCT cs.f_id as idcoluna, cs.f_name as nomecoluna, cs.f_type as tipodado
                                FROM clientspecifics cs
                                INNER JOIN clientspecificdatas csd ON (cs.f_id = csd.f_clientspecific AND csd.f_process IN (SELECT processid FROM '||varTableNameTemp||'))
                                ORDER BY 2
                                  '
        LOOP
            varSqlAlterTable := varSqlAlterTable||' ALTER TABLE '||varTableNameTemp||' ADD COLUMN specific_'||recordResultadoNomeEspecificos.idcoluna||' varchar;';

            IF (recordResultadoNomeEspecificos.tipodado = 1) THEN varNomeColunaSpecificData := 'f_text';
            ELSIF (recordResultadoNomeEspecificos.tipodado = 2) THEN varNomeColunaSpecificData := 'f_name';
            ELSIF (recordResultadoNomeEspecificos.tipodado = 3) THEN varNomeColunaSpecificData := 'f_date';
            ELSIF (recordResultadoNomeEspecificos.tipodado = 4) THEN varNomeColunaSpecificData := 'f_value';
            ELSIF (recordResultadoNomeEspecificos.tipodado = 5) THEN varNomeColunaSpecificData := 'f_text';
            END IF;
            varSqlUpdateTemp := varSqlUpdateTemp||' UPDATE '||varTableNameTemp||' SET specific_'||recordResultadoNomeEspecificos.idcoluna||' = csd.'||varNomeColunaSpecificData||'
                                FROM clientspecificdatas csd
                                WHERE csd.f_process = processid 
                                AND csd.f_clientspecific = '||recordResultadoNomeEspecificos.idcoluna||';';


        END LOOP;
        EXECUTE varSqlAlterTable;
        EXECUTE varSqlUpdateTemp;
        RETURN QUERY EXECUTE 'SELECT * FROM '||varTableNameTemp;
    END;
$$ LANGUAGE 'plpgsql';

as far a I know, it's not possible in PostgreSQL, until you specify columns when doing select from function — Roman Pekar, Aug 30 '13 at 20:34
If you're sure about what data will be returned you can set a temporary table in the function - `CREATE OR REPLACE FUNCTION getreport(reportid INTEGER, userId VARCHAR) RETURNS TABLE(column1 integer,column2 integer etc...` — Lucas, Aug 30 '13 at 22:32
_must return a temporary table that has dynamic columns_: that's what your function does, already. You should tell about the context of the call because this is where it really happens. — Daniel Vérité, Aug 30 '13 at 22:46
The question remains unclear. Maybe the related questions [here](http://stackoverflow.com/questions/11740256/refactor-a-pl-pgsql-function-to-return-the-output-of-various-select-queries/11751557) and [here](http://stackoverflow.com/questions/8605174/postgresql-error-42601-a-column-definition-list-is-required-for-functions-ret/8611675) are of help. — Erwin Brandstetter, Oct 21 '13 at 18:44

score 0 · Answer 1 · answered Nov 13 '13 at 02:47

You have a few options:

You can return a refcursor and then fetch from that. This only works in transactions.
You can return an xml document and then process that in your app
You can return JSON or HSTORE.
You can return setof record and specify column lists in the function declaration but this is ugly and brittle.

The problem is that PostgreSQL needs to know return types when planning a query. this means you can't have things like jagged rows or dynamic numbers of rows. These have to be wrapped in something the planner can accommodate.

How return SELECT * in my plpgsql

1 Answers1