Search code examples
postgresqlinformation-schema

PostgreSQL structure of function that return a query


Given a PostgreSQL function that returns a query:

CREATE OR REPLACE FUNCTION word_frequency(_max_tokens int)
  RETURNS TABLE (
    txt   text   -- visible as OUT parameter inside and outside function
  , cnt   bigint
  , ratio bigint) AS
$func$
BEGIN
   RETURN QUERY
   SELECT t.txt
        , count(*) AS cnt  -- column alias only visible inside
        , (count(*) * 100) / _max_tokens  -- I added brackets
   FROM  (
      SELECT t.txt
      FROM   token t
      WHERE  t.chartype = 'ALPHABETIC'
      LIMIT  _max_tokens
      ) t
   GROUP  BY t.txt
   ORDER  BY cnt DESC;  -- note the potential ambiguity 
END
$func$  LANGUAGE plpgsql;

How can I retrieve the structure of this function? I mean, I know that this function will return the txt, cnt and ratio columns, but how can I make a query that returns these column names? I was trying to find these columns names on information_schema schema, but I couldn't.

The expected result of this hypothetical query would be something like this:

3 results found:
---------------------------------
?column_name?  |  ?function_name?
---------------------------------
txt                word_frequency
cnt                word_frequency
ratio              word_frequency

Solution

  • Based on the answer of a_horse_with_no_name, I came with this final version:

    SELECT
      column_name,
      function_name
    FROM
    (
      SELECT 
        unnest(p.proargnames) as column_name,
        unnest(p.proargmodes) as column_type,
        p.proname as function_name
    FROM pg_proc p 
       JOIN pg_namespace n ON p.pronamespace = n.oid  
    WHERE n.nspname = 'public' 
      AND p.proname = 'my_function'
    ) as temp_table
    WHERE column_type = 't';
    

    I simply omitted the arguments, returning only the columns that the function returns