From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Edouard Tollet <edouard.tollet@stoik.io>
To: pgsql-bugs@lists.postgresql.org
Subject: Re: Issue with pg_get_functiondef
Date: Tue, 12 Dec 2023 13:18:49 +0100
Message-ID: <ffe25abd1c4202efbc228bc85656ff20fdbf86ee.camel@cybertec.at> (raw)
In-Reply-To: <CAMTFitQobPvh0_wbW_S-o7V03qcGkTWgZD-3ozXAbfzCcGuvgQ@mail.gmail.com>
References: <CAMTFitQobPvh0_wbW_S-o7V03qcGkTWgZD-3ozXAbfzCcGuvgQ@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: laurenz.albe@cybertec.at, edouard.tollet@stoik.io, pgsql-bugs@lists.postgresql.org
Subject: Re: Issue with pg_get_functiondef
In-Reply-To: <ffe25abd1c4202efbc228bc85656ff20fdbf86ee.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox