From: | Kyle Butt <kylebutt(at)gmail(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: Bug with function returning composite types. |
Date: | 2009-03-09 15:34:39 |
Message-ID: | 20090309153439.GA32934@North.cfl.rr.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
On Mon, Mar 09, 2009 at 10:03:34AM -0500, Kevin Grittner wrote:
> >>> Kyle Butt <kylebutt(at)gmail(dot)com> wrote:
>
> > select (bug_function()).*;
>
> > psql:sql/bug_example.sql:32: NOTICE: in bug_function
> > psql:sql/bug_example.sql:32: NOTICE: in bug_function
> > psql:sql/bug_example.sql:32: NOTICE: in bug_function
> > psql:sql/bug_example.sql:32: NOTICE: in bug_function
> > psql:sql/bug_example.sql:32: NOTICE: in bug_function
> > psql:sql/bug_example.sql:32: NOTICE: in bug_function
> > psql:sql/bug_example.sql:32: NOTICE: in bug_function
> > psql:sql/bug_example.sql:32: NOTICE: in bug_function
> > psql:sql/bug_example.sql:32: NOTICE: in bug_function
> > psql:sql/bug_example.sql:32: NOTICE: in bug_function
> > a | b | c | d | e | f | g | h | i | j
> > ---+---+---+---+---+---+---+---+---+---
> > 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9
> > (1 row)
>
> For completeness:
>
> cir=# select * from bug_function();
> NOTICE: in bug_function
> a | b | c | d | e | f | g | h | i | j
> ---+---+---+---+---+---+---+---+---+---
> 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9
> (1 row)
>
> -Kevin
I noticed that it has to do with the volatility. If I declare the function as
stable (which it is) then it's executed multiple times. If I declare it as
volatile, it's only executed once.
I'm more interested in something like the following (output placed inline):
create function bug_function_2 (j_p integer) returns composite_types_test
volatile
language plpgsql
as $$
declare r composite_types_test;
begin
select * into r from composite_types_test where j = j_p;
raise notice 'in bug_function_2';
return r;
end;
$$;
select (bf2).* from (
select bug_function_2(j) as bf2 from composite_types_test
) as foo;
psql:sql/bug_example.sql:54: NOTICE: in bug_function_2
a | b | c | d | e | f | g | h | i | j
---+---+---+---+---+---+---+---+---+---
0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9
(1 row)
create or replace function bug_function_2 (j_p integer) returns composite_types_test
stable
language plpgsql
as $$
declare r composite_types_test;
begin
select * into r from composite_types_test where j = j_p;
raise notice 'in bug_function_2';
return r;
end;
$$;
select (bf2).* from (
select bug_function_2(j) as bf2 from composite_types_test
) as foo;
psql:sql/bug_example.sql:70: NOTICE: in bug_function_2
psql:sql/bug_example.sql:70: NOTICE: in bug_function_2
psql:sql/bug_example.sql:70: NOTICE: in bug_function_2
psql:sql/bug_example.sql:70: NOTICE: in bug_function_2
psql:sql/bug_example.sql:70: NOTICE: in bug_function_2
psql:sql/bug_example.sql:70: NOTICE: in bug_function_2
psql:sql/bug_example.sql:70: NOTICE: in bug_function_2
psql:sql/bug_example.sql:70: NOTICE: in bug_function_2
psql:sql/bug_example.sql:70: NOTICE: in bug_function_2
psql:sql/bug_example.sql:70: NOTICE: in bug_function_2
a | b | c | d | e | f | g | h | i | j
---+---+---+---+---+---+---+---+---+---
0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9
(1 row)
From | Date | Subject | |
---|---|---|---|
Next Message | Teodor Sigaev | 2009-03-10 17:44:57 | Re: BUG #4697: to_tsvector hangs on input |
Previous Message | Kevin Grittner | 2009-03-09 15:03:34 | Re: Bug with function returning composite types. |