Use text output from function as new request

Continuing from the previous case , helped by @Erwin Brandstetter and @Craig Ringer, I have corrected my code to be the following. Note that my function is myresult()

now outputting text

, not the table (as in the first case, it makes no sense to output the table object, since we will need to define all of its columns in front, which basically defies the whole purpose):

CREATE OR REPLACE FUNCTION myresult(mytable text, myprefix text)
RETURNS text AS 
$func$
DECLARE
   myoneliner text;
BEGIN
   SELECT INTO myoneliner  
          'SELECT '
        || string_agg(quote_ident(column_name::text), ',' ORDER BY column_name)
        || ' FROM ' || quote_ident(mytable)
   FROM   information_schema.columns
   WHERE  table_name = mytable
   AND    column_name LIKE myprefix||'%'
   AND    table_schema = 'public';  -- schema name; might be another param

   RAISE NOTICE 'My additional text: %', myoneliner;
   RETURN myoneliner;
END
$func$ LANGUAGE plpgsql;

      

        
        
        
      

    

Call:

select myresult('dkj_p_k27ac','enri');   

      

        
        
        
      

    

When doing the above procedure, I am getting a text string which is basically a request. I'll refer to it below as "oneliner-output", just for the sake of simplicity.
"Oneline-output" looks like this (I just copy / paste it from one output cell that I have here):

"SELECT enrich_d_dkj_p_k27ac,enrich_lr_dkj_p_k27ac,enrich_r_dkj_p_k27ac FROM dkj_p_k27ac"

      

        
        
        
      

    

• Note that double quotes on either side of the statement were part of the output myresult()
  
  . I have not added them.

Now I understand the much more problematic idea of ​​thinking about building a single function that would create "oneliner-output" and execute it. I can copy / paste "oneliner-output" into a new Postgres query window and execute it like a normal query, just fine, getting the columns and rows I want in the Data Output window.
I would like, however, to automate this step to avoid the copy / paste action. Is there a way in Postgres to use the output text

('oneliner-output') that I receive from a function myresult()

and execute it? Is it possible to create a second function that will receive the output myresult()

and use it to execute the query?

Along those lines, although I know the following script (here below) works and actually outputs exactly the columns and rows you want:

-- DEALLOCATE stmt1; -- use this line after the first time 'stmt1' was created
prepare stmt1 as SELECT enrich_d_dkj_p_k27ac,enrich_lr_dkj_p_k27ac,enrich_r_dkj_p_k27ac FROM dkj_p_k27ac;
execute stmt1;

      

        
        
        
      

    

• I was thinking maybe something like the following scenario could potentially work, once configured correctly? I don’t know how to do it.

  prepare stmt1 as THE_OUTPUT_OF_myresult();
  execute stmt1;
  
        
  
          
          
          
        
  
      

Trying with refcursor

CREATE OR REPLACE FUNCTION show_mytable(ref refcursor) RETURNS refcursor AS $$
BEGIN
   OPEN ref FOR SELECT enrich_d_dkj_p_k27ac,enrich_lr_dkj_p_k27ac,enrich_r_dkj_p_k27ac FROM dkj_p_k27ac;   -- Open a cursor 
   RETURN ref;    -- Return the cursor to the caller
END;
$$ LANGUAGE plpgsql;

      

        
        
        
      

    

Call:

BEGIN;
SELECT show_mytable('roy');
FETCH ALL IN "roy"; 

      

        
        
        
      

    

This procedure actually works and splits out the columns and rows you want, and again, I have to provide an exact SELECT statement.

Basically I would like to be able to and provide it as the output of my function instead myresult()

. Something like that:

CREATE OR REPLACE FUNCTION show_mytable(ref refcursor) RETURNS refcursor AS $$
BEGIN
   OPEN ref FOR myresult();   -- Open a cursor 
   RETURN ref;    -- Return the cursor to the caller
END;
$$ LANGUAGE plpgsql;

      

        
        
        
      

    

Call:

BEGIN;
SELECT show_mytable('roy');
FETCH ALL IN "roy";