Thread: Why won't nested select-into expression work?

I've got an expression that works fine if I assign its return value (bigint) to a temporary variable (t).  But if
I eliminate the variable and just nest the expression, its outer expression (select into) fails for some reason.

Relevant variables:
    my_paragraph    paragraph%rowtype;
    t bigint;

I tried this and it failed (see embedded comments):

        select * into my_paragraph from paragraph where
            form = p_form and
            number = ( select method_paragraph_new( p_form, 0, p_append ) );
            -- Inner expression above inserts a new row in the 'paragraph' table which
            -- just happens to be the one I want selected by the outer select-into expression
        if not found then
            raise exception 'DEBUG: Paragraph create failed';  -- This is what happens!
        else
            raise exception'DEBUG:  Success!';
        end if;

But it works just fine if I use the variable 't' instead:

        t := ( select method_paragraph_new( p_form, 0, p_append ) );
        select * into my_paragraph from paragraph where
            form = p_form and
            number = t;  -- instead of a nested expression that inserts the thing I am looking for
        if not found then
            raise exception 'DEBUG: Paragraph create failed';
        else
            raise exception'DEBUG:  Success!';  -- This is what happens!
        end if;

There's probably something fundamental I am not understanding here.  Does anyone see the problem?

Leon Starr <leon_starr@modelint.com> writes:
> I tried this and it failed (see embedded comments):

>         select * into my_paragraph from paragraph where
>             form = p_form and
>             number = ( select method_paragraph_new( p_form, 0, p_append ) );
>             -- Inner expression above inserts a new row in the 'paragraph' table which
>             -- just happens to be the one I want selected by the outer select-into expression

You're apparently expecting that the row inserted by
method_paragraph_new() will be seen by the already-in-progress outer
query?  That won't happen.  A SELECT will only see rows that exist
when it starts execution.  This is a feature, not a bug.

            regards, tom lane

Good to know the rule.  Not entirely clear on why it's a feature and not a bug, but
that's why I posted as a novice!

On Nov 23, 2010, at 11:38 AM, Tom Lane wrote:

> Leon Starr <leon_starr@modelint.com> writes:
>> I tried this and it failed (see embedded comments):
>
>>         select * into my_paragraph from paragraph where
>>             form = p_form and
>>             number = ( select method_paragraph_new( p_form, 0, p_append ) );
>>             -- Inner expression above inserts a new row in the 'paragraph' table which
>>             -- just happens to be the one I want selected by the outer select-into expression
>
> You're apparently expecting that the row inserted by
> method_paragraph_new() will be seen by the already-in-progress outer
> query?  That won't happen.  A SELECT will only see rows that exist
> when it starts execution.  This is a feature, not a bug.
>
>             regards, tom lane
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice

Greetings!

I am trying to write a function that will generate test requests as
input for another program.  The function will be called every ten
minutes.  It will read data from a table and generate requests for each
record in the source table in turn.  When it runs out of records in the
source table, it will go back to the beginning of the source table.

The function will get the lowest value larger than a given number from a
column.  If the given number is larger than any value in that column,
then the function should get the lowest number in that column.  The
number that is retrieved is stored in a one-row table for use the next
time the function is called.

Here is my function:

CREATE OR REPLACE FUNCTION add_test_request()
  RETURNS integer AS
$BODY$
declare
    LastIPChargeNum integer;
    LastHSCSChargeNum integer;
    NextIPChargeNum integer;
    NextHSCSChargeNum integer;

begin
    raise notice 'Running add_test_request()';

    select into LastIPChargeNum, LastHSCSChargeNum last_ip_charge,
last_hscs_charge from model_scheduler_test;
    select into NextIPChargeNum min(charge) from charge where status =
'Done' and charge > LastIPChargeNum;
    if not found then
    select into NextIPChargeNum min(charge) from charge where status
= 'Done';
    end if;

    select into NextHSCSChargeNum min(charge) from feedback
    where charge > LastHSCSChargeNum;
    if not found then
    select into NextHSCSChargeNum min(charge) from feedback;
    end if;

    insert into model_request (charge, run_date, heating, cooling)
    values (NextIPChargeNum, current_timestamp, 0, 0);
    insert into model_request (charge, run_date, heating, cooling)
    values (NextHSCSChargeNum, current_timestamp, 1, 1);

    update model_scheduler_test set last_ip_charge = NextIPChargeNum,
last_hscs_charge = NextHSCSChargeNum;

    return 1;
end;


When the values stored in model_scheduler_test are higher than anything
in the charge columns of charge or feedback, the NextIPChargeNum and
NextHSCSChargeNum end up being null.  If the values stored in
model_scheduler_test are low, the function works well.

What piece of sheer idiocy am I missing?

Thanks for your help!

RobR

Answering my own question:  It appears that FOUND will always be true
after calling the min() function, and probably after any other aggregate
function as well, even if the set of data the min() function is looking
at is empty.

RobR