Thread: 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';
Fixed. Don't use '' for variables. Weird syntax though. 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! > > 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.