From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | Edouard Tollet <edouard(dot)tollet(at)stoik(dot)io>, pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Re: Issue with pg_get_functiondef |
Date: | 2023-12-12 12:18:49 |
Message-ID: | ffe25abd1c4202efbc228bc85656ff20fdbf86ee.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Aksel Allas | 2023-12-12 12:51:02 | Re: BUG #18242: pg_dump with non-superuser from pg14 to pg15 fails on ALTER FUNCTION |
Previous Message | Laurenz Albe | 2023-12-12 12:14:33 | Re: BUG #18242: pg_dump with non-superuser from pg14 to pg15 fails on ALTER FUNCTION |