From: | Andrew Dunstan <andrew(at)dunslane(dot)net> |
---|---|
To: | Sergey Konoplev <gray(dot)ru(at)gmail(dot)com> |
Cc: | George Neuner <gneuner2(at)comcast(dot)net>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: help: function failing |
Date: | 2014-10-07 21:27:58 |
Message-ID: | 54345ADE.8030806@dunslane.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On 10/07/2014 04:41 PM, Sergey Konoplev wrote:
> On Thu, Oct 2, 2014 at 4:00 PM, George Neuner <gneuner2(at)comcast(dot)net> wrote:
>> --------------- code ----------------
>> CREATE OR REPLACE FUNCTION gen_random()
>> RETURNS double precision AS
>> $BODY$
>> DECLARE
>> num float8 := 0;
>> den float8 := 281474976710655; -- 0xFFFFFFFFFFFF
>> bytes bytea[6];
>> BEGIN
>> -- get random bytes from crypto module
>> bytes := ext.gen_random_bytes(6);
>>
>> -- assemble a double precision value
>> num := num + get_byte( bytes, 0 );
>> FOR i IN 1..5 LOOP
>> num := num * 256;
>> num := num + get_byte( bytes, i );
>> END LOOP;
>>
>> -- normalize value to range 0.0 .. 1.0
>> RETURN num / den;
>> END;
>> $BODY$
>> LANGUAGE plpgsql VOLATILE;
>> --------------- code ----------------
>>
>> The error is:
>> ERROR: array value must start with "{" or dimension information
>> SQL state: 22P02
>> Context: PL/pgSQL function gen_random() line 8 at assignment
>>
>> which, if I'm counting correctly, is
>> bytes := ext.gen_random_bytes(6);
> Guessing on the name of ext.gen_random_bytes(6) it returns a value
> that is incompatible with bytea[] array representation time from time,
> so take a closer look at ext.gen_random_bytes() first. You can test
> the case using DO block.
>
>> If I comment out that line, it then tells me get_byte() is undefined,
>> which should be impossible because it's built in.
> Feels like somewhere inside ext.gen_random_bytes() you set a
> search_path that allows to see get_byte() and the search_path that was
> set before the gen_random() call doesn't allow it.
>
Why does this code want an array of byteas?
It looks like the code thinks bytea[6] is a declaration of a bytea of
length 6, which of course it is not. Shouldn't it just be declared as:
bytes bytea;
?
Oh, and pgsql-performance is completely the wrong forum for this query.
usage questions should be on pgsql-general.
cheers
andrew
From | Date | Subject | |
---|---|---|---|
Next Message | and | 2014-10-08 06:35:02 | Bad optimization/planning on Postgres window-based queries (partition by(, group by?)) - 1000x speedup |
Previous Message | Sergey Konoplev | 2014-10-07 20:41:04 | Re: help: function failing |