Обсуждение: 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.