Search Postgresql Archives

Re: Referring to function parameter in function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 18 September 2010 00:52, Thom Brown <thom@xxxxxxxxx> wrote:
> On 18 September 2010 00:14, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
>> Thom Brown <thom@xxxxxxxxx> 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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux