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