pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How 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