Generating a SQL Server population routine

Martin_Hurst@dom.com wrote:

Has some one come up with a similar type script that could be used in a
Postgresql database?

The script below was created for a SQLServer database.
Thx,
-Martin

I haven't. But I was wondering if a general purpose tuple-generating
function, which would be trivial to implement, might be worthwhile in
PostgreSQL or perhaps added to Joe Conway's tablefunc module.
Something like:

tuple_generator(integer)

which returns a set of numbers whose elements are the integer values
between 1 and the number supplied.

You could then create any number of pseudo-duplicates (can't violate
the candidate key, obviously) from a single-record table like so:

INSERT INTO employees (name, salary)
SELECT employees.name, employees.salary
FROM employees, tuple_generator(1000)
WHERE employees.employeeid = 1;

You could easily build a script to fill your database by querying
pg_class.relname and feeding the output to psql.

It would also be useful for handling sparse date and time data:

SELECT day_of_year,
(SELECT COALESCE(SUM(purchases.qty), 0)
FROM purchases
WHERE EXTRACT(doy FROM purchases.sale_date) = day_of_year)
FROM tuple_generator(366) AS day_of_year
ORDER BY day_of_year;

Mike Mascari
mascarm@mascari.com

I think I have come across a bug in postgres 7.3.x. I noticed that when I
upgraded to 7.3 my code broke on certain sql statements. They still don't
work on the lastest stable release of 7.3.

Here is an example to illustrate my problem:

-- create the table with:
CREATE TABLE bugtest ( a int2 );

-- then do the following inserts:
insert into bugtest (a) select 1 union select 1; -- this one succeeds
insert into bugtest (a) select 1 union select '1'; -- this one also succeeds
insert into bugtest (a) select '1' union select 1; -- this one also succeeds
insert into bugtest (a) select '1' union select '1'; -- this one fails

The all succeed except the last one. It fails with the following error:

ERROR: column "a" is of type smallint but expression is of type text
You will need to rewrite or cast the expression

It seems to me that they should all succeed. At least postgres seems to
have no problem converting '<intvalue>' to <intvalue> anywhere else that I
can find. At least the last 3 inserts shoudl either all fail or all succeed
(IMHO).

Is this a bug? Has anyone else reported it? Is there a procedure I need to
follow to report it? Has it been fixed in 7.4?

On a sidenote I am doing the weird select union thing as a way to insert
many records at once without having to execute multiple queries. I first
started doing it on SQLServer and it was much, much faster than doing
separate inserts. Is there a better way to do it in postgres? I have
looked at the copy from command but I can't find any examples of how to use
it in php or how to specify the columns / column order that you are going to
use with php. Also what characters need to be excaped if I do this
(obviously new lines and tabs). And nulls are represented by \N.

Rick Gigger

"Rick Gigger" <rick@alpinenetworking.com> writes:

insert into bugtest (a) select '1' union select '1'; -- this one fails

Is this a bug?

No. It's unfortunate perhaps, but it's not a bug. The UNION forces us
to make a decision about the output datatype of the UNION operation.
In your other cases the chosen datatype is integer, which can later be
cast to smallint for the insert, but in this case the chosen datatype is
text, which is not implicitly castable to smallint.

regards, tom lane

What was it that changed in 7.3 that made this behavior change. (it worked
in 7.2)

Thanks,

Rick Gigger

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Rick Gigger" <rick@alpinenetworking.com>
Cc: <pgsql-general@postgresql.org>
Sent: Monday, October 06, 2003 1:04 PM
Subject: Re: [GENERAL] Possible bug on insert

I guess then I will switch to use COPY "tablename (fieldlist)" FROM. Will
this end up being faster anyway.

In case anyone was wondering here is a good example of how to do it in php
from:

http://us4.php.net/manual/en/function.pg-put-line.php

<?php
$conn = pg_pconnect("dbname=foo");
pg_query($conn, "create table bar (a int4, b char(16), d float8)");
pg_query($conn, "copy bar from stdin");
pg_put_line($conn, "3\thello world\t4.5\n");
pg_put_line($conn, "4\tgoodbye world\t7.11\n");
pg_put_line($conn, "\\.\n");
pg_end_copy($conn);
?>

thanks,

Rick Gigger

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Rick Gigger" <rick@alpinenetworking.com>
Cc: <pgsql-general@postgresql.org>
Sent: Monday, October 06, 2003 1:04 PM
Subject: Re: [GENERAL] Possible bug on insert

"Rick Gigger" <rick@alpinenetworking.com> writes:

What was it that changed in 7.3 that made this behavior change. (it worked
in 7.2)

7.2 allowed implicit casts from text to smallint, I think.

regards, tom lane

Is there any possiblity of adding an option for compatibilityes sake (or
does one already exist) to revert to the old behavior. This has currently
kept me from upgrading beyond 7.2.4 thus far in production as it will break
all of my apps. I can slowly update them but many of them don't get changed
very often and I will have to undergo a testing cycle for each of them just
to maintain compatibility with postgres > 7.2.4. This is not something I
really want to do. I would much prefer to just upgrade and have my legasy
apps work without modification or testing.

Thanks,

Rick Gigger

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Rick Gigger" <rick@alpinenetworking.com>
Cc: <pgsql-general@postgresql.org>
Sent: Monday, October 06, 2003 2:26 PM
Subject: Re: [GENERAL] Possible bug on insert

"Rick Gigger" <rick@alpinenetworking.com> writes:

What was it that changed in 7.3 that made this behavior change. (it

worked

Two questions:

1) how would I go about doing that
2) is there any change that doing that could break other things?

thanks,

Rick Gigger

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Rick Gigger" <rick@alpinenetworking.com>
Sent: Monday, October 06, 2003 2:57 PM
Subject: Re: [GENERAL] Possible bug on insert

"RG" == Rick Gigger <rick@alpinenetworking.com> writes:

RG> very often and I will have to undergo a testing cycle for each of them just
RG> to maintain compatibility with postgres > 7.2.4. This is not something I
RG> really want to do. I would much prefer to just upgrade and have my legasy
RG> apps work without modification or testing.

You can't have progress that way. To be fair, it *is* a major version
number change, and if you don't test your apps across major version
changes of *anything* you don't need to be in this business. No
offense ;-)

Anyhow, Bruce posted a patch to the 7.3 line to allow this behavior
back, as it was necessary for some apps. However, don't count on it
for 7.4...

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

Do other RDBMSs like Oracle, DB2, mysql, SQLServer, etc usually break
compatibility with applications when changing major versions? I am not
trying to say either way but I would be interested to see what others
experience has been. My experience has been that they do not. At the same
time I switched all of my stuff to postgres a while back and have had so few
problems that I haven't really felt much need to use those other systems.

No offense taken. I am very meticulous about any software upgrades that I
do on my production systems. I'm not quite sure what you mean by "this
business since you have no idea what I am actually doing. For all you know
I am a boy scout developing a public service web site to earn a merit badge
and am not actually in any business. ;-) I suppose that I shouldn't have
said that I don't want to have to test my apps to upgrade to a new version
of postgres. I am used to having to do this for any of the software on my
servers. What I should have said is that I would prefer not to have to go
any change a bunch of code on my production applications immediately. I
realize that in order to make progress it is sometimes neccesary to break
compatibility with old stuff. That being said it would have been nice in my
opinion if there was an option to revert to the old behavior for at least a
while so that I can upgrade sooner rather than later. It is not going to be
a huge problem for me to update the apps but I am probably going to wait
until I am already making other changes and going through a full testing
cycle before I do the upgrade. The whole proccess would just be a lot
smoother if I had the option of using the old behavior with 7.3 for while.

Plus if I have to tell people that we have to spend time and money retesting
all of our apps just to not get stuck on an old version of the database
that's one more thing they might bring up when making the case to switch to
something else. This is why I would like to know about other systems
maintaining backwards compatibility. If I had the option to use the old
behavior it would be a lot easier to make the transition without anyone
noticing. Once again not an insurmountable obstacle but it would be nice.

I am also still confused as to what the rules are that I am breaking. For
instance:

The following will all execute successfully:

CREATE TABLE bugtest ( a int2 );
select '1' union select '2';
insert into bugtest values('1');
insert into bugtest values('2');

But the following will not:

insert into bugtest (a) select '1' union select '2';

Where exactly is the illegal type conversion taking place in the last
statement that is different from the above statements.

I hope that I don't sound like I am complaining. I love postgres and to be
honest I really just want to be able to use pgadmin III sooner than later as
it is so much better than pgadmin II but it doesn't support postgres 7.2.

Anyhow, Bruce posted a patch to the 7.3 line to allow this behavior
back, as it was necessary for some apps. However, don't count on it
for 7.4...

Thanks very much that could very possibly buy me all the time that I will
want. Where do I get this patch?

Thanks again,

Rick Gigger

P.S. I have one more question. Will the following php code work fine inside
a larger transaction:

pg_query($conn, "copy bar from stdin");
pg_put_line($conn, "3\thello world\t4.5\n");
pg_put_line($conn, "4\tgoodbye world\t7.11\n");
pg_put_line($conn, "\\.\n");
pg_end_copy($conn);

That is does doing the weird thing reading from standard input change how it
behaves inside a transaction or do anything else unexpected. And is this
the best and or fastest way to insert multiple tules at a time?

"Vivek Khera" <khera@kcilink.com> wrote in message
news:x7isn16rzx.fsf@yertle.int.kciLink.com...

"RG" == Rick Gigger <rick@alpinenetworking.com> writes:

RG> very often and I will have to undergo a testing cycle for each of them

just

RG> to maintain compatibility with postgres > 7.2.4. This is not

something I

RG> really want to do. I would much prefer to just upgrade and have my

legasy

In article <3F81B176.3060701@mascari.com>,
Mike Mascari <mascarm@mascari.com> writes:

Martin_Hurst@dom.com wrote:

Has some one come up with a similar type script that could be used in a
Postgresql database?

The script below was created for a SQLServer database.
Thx,
-Martin

I haven't. But I was wondering if a general purpose tuple-generating
function, which would be trivial to implement, might be worthwhile in
PostgreSQL or perhaps added to Joe Conway's tablefunc module.
Something like:

tuple_generator(integer)

which returns a set of numbers whose elements are the integer values
between 1 and the number supplied.

How about this?

CREATE OR REPLACE FUNCTION enum (INT) RETURNS SETOF INT AS '
DECLARE
numvals ALIAS FOR $1;
BEGIN
FOR currval IN 0 .. numvals - 1 LOOP
RETURN NEXT currval;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION enum (INT, INT) RETURNS SETOF INT AS '
DECLARE
numvals ALIAS FOR $1;
minval ALIAS FOR $2;
BEGIN
FOR currval IN 0 .. numvals - 1 LOOP
RETURN NEXT minval + currval;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION enum (INT, INT, INT) RETURNS SETOF INT AS '
DECLARE
numvals ALIAS FOR $1;
minval ALIAS FOR $2;
maxval ALIAS FOR $3;
BEGIN
FOR currval IN 0 .. numvals - 1 LOOP
RETURN NEXT currval % (maxval - minval + 1) + minval;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';

Usage: SELECT * FROM enum (numvals [, minval [, maxval]])
Returns numvals consecutive numbers, beginning with 0 or minval
Wraps around to minval if maxval is reached

Rick Gigger wrote:

No offense taken. I am very meticulous about any software upgrades that I
do on my production systems. I'm not quite sure what you mean by "this
business since you have no idea what I am actually doing. For all you know
I am a boy scout developing a public service web site to earn a merit badge
and am not actually in any business. ;-) I suppose that I shouldn't have

Well.. Let's leave that part out to keep this discussion technical.:-)

said that I don't want to have to test my apps to upgrade to a new version
of postgres. I am used to having to do this for any of the software on my
servers. What I should have said is that I would prefer not to have to go
any change a bunch of code on my production applications immediately. I
realize that in order to make progress it is sometimes neccesary to break
compatibility with old stuff. That being said it would have been nice in my
opinion if there was an option to revert to the old behavior for at least a
while so that I can upgrade sooner rather than later. It is not going to be
a huge problem for me to update the apps but I am probably going to wait
until I am already making other changes and going through a full testing
cycle before I do the upgrade. The whole proccess would just be a lot
smoother if I had the option of using the old behavior with 7.3 for while.

Well.. I haven't work enough on pg to make a migration across versions. But the
app. I work on regularly is routinely one year behind a major oracle release and
we spend good deal of our time testing oracle bug/feature compatibility and
making any changes required.

I hope that answers your question.

It really depends upon how much mission critical your app. is. If its something
that monitors heart beats of patients in a 10000 patient hospital in a central
fashion (Just making it up) or controlling a nuke plant, I would rather test
everything I can.

Plus if I have to tell people that we have to spend time and money retesting
all of our apps just to not get stuck on an old version of the database
that's one more thing they might bring up when making the case to switch to
something else. This is why I would like to know about other systems
maintaining backwards compatibility. If I had the option to use the old
behavior it would be a lot easier to make the transition without anyone
noticing. Once again not an insurmountable obstacle but it would be nice.

Retesting with application with version upgrade in dependent components is fact
of life. It is a must. Its upto you to decide whether you can afford to bypass
it, fully or partially. Of course there are business constraints that decides
where the trade off settles.

If you are happy with the way pg upgrade, good for you. But you should read
release notes carefully and work on removing/changing any deprecated features in
time.

Usually pg makes a feature non-default in one major version and removes it
completely in next major version. So you have time of two major releases to take
care of any issues. I think that is more than what anybody else can give across
versions.

Shridhar

On Tue, 7 Oct 2003, Rick Gigger wrote:

I am also still confused as to what the rules are that I am breaking. For
instance:

The following will all execute successfully:

CREATE TABLE bugtest ( a int2 );
select '1' union select '2';
insert into bugtest values('1');
insert into bugtest values('2');

Technically speaking, I believe the above inserts would be errors in plain
SQL92 (I don't believe character(1) is assignable to an exact numeric
type). In part because of the flexible type system, we try to treat such
things as the type being assigned to when possible.

insert into bugtest (a) select '1' union select '2';

Where exactly is the illegal type conversion taking place in the last
statement that is different from the above statements.

The union requires us to figure out what type it is before we get to
actually doing the insert. It might be possible in union all to avoid
that, but for union, you need to know what rules to use to determine
duplicates, so I really don't think there's any choice there (imagine that
the values were say '1.1' and '1.2'... Are those two distinct values or
not?)

On Tue, Oct 07, 2003 at 12:43:21PM -0600, Rick Gigger wrote:

Do other RDBMSs like Oracle, DB2, mysql, SQLServer, etc usually break
compatibility with applications when changing major versions? I am not

I can say for sure that I was involved in a case where the upgrade of
SQL Server required significant additional development efforts to
make an application work. So yes, they sometimes do.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Afilias Canada                        Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

In article <3F81B176.3060701@mascari.com>,
Mike Mascari <mascarm@mascari.com> writes:

Martin_Hurst@dom.com wrote:

Has some one come up with a similar type script that could be used
in a Postgresql database?

The script below was created for a SQLServer database.
Thx,
-Martin

I haven't. But I was wondering if a general purpose tuple-generating
function, which would be trivial to implement, might be worthwhile in
PostgreSQL or perhaps added to Joe Conway's tablefunc module.
Something like:

tuple_generator(integer)

which returns a set of numbers whose elements are the integer values
between 1 and the number supplied.

How about this?

CREATE OR REPLACE FUNCTION enum (INT) RETURNS SETOF INT AS '
DECLARE
numvals ALIAS FOR $1;
BEGIN
FOR currval IN 0 .. numvals - 1 LOOP
RETURN NEXT currval;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION enum (INT, INT) RETURNS SETOF INT AS '
DECLARE
numvals ALIAS FOR $1;
minval ALIAS FOR $2;
BEGIN
FOR currval IN 0 .. numvals - 1 LOOP
RETURN NEXT minval + currval;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION enum (INT, INT, INT) RETURNS SETOF INT AS
' DECLARE
numvals ALIAS FOR $1;
minval ALIAS FOR $2;
maxval ALIAS FOR $3;
BEGIN
FOR currval IN 0 .. numvals - 1 LOOP
RETURN NEXT currval % (maxval - minval + 1) + minval;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';

Usage: SELECT * FROM enum (numvals [, minval [, maxval]])
Returns numvals consecutive numbers, beginning with 0 or minval
Wraps around to minval if maxval is reached

Or a little different, with the over-loaded functions relying on the
original:

CREATE OR REPLACE FUNCTION public.enum(int4) RETURNS SETOF int4 AS '
DECLARE
numvals ALIAS FOR $1;
BEGIN
FOR currval IN 0 .. numvals - 1 LOOP
RETURN NEXT currval;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE FUNCTION public.enum(int4, int4) RETURNS SETOF int4 AS '
DECLARE
numvals ALIAS FOR $1;
minval ALIAS FOR $2;
currval RECORD;
BEGIN
FOR currval IN SELECT minval + enum AS enum FROM enum(numvals) LOOP
RETURN NEXT currval.enum;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE FUNCTION public.enum(int4, int4, int4) RETURNS SETOF
int4 AS ' DECLARE
numvals ALIAS FOR $1;
minval ALIAS FOR $2;
maxval ALIAS FOR $3;
currval RECORD;
/*
From: Harald Fuchs
Date: Wed, October 8, 2003 5:53
To: pgsql-general@postgresql.org

tuple_generator(integer)

which returns a set of numbers whose elements are the integer values
between 1 and the number supplied.

Usage: SELECT * FROM enum (numvals [, minval [, maxval]])
Returns numvals consecutive numbers, beginning with 0 or minval
Wraps around to minval if maxval is reached
*/

BEGIN
FOR currval IN SELECT * FROM enum(numvals, minval) LOOP
RETURN NEXT currval.enum % maxval;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql' VOLATILE;

But, as interesting as these look, what would you actually use them for?

~Berend Tober

In article <64591.66.212.203.144.1065659357.squirrel@$HOSTNAME>,
<btober@seaworthysys.com> writes:

Or a little different, with the over-loaded functions relying on the
original:

CREATE OR REPLACE FUNCTION public.enum(int4) RETURNS SETOF int4 AS '
DECLARE
numvals ALIAS FOR $1;
BEGIN
FOR currval IN 0 .. numvals - 1 LOOP
RETURN NEXT currval;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql' VOLATILE;

Why VOLATILE? Shouldn't that be IMMUTABLE?
(Sorry, but I'm a PostgreSQL newbie.)

But, as interesting as these look, what would you actually use them for?

SELECT extract (month FROM sdate) AS month,
count (*) AS monthly_sales
FROM sales
GROUP BY month
ORDER BY month;

gives you the monthly sales, but what if you would like a result row
also for months with nothing sold?

SELECT enum, count (sdate) AS monthly_sales
FROM enum (12, 1)
LEFT JOIN sales ON enum = extract (month FROM sdate)
GROUP BY enum
ORDER BY enum;

This would do the trick. Is there a more elegant solution?