Обсуждение: Referring to function parameter in function

I appear to be having a problem with a function I've created, and no
doubt it'll be something obvious I'm doing wrong.  Here's my function:

CREATE OR REPLACE FUNCTION get_lsfr(
    bitlength INT,
    taps INT[],
    from_value INT
) RETURNS INT AS $$
DECLARE
    last_tap_value BIT;
    tap INT;
    new_value INT;
BEGIN
    IF (SELECT MAX(x) FROM unnest(taps) AS x) > bitlength THEN
        RAISE EXCEPTION 'LSFR tap exceeds range of value.';
    END IF;

    FOR tap IN SELECT value FROM unnest(taps) AS x(value) ORDER BY value DESC LOOP
        IF last_tap_value IS NOT NULL THEN
            last_tap_value := last_tap_value #
GET_BIT(from_value::bit(bitlength), tap.value-1);
        ELSE
            last_tap_value := GET_BIT(from_value::bit(bitlength), tap.value-1);
            CONTINUE;
        END IF;
    END LOOP;

    new_value := (last_tap_value || SUBSTRING(from_value::BIT(bitlength),
1, bitlength - 1))::BIT(bitlength)::INT;

    RETURN new_value;
END;
$$ LANGUAGE plpgsql;

And here's it's usage and result:

select get_lsfr(4,'{3,4}'::int[],6);
ERROR:  invalid input syntax for integer: "bitlength"
LINE 1: SELECT GET_BIT(from_value::bit(bitlength), tap.value-1)
                                   ^
QUERY:  SELECT GET_BIT(from_value::bit(bitlength), tap.value-1)
CONTEXT:  PL/pgSQL function "get_lsfr" line 14 at assignment

If the function is difficult to read, please look at this paste:
http://pgsql.privatepaste.com/fd5b83166c

I want to use the parameter called "bitlength" as the length of a bit
when casting a value.

So, in this case, it would be GET_BIT(6::bit(4), 4-1)

What am I missing?

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

Thom Brown <thom@linux.com> writes:
> ERROR:  invalid input syntax for integer: "bitlength"
> LINE 1: SELECT GET_BIT(from_value::bit(bitlength), tap.value-1)
>                                    ^

> I want to use the parameter called "bitlength" as the length of a bit
> when casting a value.

Hm, you can't ... that's not a valid place for a parameter.  You'd have
to EXECUTE a built-up string.

            regards, tom lane

On 18 September 2010 00:14, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Thom Brown <thom@linux.com> writes:
>> ERROR:  invalid input syntax for integer: "bitlength"
>> LINE 1: SELECT GET_BIT(from_value::bit(bitlength), tap.value-1)
>>                                    ^
>
>> I want to use the parameter called "bitlength" as the length of a bit
>> when casting a value.
>
> Hm, you can't ... that's not a valid place for a parameter.  You'd have
> to EXECUTE a built-up string.

Ah, thanks Tom.  Although it's now treating the actual query text as a
value by the look of it:

CREATE OR REPLACE FUNCTION get_lsfr(
    bitlength INT,
    taps INT[],
    from_value INT
) RETURNS INT AS $$
DECLARE
    last_tap_value BIT;
    tap RECORD;
    new_value INT;
BEGIN
    IF (SELECT MAX(x) FROM unnest(taps) AS x) > bitlength THEN
        RAISE EXCEPTION 'LSFR tap exceeds range of value.';
    END IF;

    FOR tap IN SELECT tap_values FROM unnest(taps) AS x(tap_values) ORDER
BY tap_values DESC LOOP
        IF last_tap_value IS NOT NULL THEN
            EXECUTE 'SELECT ' || last_tap_value || ' # GET_BIT(' || from_value
|| '::bit(' || bitlength || '), ' || tap.tap_values || '-1)' INTO
last_tap_value;
        ELSE
            EXECUTE 'SELECT GET_BIT(' || from_value || '::bit(' || bitlength ||
'), ' || tap.tap_values || '-1)' INTO last_tap_value;
            CONTINUE;
        END IF;
    END LOOP;

    new_value := (last_tap_value || SUBSTRING(from_value::BIT(bitlength),
1, bitlength - 1))::BIT(bitlength)::INT;

    RETURN new_value;
END;
$$ LANGUAGE plpgsql;

=# select get_lsfr(4,'{3,4}'::int[],6);
ERROR:  "S" is not a valid binary digit
LINE 1: SELECT 'SELECT ' || last_tap_value || ' # GET_BIT(' || from_...
               ^
QUERY:  SELECT 'SELECT ' || last_tap_value || ' # GET_BIT(' ||
from_value || '::bit(' || bitlength || '), ' || tap.tap_values ||
'-1)'
CONTEXT:  PL/pgSQL function "get_lsfr" line 12 at EXECUTE statement

http://pgsql.privatepaste.com/5441ff7cc0

I'm thinking maybe I haven't used the correct syntax.
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

On 18 September 2010 00:52, Thom Brown <thom@linux.com> wrote:
> On 18 September 2010 00:14, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Thom Brown <thom@linux.com> writes:
>>> ERROR:  invalid input syntax for integer: "bitlength"
>>> LINE 1: SELECT GET_BIT(from_value::bit(bitlength), tap.value-1)
>>>                                    ^
>>
>>> I want to use the parameter called "bitlength" as the length of a bit
>>> when casting a value.
>>
>> Hm, you can't ... that's not a valid place for a parameter.  You'd have
>> to EXECUTE a built-up string.
>
> Ah, thanks Tom.  Although it's now treating the actual query text as a
> value by the look of it:
>
> CREATE OR REPLACE FUNCTION get_lsfr(
>        bitlength INT,
>        taps INT[],
>        from_value INT
> ) RETURNS INT AS $$
> DECLARE
>        last_tap_value BIT;
>        tap RECORD;
>        new_value INT;
> BEGIN
>        IF (SELECT MAX(x) FROM unnest(taps) AS x) > bitlength THEN
>                RAISE EXCEPTION 'LSFR tap exceeds range of value.';
>        END IF;
>
>        FOR tap IN SELECT tap_values FROM unnest(taps) AS x(tap_values) ORDER
> BY tap_values DESC LOOP
>                IF last_tap_value IS NOT NULL THEN
>                        EXECUTE 'SELECT ' || last_tap_value || ' # GET_BIT(' || from_value
> || '::bit(' || bitlength || '), ' || tap.tap_values || '-1)' INTO
> last_tap_value;
>                ELSE
>                        EXECUTE 'SELECT GET_BIT(' || from_value || '::bit(' || bitlength ||
> '), ' || tap.tap_values || '-1)' INTO last_tap_value;
>                        CONTINUE;
>                END IF;
>        END LOOP;
>
>        new_value := (last_tap_value || SUBSTRING(from_value::BIT(bitlength),
> 1, bitlength - 1))::BIT(bitlength)::INT;
>
>        RETURN new_value;
> END;
> $$ LANGUAGE plpgsql;
>
> =# select get_lsfr(4,'{3,4}'::int[],6);
> ERROR:  "S" is not a valid binary digit
> LINE 1: SELECT 'SELECT ' || last_tap_value || ' # GET_BIT(' || from_...
>               ^
> QUERY:  SELECT 'SELECT ' || last_tap_value || ' # GET_BIT(' ||
> from_value || '::bit(' || bitlength || '), ' || tap.tap_values ||
> '-1)'
> CONTEXT:  PL/pgSQL function "get_lsfr" line 12 at EXECUTE statement
>
> http://pgsql.privatepaste.com/5441ff7cc0
>
> I'm thinking maybe I haven't used the correct syntax.
> --

I've solved it.  These constructs take a bit of getting used to.  I
just needed to convert the parameter being injected after the SELECT
to text as the bit value couldn't be inserted natively.  My function
works perfectly now.  Thanks for the help :)

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935