2

I have created the following stored procedure, which basically receives a name of table, and a prefix. The function then finds all columns that share this prefix and returns as an output a 'select' query command ('myoneliner'). as follows:

CREATE OR REPLACE FUNCTION mytext (mytable text, myprefix text)
RETURNS text AS $myoneliner$
declare
    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;
   RETURN myoneliner;
END;
$myoneliner$ LANGUAGE plpgsql;

Call:

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

As a result of running this stored procedure and the 'select' that is following it, I get the following output at the Data Output window (all within one cell, named "mytext text"):

'SELECT enrich_d_dkj_p_k27ac,enrich_lr_dkj_p_k27ac,enrich_r_dkj_p_k27ac
 FROM dkj_p_k27ac'

I would like to basically be able to take the output command line that I received as an output and execute it. In other words, I would like to be able and execute the output of my stored procedure. How can I do so?

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
Improve this question
8
  • 1
    RETURN QUERY EXECUTE. However, your procedure will have to be declared as RETURNING SETOF RECORD and then the caller will have to enumerate the columns in the call, making it less than useful. Alternately you can open a cursor and RETURNS REFCURSOR, then the caller can FETCH from the cursor - but in that case you cannot use the results as part of subqueries etc, only stream them to the client application. Commented Jan 7, 2015 at 0:33
  • This looks like an instance of the inner platform effect anyway. I question the design. What about using hstore or json columns instead? Commented Jan 7, 2015 at 0:34
  • 1
    you need to invoke the procedure as select * from my_function(...) Commented Jan 7, 2015 at 1:57
  • 1
    @Roy That's what I was saying - you have to identify the columns in the query, so it doesn't gain you much. Where I said "then the caller will have to enumerate the columns in the call". You're trying to do something that the system really isn't good at, or particularly designed for. Commented Jan 7, 2015 at 4:29
  • 1
    @Roy See the manual for information setof record. Commented Jan 7, 2015 at 4:35

1 Answer 1

Reset to default
4

Your first problem was solved by using dynamic SQL with EXECUTE like Craig advised. But the rabbit hole goes deeper:

CREATE OR REPLACE FUNCTION myresult(mytable text, myprefix text)
  RETURNS SETOF RECORD AS
$func$
DECLARE
   smalltext  text;
   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

   smalltext := lower(myoneliner);  -- nonsense
   RAISE NOTICE 'My additional text: %', myoneliner;

   RETURN QUERY EXECUTE myoneliner;
END
$func$ LANGUAGE plpgsql;

Major points

  • Don't cast the whole statement to lower case. Column names might be double-quoted with upper case letters, which are case-sensitive in this case (no pun intended).

  • You don't need DISTINCT in the query on information_schema.columns. Column names are unique per table.

  • You do need to specify the schema, though (or use another way to single out one schema), or you might be mixing column names from multiple tables of the same name in multiple schemas, resulting in nonsense.

  • You must sanitize all identifiers in dynamic code - including table names: quote_ident(mytable). Be aware that your text parameter to the function is case sensitive! The query on information_schema.columns requires that, too.

  • I untangled your whole construct to build the list of column names with string_agg() instead of the array constructor. Related answer:

  • The assignment operator in plpgsql is :=.

  • Simplified syntax of RAISE NOTICE.

Core problem impossible to solve

All of this still doesn't solve your main problem: SQL demands a definition of the columns to be returned. You can circumvent this by returning anonymous records like you tried. But that's just postponing the inevitable. Now you have to provide a column definition list at call time, just like your error message tells you. But you just don't know which columns are going to be returned. Catch 22.

Your call would work like this:

SELECT *
FROM   myresult('dkj_p_k27ac','enri') AS f (
  enrich_d_dkj_p_k27ac text  -- replace with actual column types
, enrich_lr_dkj_p_k27ac text
, enrich_r_dkj_p_k27ac text);

But you don't know number, names (optional) and data types of returned columns, not at creation time of the function and not even at call time. It's impossible to do exactly that in a single call. You need two separate queries to the database.

You could return all columns of any given table dynamically with a function using polymorphic types, because there is a well defined type for the whole table. Last chapter of this related answer:

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

There's nothing that fundamentally requires that SQL clients know the columns in advance; for most clients they can be sent just before resultset information. However, PostgreSQL's design doesn't currently allow for queries whose result types are unknown during planning and only determined at execution, so PostgreSQL can't do this.
Also, while two separate queries are required, that doesn't necessarily mean two calls in the sense of two function invocations of the DB API, or two round-trips. You can execute something like SELECT prepare_cursor('cursorname'); FETCH ALL FROM "cursorname"; in a composite statement. Most client drivers will deal with that fine.
@CraigRinger: Similarly, you could CREATE TEMP TABLE foo AS ...; in a dynamic statement and SELECT * FROM foo; in the next query. Can be a single round trip to the server, but two separate queries. As suggested I clarified "queries", since "call" was too fuzzy.
Hi @Erwin Brandstetter, thanks a lot for your replies. Very informative and educative. I've added a new part to my question - please see under "MY 2ND ATTEMPT" above. I am basically wondering if I could create a 2nd function that would receive the TEXT output of myresult() function (which is basically an SQL query line) and would be able to use it for executing a query. I would really appreciate your help. The incentive behind this is to avoid the copy/paste step that I currently do after receiving the output of myresult(). Thanks a lot!
Please revert your edit to the question and start a new question instead. The information is for the general public, which will have a hard time to follow. You can always link to this one for context.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.