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
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