Re: Issue with pg_get_functiondef

On Tue, 2023-12-12 at 10:33 +0100, Edouard Tollet wrote:
> I'm having trouble understanding the following, I apologize in advance if it is not a bug.
> The following query works and lists the functions name and definitions as set in my database:
>
> select * from (
>     select proname, prokind, pg_get_functiondef(oid) as def
>     from pg_proc
>     where pg_proc.prokind = 'f'
> ) def
>
> however, if I add the filter where def is not null, it returns an error:
> select * from (
>         select proname, prokind, pg_get_functiondef(oid) as def
>         from pg_proc
>         where pg_proc.prokind = 'f'
> ) def
> where def is not null;
> ERROR:  "array_agg" is an aggregate function

PostgreSQL evaluates the function before the WHERE clause.
Try this:

  WITH cte AS MATERIALIZED (
     SELECT proname, oid
     FROM pg_proc
     WHERE prokind = 'f'
  )
  SELECT proname, pg_get_functiondef(oid) AS def
  FROM cte;

Yours,
Laurenz Albe