Referring to function parameter in function

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