pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedHow do a user-defined function that returns a table executes a query?
5+ messages / 3 participants
[nested] [flat]
* How do a user-defined function that returns a table executes a query?
@ 2023-02-07 23:49 Katsuya Okizaki <miotsukushi0800@gmail.com>
2023-02-07 23:50 ` Re: How do a user-defined function that returns a table executes a query? David G. Johnston <david.g.johnston@gmail.com>
2023-02-08 10:18 ` Re: How do a user-defined function that returns a table executes a query? Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 5+ messages in thread
From: Katsuya Okizaki @ 2023-02-07 23:49 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Hi everyone,
I hope this message finds you in good health and spirits. I am reaching out
to this incredible community for some guidance and advice. I am currently
trying to understand how a user-defined function that returns a table
executes a query.
In a normal SQL, we can use the EXPLAIN command to view the execution plan.
However, in this case, I am not sure how a user-defined function work.
If anyone has faced a similar situation and found a way to view the
execution plan, I would greatly appreciate your insights and suggestions.
Thank you for taking the time to read my message. I look forward to hearing
from you.
Best regards,
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How do a user-defined function that returns a table executes a query?
2023-02-07 23:49 How do a user-defined function that returns a table executes a query? Katsuya Okizaki <miotsukushi0800@gmail.com>
@ 2023-02-07 23:50 ` David G. Johnston <david.g.johnston@gmail.com>
2023-02-08 22:54 ` Re: How do a user-defined function that returns a table executes a query? Katsuya Okizaki <miotsukushi0800@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2023-02-07 23:50 UTC (permalink / raw)
To: Katsuya Okizaki <miotsukushi0800@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
On Tue, Feb 7, 2023 at 4:49 PM Katsuya Okizaki <miotsukushi0800@gmail.com>
wrote:
> In a normal SQL, we can use the EXPLAIN command to view the execution
> plan. However, in this case, I am not sure how a user-defined function work.
>
> If anyone has faced a similar situation and found a way to view the
> execution plan, I would greatly appreciate your insights and suggestions.
>
>
https://www.postgresql.org/docs/current/auto-explain.html
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How do a user-defined function that returns a table executes a query?
2023-02-07 23:49 How do a user-defined function that returns a table executes a query? Katsuya Okizaki <miotsukushi0800@gmail.com>
2023-02-07 23:50 ` Re: How do a user-defined function that returns a table executes a query? David G. Johnston <david.g.johnston@gmail.com>
@ 2023-02-08 22:54 ` Katsuya Okizaki <miotsukushi0800@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Katsuya Okizaki @ 2023-02-08 22:54 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
Dear David,
Thank you for your prompt reply.
This is exactly what I was looking for. Thank you for your help.
Best regards,
Katsuya Okizaki
2023年2月8日(水) 8:51 David G. Johnston <david.g.johnston@gmail.com>:
> On Tue, Feb 7, 2023 at 4:49 PM Katsuya Okizaki <miotsukushi0800@gmail.com>
> wrote:
>
>> In a normal SQL, we can use the EXPLAIN command to view the execution
>> plan. However, in this case, I am not sure how a user-defined function work.
>>
>> If anyone has faced a similar situation and found a way to view the
>> execution plan, I would greatly appreciate your insights and suggestions.
>>
>>
> https://www.postgresql.org/docs/current/auto-explain.html
>
> David J.
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How do a user-defined function that returns a table executes a query?
2023-02-07 23:49 How do a user-defined function that returns a table executes a query? Katsuya Okizaki <miotsukushi0800@gmail.com>
@ 2023-02-08 10:18 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-02-08 23:10 ` Re: How do a user-defined function that returns a table executes a query? Katsuya Okizaki <miotsukushi0800@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2023-02-08 10:18 UTC (permalink / raw)
To: Katsuya Okizaki <miotsukushi0800@gmail.com>; pgsql-general@lists.postgresql.org
On Wed, 2023-02-08 at 08:49 +0900, Katsuya Okizaki wrote:
> In a normal SQL, we can use the EXPLAIN command to view the execution plan.
> However, in this case, I am not sure how a user-defined function work.
>
> If anyone has faced a similar situation and found a way to view the execution plan,
> I would greatly appreciate your insights and suggestions.
I am not sure which of the following you want:
1. Get the execution plan of SQL statements run inside a function:
For that, you would use "auto_explain" with "auto_explain.log_nested_statements = on".
2. Get a trace of the execution of the function code itself:
For PL/pgSQL, there is "pldebugger" (https://github.com/EnterpriseDB/pldebugger),
which can be used together with pgAdmin.
I usually sprinkle the code with RAISE NOTICE statements.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How do a user-defined function that returns a table executes a query?
2023-02-07 23:49 How do a user-defined function that returns a table executes a query? Katsuya Okizaki <miotsukushi0800@gmail.com>
2023-02-08 10:18 ` Re: How do a user-defined function that returns a table executes a query? Laurenz Albe <laurenz.albe@cybertec.at>
@ 2023-02-08 23:10 ` Katsuya Okizaki <miotsukushi0800@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Katsuya Okizaki @ 2023-02-08 23:10 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-general@lists.postgresql.org
Dear Laurenz,
Thank you for your help. As you suspected, I was seeking a way to view the
execution plan of a function defined in SQL. Your suggestion was exactly
what I needed and has been very helpful.
Additionally, I also appreciated learning about the debugging techniques
for PL/pgSQL. Thank you for your valuable advice.
Best regards,
Katsuya Okizaki
2023年2月8日(水) 19:18 Laurenz Albe <laurenz.albe@cybertec.at>:
> On Wed, 2023-02-08 at 08:49 +0900, Katsuya Okizaki wrote:
> > In a normal SQL, we can use the EXPLAIN command to view the execution
> plan.
> > However, in this case, I am not sure how a user-defined function work.
> >
> > If anyone has faced a similar situation and found a way to view the
> execution plan,
> > I would greatly appreciate your insights and suggestions.
>
> I am not sure which of the following you want:
>
> 1. Get the execution plan of SQL statements run inside a function:
>
> For that, you would use "auto_explain" with
> "auto_explain.log_nested_statements = on".
>
> 2. Get a trace of the execution of the function code itself:
>
> For PL/pgSQL, there is "pldebugger" (
> https://github.com/EnterpriseDB/pldebugger),
> which can be used together with pgAdmin.
>
> I usually sprinkle the code with RAISE NOTICE statements.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2023-02-08 23:10 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-02-07 23:49 How do a user-defined function that returns a table executes a query? Katsuya Okizaki <miotsukushi0800@gmail.com>
2023-02-07 23:50 ` David G. Johnston <david.g.johnston@gmail.com>
2023-02-08 22:54 ` Katsuya Okizaki <miotsukushi0800@gmail.com>
2023-02-08 10:18 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-02-08 23:10 ` Katsuya Okizaki <miotsukushi0800@gmail.com>
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