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?
