Why won't nested select-into expression work?

From: Leon Starr <leon(at)modelint(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Why won't nested select-into expression work?
Date: 2010-11-23 19:23:25
Message-ID: 170AC186-9770-4DA6-A54A-F47ABE39B8E1@modelint.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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?

Browse pgsql-novice by date

  From Date Subject
Next Message Leon Starr 2010-11-23 19:24:40 Why won't nested select-into expression work?
Previous Message Vaduvoiu Tiberiu 2010-11-23 12:42:20 Re: Null values detected as 0 value