PostgreSQL structure of function that return a query

Question

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

Unrelated, but: a PL/pgSQL function is unnecessary for your example. A plain SQL function will do just fine and is more efficient. — user330315
– user330315, Commented Apr 14, 2017 at 5:55

user330315 · Accepted Answer · 2017-04-14 05:54:16Z

1

This information is stored in pg_proc

SELECT unnest(p.proargnames) as column_name,
       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 = 'word_frequency'

answered Apr 14, 2017 at 5:54

user330315

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

Comments

theonelucas · Accepted Answer · 2017-04-14 15:51:35Z

1

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

answered Apr 14, 2017 at 15:51

theonelucas

6248 silver badges12 bronze badges

Collectives™ on Stack Overflow

PostgreSQL structure of function that return a query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related