Message-ID: <4BEAEBFA.5090304@ttitech.net>
Date: Wed, 12 May 2010 13:57:14 -0400
From: Josh <jgooding@ttitech.net>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.9) Gecko/20100423 Thunderbird/3.0.4
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Greetings folks, dumb question maybe?
References: <4BEAE646.9050902@ttitech.net>
In-Reply-To: <4BEAE646.9050902@ttitech.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 05/12/2010 01:32 PM, Josh wrote:
> Hello, I'm a little new at this so please bear with me.
>
> I am trying to create a function that loads 100M test records into a 
> database,  however I am having a hard time building the function that 
> does so.
>
> I'm trying to do this in PGAdmin III for Ubuntu.  Is there something 
> that I have wrong with this?  I know that this works in MySQL (and yes 
> I know that MySQL bends the SQL Standards), but I am not sure what I 
> am doing wrong exactly.  I am coming up with the error that says 
> there's an error in my syntax near the v INTEGER := 0 line.  I get the 
> same error in psql as I do in the PGAdmin III.
>
> I have the following so far:
>
> DECLARE
> v INTEGER := 0;
> BEGIN
> while v < 100000000
> DO
> INSERT INTO unpart_tbl_test VALUES
> (v, 'test string data', adddate('1995-01-01', (rand(v)*36520) mod 3652));
> v := v + 1;
> END WHILE;
> END;
>
> Any insight would be greatly appreciated.
>
> - J

after some digging I had to first create a language plpgsql, then I 
changed the function to be as follows:

CREATE FUNCTION no_part_tbl() RETURNS void AS '
DECLARE
v INTEGER := 0;
BEGIN
WHILE v < 100000000 LOOP
INSERT INTO no_part_tbl VALUES
(v, "testing no parts", adddate("1995-01-01",
(rand(v)*36520 % 3652));
v := v + 1;
END LOOP;
END;
' LANGUAGE 'plpgsql';

And it seems to accepted the function finally.   SOrry for the waste of 
bandwidth and anyones time.  I'm not used to this syntax, so it will 
take me a bit to get on boad with it.

- J