create function question
The following function always returns NULL when I call it like this:
select get_bundle_id('1009699', '1', '1').
If I replace b, r, c with '1009699', '1', '1' inside the function body, it
returns the correct id!
I changed the first line of b_id to b_id int, and that does not make
difference.
I changed the lines with '' to something like this: '' || b || '' and no
lucky either.
I am wondering what's wrong with this fuction.
Thanks.
Lixin
create function get_bundle_id(varchar(25), varchar(4), varchar(4)) returns
integer as'
declare
b_id bundle.bundle_id%type;
b alias for $1;
r alias for $2;
c alias for $3;
begin
select into b_id bundle.bundle_id
from block, bundle
where block.block_id = bundle.block_id
and bundle.bundle_row = ''r''
and bundle.bundle_column = ''c''
and block.block_id = (select block_id
from block
where populated_serial_no = ''b''
order by _loading_time_ desc
limit 1)
;
if not found then
raise notice ''can not compute bundle_id for % % %'', b, r, c;
end if;
return b_id;
end;
'
language 'plpgsql';
On Mon, 29 Oct 2001, Zhou, Lixin wrote:
The following function always returns NULL when I call it like this:
select get_bundle_id('1009699', '1', '1').
If I replace b, r, c with '1009699', '1', '1' inside the function body, it
returns the correct id!
Probably you shouldn't double quote the b r and c in the function since
that'd be the constants not the variables.
I followed up the original post, and I don't know if the follow-up has been
posted or not.
The trick is: do not use quotes ('') for variables declared in the declare
secton.
Thanks.
-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
Sent: Tuesday, October 30, 2001 12:32 PM
To: Zhou, Lixin
Cc: 'pgsql-general@postgresql.org'
Subject: Re: [GENERAL] create function question
On Mon, 29 Oct 2001, Zhou, Lixin wrote:
The following function always returns NULL when I call it like this:
select get_bundle_id('1009699', '1', '1').
If I replace b, r, c with '1009699', '1', '1' inside the function body, it
returns the correct id!
Probably you shouldn't double quote the b r and c in the function since
that'd be the constants not the variables.
Import Notes
Resolved by subject fallback