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