Re: Using sequences in SQL text files - Mailing list pgsql-general

HHB wrote:
> Thank you all for your help.
> Let me summer what I'm trying to do:
> I have an empty database that I want to populate it with data.
> I created SQL text files, categories.sql, books.sql ....
> As I'm a Java guy, I use Apache Ant SQL taks to run those SQL text files.
> So, I run categories.sql file first, then books.sql
> It seems to me that Brian's solution supposed that I have all SQL insert
> statements are in one file, this isn't my case (actually, I have many SQL
> files that
> I want to run, merging them in one SQL isn't practical).
> So, is it possible to do so:
> ----
> categories.sql
> don't insert an id here
> INSERT INTO categories ('..', '..' ...) VALUES ('..', '..', ...);
> INSERT INTO categories ('..', '..' ...) VALUES ('..', '..', ...);
> ----
> books.sql
> INSERT INTO books (category_id, '..', ...) VALUES
> (CAST(currval('categories_id_seq') AS INT), '..', ...);
> INSERT INTO books (category_id, '..', ...) VALUES
> (CAST(currval('categories_id_seq') AS INT), '..', ...);
> ----
> Thank you all for your time.
>
>

No, because you'll be retrieving the same (last) value for
categories_id_seq every time. If your library is limited to a single
category, you might be good to go, but anyway ...


If you're going to be using INSERT, rather than COPY, you might as well
follow each "INSERT INTO category ..." with the books for that
category. You say you want to keep things separated a bit (and I can
relate to that--haha) but you could take that a step further, reading
the categories line by line and running the appropriate books file for
each. Or something like that.

Or you could use Ant to create a bunch of COPY blocks after each INSERT
statement. (I guess--I'm sure it can do that)

Or you can use the following mechanism to store the category IDs as
session variables.

CREATE FUNCTION get_id(name text) RETURNS integer
AS $_X$
     return $_SHARED{$_[0]};
$_X$
LANGUAGE plperl IMMUTABLE;


CREATE FUNCTION set_id(name text, val integer) RETURNS text
AS $_X$

if ($_SHARED{$_[0]} = $_[1])
{
     return 'ok';
}
else
{
     return "can't set shared variable $_[0] to $_[1]";
}

$_X$
LANGUAGE plperl;


Usage:

INSERT INTO categories ...
SELECT set_id('fiction', CAST(currval('categories_id_seq') AS INT));
INSERT INTO categories ...
SELECT set_id('science', CAST(currval('categories_id_seq') AS INT));
INSERT INTO categories ...
SELECT set_id('politics', CAST(currval('categories_id_seq') AS INT));

...
INSERT INTO books (category_id, ....) VALUES (CAST(get_id('fiction') AS
INT4), ...
INSERT INTO books (category_id, ....) VALUES (CAST(get_id('politics') AS
INT4), ...
INSERT INTO books (category_id, ....) VALUES (CAST(get_id('fiction') AS
INT4), ...


Personally, I'd work out some way to do each INSERT into categories
followed by a COPY block with all the books for that category, simply
because it will be quicker and is much neater.

b