Return to Question

MY 2ND ATTEMPT:

In response to @Erwin Brandstetter I've fixed my code to become the following:
Note, that my function outputs now TEXT, and not TABLE (as indeed, as you pointed out, there is no point in outputting a TABLE object, since we would need to define its all its columns ahead, which basically defies the entire 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;
    
    --now call function
    select myresult('dkj_p_k27ac','enri');

--now call the function

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

 


And now, upon running the above procedure - I get a text string, which is basically a query (I'll refer to it up next as 'oneliner-output', just for simplicity). As follows (i just copy/paste it from the one output cell that i've got into here):

    "SELECT enrich_d_dkj_p_k27ac,enrich_lr_dkj_p_k27ac,enrich_r_dkj_p_k27ac FROM dkj_p_k27ac"

* please note that the double quotes from both sides of the statement were part of the myresult() output (i didn't add them by myself).
 
@Erwin Brandstetter, and @Craig Ringer, I understand now much better the problematic idea of thinking to construct a single function that would both create the 'oneliner-output' AND execute it.~Thanks a lot for all the informative and helpful input you gave!! 
But - basically, I am able to copy/paste the 'oneliner-output' into a new postgres query window and execute it as a normal query just fine - receiving the desired columns and rows in my Data Output window. I would like however to automate this step, so to avoid the copy/paste step. Is there a way in postgres to use the TEXT output (the 'oneliner-output') that I receive from myresult() function, and execute it? Can a second function be created that would receive the output of myresult() and use it for executing a query? 
Thanks a lot!  

I tried the following:

CREATE OR REPLACE FUNCTION mytext (mytable text, myprefix text)
RETURNS SETOF RECORD AS $$
declare
        smalltext text;
    myoneliner text;
BEGIN
   SELECT 'SELECT ' || substr(cols,2,length(cols)-2) ||' FROM '||mytable 
   INTO myoneliner  
     FROM (
        SELECT array(
           SELECT DISTINCT quote_ident(column_name::text)
           FROM   information_schema.columns
           WHERE  table_name = mytable
           AND    column_name LIKE myprefix||'%'
           order by quote_ident              
      )::text cols 
     ) sub;

   smalltext=lower(myoneliner);
   raise notice '%','my additional text '||smalltext;
   RETURN QUERY EXECUTE smalltext;
END;
$$ LANGUAGE plpgsql;

Call function:

SELECT * from mytext('dkj_p_k27ac','enri');

But I'm getting the following error message, could you please advise what should I change in order for it to execute?:

ERROR:  a column definition list is required for functions returning "record"
LINE 26: SELECT * from mytext('dkj_p_k27ac','enri');

********** Error **********

ERROR: a column definition list is required for functions returning "record"
SQL state: 42601
Character: 728

In response to @Erwin Brandstetter I've fixed my code to become the following:
Note, that my function outputs now TEXT, and not TABLE (as indeed, as you pointed out, there is no point in outputting a TABLE object, since we would need to define its all its columns ahead, which basically defies the entire 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;
    
    --now call function
    select myresult('dkj_p_k27ac','enri');

--now call the function

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

 


And now, upon running the above procedure - I get a text string, which is basically a query (I'll refer to it up next as 'oneliner-output', just for simplicity). As follows (i just copy/paste it from the one output cell that i've got into here):

    "SELECT enrich_d_dkj_p_k27ac,enrich_lr_dkj_p_k27ac,enrich_r_dkj_p_k27ac FROM dkj_p_k27ac"

@Erwin Brandstetter, and @Craig Ringer, I understand now much better the problematic idea of thinking to construct a single function that would both create the 'oneliner-output' AND execute it.~Thanks a lot for all the infromative and helpful input you gave!! 
But - basically, I am able to copy/paste the 'oneliner-output' into a new postgres query window and execute it as a normal query just fine - receiving the desired columns and rows in my Data Output window. I would like however to automate this step, so to avoid the copy/paste step. Is there a way in postgres to use the TEXT output (the 'oneliner-output') that I receive from myresult() function, and execute it? Can a second function be created that would receive the output of myresult() and use it for executing a query? 
Thanks a lot!  

MY 2ND ATTEMPT:

 
In response to @Erwin Brandstetter I've fixed my code to become the following:
Note, that my function outputs now TEXT, and not TABLE (as indeed, as you pointed out, there is no point in outputting a TABLE object, since we would need to define its all its columns ahead, which basically defies the entire 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;
    
    --now call function
    select myresult('dkj_p_k27ac','enri');

--now call the function

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

 


And now, upon running the above procedure - I get a text string, which is basically a query (I'll refer to it up next as 'oneliner-output', just for simplicity). As follows (i just copy/paste it from the one output cell that i've got into here):

    "SELECT enrich_d_dkj_p_k27ac,enrich_lr_dkj_p_k27ac,enrich_r_dkj_p_k27ac FROM dkj_p_k27ac"

* please note that the double quotes from both sides of the statement were part of the myresult() output (i didn't add them by myself).
 
@Erwin Brandstetter, and @Craig Ringer, I understand now much better the problematic idea of thinking to construct a single function that would both create the 'oneliner-output' AND execute it.~Thanks a lot for all the informative and helpful input you gave!! 
But - basically, I am able to copy/paste the 'oneliner-output' into a new postgres query window and execute it as a normal query just fine - receiving the desired columns and rows in my Data Output window. I would like however to automate this step, so to avoid the copy/paste step. Is there a way in postgres to use the TEXT output (the 'oneliner-output') that I receive from myresult() function, and execute it? Can a second function be created that would receive the output of myresult() and use it for executing a query? 
Thanks a lot!  

I tried the following:

CREATE OR REPLACE FUNCTION mytext (mytable text, myprefix text)
RETURNS SETOF RECORD AS $$
declare
        smalltext text;
    myoneliner text;
BEGIN
   SELECT 'SELECT ' || substr(cols,2,length(cols)-2) ||' FROM '||mytable 
   INTO myoneliner  
     FROM (
        SELECT array(
           SELECT DISTINCT quote_ident(column_name::text)
           FROM   information_schema.columns
           WHERE  table_name = mytable
           AND    column_name LIKE myprefix||'%'
           order by quote_ident             
      )::text cols 
     ) sub;

   smalltext=lower(myoneliner);
    raise notice '%','my additional text '||smalltext;
   RETURN QUERY EXECUTE smalltext;
END;
$$ LANGUAGE plpgsql;

Call function:

SELECT * from mytext('dkj_p_k27ac','enri');

But I'm getting the following error message, could you please advise what should I change in order for it to execute?:

ERROR:  a column definition list is required for functions returning "record"
LINE 26: SELECT * from mytext('dkj_p_k27ac','enri');

********** Error **********

ERROR: a column definition list is required for functions returning "record"
SQL state: 42601
Character: 728

In response to @Erwin Brandstetter I've fixed my code to become the following:
Note, that my function outputs now TEXT, and not TABLE (as indeed, as you pointed out, there is no point in outputting a TABLE object, since we would need to define its all its columns ahead, which basically defies the entire 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;
    
    --now call function
    select myresult('dkj_p_k27ac','enri');

--now call the function

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

 


And now, upon running the above procedure - I get a text string, which is basically a query (I'll refer to it up next as 'oneliner-output', just for simplicity). As follows (i just copy/paste it from the one output cell that i've got into here):

    "SELECT enrich_d_dkj_p_k27ac,enrich_lr_dkj_p_k27ac,enrich_r_dkj_p_k27ac FROM dkj_p_k27ac"

@Erwin Brandstetter, and @Craig Ringer, I understand now much better the problematic idea of thinking to construct a single function that would both create the 'oneliner-output' AND execute it.~Thanks a lot for all the infromative and helpful input you gave!! 
But - basically, I am able to copy/paste the 'oneliner-output' into a new postgres query window and execute it as a normal query just fine - receiving the desired columns and rows in my Data Output window. I would like however to automate this step, so to avoid the copy/paste step. Is there a way in postgres to use the TEXT output (the 'oneliner-output') that I receive from myresult() function, and execute it? Can a second function be created that would receive the output of myresult() and use it for executing a query? 
Thanks a lot!