Message-ID: <54345ADE.8030806@dunslane.net>
Date: Tue, 07 Oct 2014 17:27:58 -0400
From: Andrew Dunstan <andrew@dunslane.net>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:17.0) Gecko/20130110 Thunderbird/17.0.2
MIME-Version: 1.0
To: Sergey Konoplev <gray.ru@gmail.com>
CC: George Neuner <gneuner2@comcast.net>,
 "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: help: function failing
References: <542DD92A.9000305@comcast.net>
 <CAL_0b1uwhpvPrrtC6ba6E=bBn+euj78VM-qWH+cp3jxrYyskPw@mail.gmail.com>
In-Reply-To: 
 <CAL_0b1uwhpvPrrtC6ba6E=bBn+euj78VM-qWH+cp3jxrYyskPw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


On 10/07/2014 04:41 PM, Sergey Konoplev wrote:
> On Thu, Oct 2, 2014 at 4:00 PM, George Neuner <gneuner2@comcast.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


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