Re: [GENERAL] WITH AS vs subselect was: count (DISTINCT expression [ , ... ] ) and documentation
On Fri, Dec 26, 2008 at 07:13:48PM -0500, Tom Lane wrote: > Ivan Sergio Borgonovo writes: > >>> David Fetter wrote: > In 8.4, you'll be able to do: > > WITH d AS ( > SELECT DISTINCT c1, c2 FROM table1 > ) > SELECT count(*) FROM d; > > >>> Nice, but what will be the difference from > >>> select count(*) from (select distinct c1, c2 from t); > >>> ? > >>> Optimisation? > > >> None especially. > > > So what would be the advantage compared to subselect? > > None, David just has WITH on the brain ;-) LOL! You're only saying that because it's true ;) Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Bind message has 6 results formats but query has 5 columns
Windows XP, Using Delphi 7 ADO SQL Query Component to Drop/Delete a Column from a simple table. When I then try to query the table I get this error: "Bind message has 6 results formats but query has 5 columns" Obviously I need to refresh the connection or table but how? I have tried ADOConnection.Connected:=False; ADOConnection.Connected:=True; SQLTable.Close; SQLTable.Open; SQLTable.Fieldefs.Refresh; All to no avial. What is my solution to this?? Your contrib would be highly aprreciated. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] "disappearing" rows in temp table, in recursing trigger
Hello, I'm guessing the rows aren't really disappearing but how else to describe it? I have a trigger function that calls another function that is recursive. The recursive function creates a temp table and inserts rows into it. After the recursive function returns, the trigger function examines the temp table in order to validate the data in it. This all works perfectly well when the trigger function is written slightly modified as a regular function. However when run as a trigger, the temp table comes back empty. No errors are thrown. I have version 8.1.10. I've tried to include the relevant parts below. Any smarty out there see the problem? --Eric --= --The trigger is like this: CREATE TRIGGER trigger_name AFTER INSERT OR UPDATE ON table_name FOR EACH ROW EXECUTE PROCEDURE trigger_func() -- --The trigger_func() (abbreviated): begin perform recursive_func(new.id, 1, new.id); l_row_count := count(*) from tmp_ancestors; raise debug 'total rows=%', l_row_count; --LOG OUTPUT SAYS: "total rows=0" return new; end; --The recursive_func(): begin insert into tmp_ancestors (blah, blah)... if logic then new_level := p_level + 1; perform recursive_func(id, new_level, id); end if; l_row_count := count(*) from tmp_ancestors; raise debug 'returning p_level=%; rows in tmp_ancestors=%', p_level, l_row_count; --LOG OUTPUT SHOWS EXPECTED INCREMENTING NUMBERS return; end -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] WITH AS vs subselect was: count (DISTINCT expression [ , ... ] ) and documentation
Ivan Sergio Borgonovo writes: >>> David Fetter wrote: In 8.4, you'll be able to do: WITH d AS ( SELECT DISTINCT c1, c2 FROM table1 ) SELECT count(*) FROM d; >>> Nice, but what will be the difference from >>> select count(*) from (select distinct c1, c2 from t); >>> ? >>> Optimisation? >> None especially. > So what would be the advantage compared to subselect? None, David just has WITH on the brain ;-) The subselect syntax certainly seems like the one most likely to work across different SQL implementations. WITH is a pretty recent addition to the SQL spec, and DISTINCT with multiple aggregate arguments isn't in the spec at all. The COUNT(DISTINCT ROW(x,y)) hack is a cute idea but I'm dubious that that's portable either (it certainly doesn't work in pre-8.4 PG). regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] WITH AS vs subselect was: count (DISTINCT expression [ , ... ] ) and documentation
On Fri, 26 Dec 2008 12:04:48 -0800 David Fetter wrote: > On Fri, Dec 26, 2008 at 08:03:30PM +0100, Ivan Sergio Borgonovo > wrote: > > > > aggregate_name (DISTINCT expression [, expression] ) > > > In 8.4, you'll be able to do: > > > WITH d AS ( > > > SELECT DISTINCT c1, c2 FROM table1 > > > ) > > > SELECT count(*) FROM d; > > Nice, but what will be the difference from > > select count(*) from (select distinct c1, c2 from t); > > ? > > Optimisation? > None especially. So what would be the advantage compared to subselect? > > Furthermore... I was actually looking at docs because I needed to > > find a way supported by both postgresql and mysql > > Generally, it's *not* a good idea to try to support more than one > back-end. You wind up maintaining several disparate code bases, Not really my main target... I was just investigating if it could come for free ;) -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] lack of consequence with domains and types
On Fri, Dec 26, 2008 at 3:57 PM, Grzegorz Jaśkiewicz wrote: > another glance at source code, and docs tells me - that there's not > such thing as default value for custom type - unless that type is > defined as new base scalar type. So probably, that would require > postgresql to allow users to define default values for composite types > as well, like that: > create type foo AS > ( > a int default 1, > b foodomain default 'foo', > > ); don't forget, you can create types via create table: create table foo as ( a int default 1, ... check (a<5) ); create table bar(f foo); insert into bar default values; -- should foo defaults fire?? I say probably, but check constraints should definately be enforced (currently they are not). (since you can alter the table later, there is very little reason not to create types with create table always). merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] lack of consequence with domains and types
another glance at source code, and docs tells me - that there's not such thing as default value for custom type - unless that type is defined as new base scalar type. So probably, that would require postgresql to allow users to define default values for composite types as well, like that: create type foo AS ( a int default 1, b foodomain default 'foo', ); Going through source code, I have no idea where that would go - because I got only experience in creating types + custom indices, not hacking postgresql guts. More help required here, please .. (I don't know, should that go to -hackers [too]. My recent history there probably makes majority of folks to ignore my posts straight away). Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] lack of consequence with domains and types
On Wed, Dec 24, 2008 at 6:41 PM, Erik Jones wrote: > > On Dec 24, 2008, at 12:04 PM, Grzegorz Jaśkiewicz wrote: > >> On Wed, Dec 24, 2008 at 6:12 PM, Erik Jones wrote: >>> >>> Yes, and columns have default values, too, which are not tied to their >>> datatype's default value (if it even has one). ALTER TABLE initializes >>> rows >>> to have the new *column's* default. A column of some domain type could >>> easily have some default other than the domain's default and, in fact, if >>> you don't specify a default for the column then it's default is NULL. >> >> the whole thing about domains, is that you specify type and default, >> and even check constraint. And I did specify default - hence I would >> expect it to be set to that value!! > > You really need to understand the difference between a domain's default and > a column's default. The ALTER TABLE docs specifically say that if you don't > specify a default for the new *column* then that column is set to NULL for > all rows. That is not the same as not providing a value for a column of > some domain type with a default in an INSERT statement. A domain with a > default does not specify that it can not be set to null: I disagree. It's quite natural and reasonable to have defaults passed through the composite type as the OP expects. This is a possible improvement (TODO?) in the way composite types are handled. There are a couple of other loopholes in domans/composite types: * domains can't be stacked in an array (but you can if they are wrapped in a composite type) * check constraints not enforced for composite type on cast (but are for domains) * alter type should be expanded to allow things that are currently possible via alter table (currently a TODO, IIRC), or create table/alter table should be adjusted for better handling of types, and 'create type as' should be deprecated. The latter is what I think should happen, but it's controversial :-). In the meantime the OP has to decide what he wants to use more, composite types or default values on domains. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] lack of consequence with domains and types
I hope Tom can hear my prayers. This basically means, I won't be able to use domains+type in my designs. :/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] count (DISTINCT expression [ , ... ] ) and documentation
On Fri, Dec 26, 2008 at 08:03:30PM +0100, Ivan Sergio Borgonovo wrote: > On Fri, 26 Dec 2008 10:43:25 -0800 > David Fetter wrote: > > > On Fri, Dec 26, 2008 at 03:34:33PM +0100, Ivan Sergio Borgonovo > > wrote: > > > I noticed that starting from 8.2 the documentation at > > > http://www.postgresql.org/docs/8.2/interactive/sql-expressions.html > > > say that multiple distinct expressions are supported > > > > > > aggregate_name (DISTINCT expression [, expression] ) > > > In 8.4, you'll be able to do: > > > WITH d AS ( > > SELECT DISTINCT c1, c2 FROM table1 > > ) > > SELECT count(*) FROM d; > > Nice, but what will be the difference from > select count(*) from (select distinct c1, c2 from t); > ? > Optimisation? None especially. > Furthermore... I was actually looking at docs because I needed to > find a way supported by both postgresql and mysql Generally, it's *not* a good idea to try to support more than one back-end. You wind up maintaining several disparate code bases, all of which must do exactly the same thing, or you create your own RDBMS in your client code, or worst of all, some of each. Unless the most important attribute of the software, i.e. you can jettison any other feature to support it, is to support more than one RDBMS back-end, don't even try. Examples of software which needs to support multiple RDBMS back-ends include, and are pretty much limited to, ERD generators and migration tools. > > and very likely an OLAP version. :) > > What's "an OLAP version" of WITH d AS... OLAP includes clauses like WINDOW() and OVER(), but since it's not committed yet, I don't want to get too far into it :) Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] count (DISTINCT expression [ , ... ] ) and documentation
On Fri, 26 Dec 2008 10:43:25 -0800 David Fetter wrote: > On Fri, Dec 26, 2008 at 03:34:33PM +0100, Ivan Sergio Borgonovo > wrote: > > I noticed that starting from 8.2 the documentation at > > http://www.postgresql.org/docs/8.2/interactive/sql-expressions.html > > say that multiple distinct expressions are supported > > > > aggregate_name (DISTINCT expression [, expression] ) > In 8.4, you'll be able to do: > WITH d AS ( > SELECT DISTINCT c1, c2 FROM table1 > ) > SELECT count(*) FROM d; Nice, but what will be the difference from select count(*) from (select distinct c1, c2 from t); ? Optimisation? Furthermore... I was actually looking at docs because I needed to find a way supported by both postgresql and mysql and I've heard that mysql is not that good at subselect and I doubt it supports WITH AS. (OK not really a postgresql problem...). Meanwhile what would you suggest as a general approach to stuff like select count(distinct c1, c2) from t; regardless of mysql support? and considering mysql support? I was thinking to find some way to exploit group by, but I didn't come to anything useful yet. > and very likely an OLAP version. :) What's "an OLAP version" of WITH d AS... -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] count (DISTINCT expression [ , ... ] ) and documentation
2008/12/26 Ivan Sergio Borgonovo : > On Fri, 26 Dec 2008 16:23:52 +0100 > "Pavel Stehule" wrote: > >> 2008/12/26 Ivan Sergio Borgonovo : >> > On Fri, 26 Dec 2008 15:46:48 +0100 >> > "Pavel Stehule" wrote: >> > >> >> count has only one argument, >> > >> > then what was changed between 8.1 and 8.2 to change the docs? >> > None of the functions listed in: >> > http://www.postgresql.org/docs/8.2/static/functions-aggregate.html >> > seems to support >> > aggregate(distinct exp [,exp]) >> >> http://www.postgresql.org/docs/8.2/interactive/functions-aggregate.html >> >> http://www.postgresql.org/docs/8.2/interactive/functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE > > > yeah but no function seems to support > > aggregate(distinct x, y) > >> CREATE AGGREGATE name ( input_data_type [ , ... ] ) ( >> SFUNC = sfunc, >> STYPE = state_data_type >> [ , FINALFUNC = ffunc ] >> [ , INITCOND = initial_condition ] >> [ , SORTOP = sort_operator ] >> ) > > > OK... but how am I going to implement an user defined aggregate that > support without resorting to C? > > myaggfunc(distinct x, y)? > > Otherwise to what is it referring > > http://www.postgresql.org/docs/8.2/interactive/sql-expressions.html > > aggregate_name (DISTINCT expression [ , ... ] ) > ok, I tested and it isn't supported yet. This is documentation bug. DISTINCT is allowed only for single argument aggregate. Regards Pavel Stehule > -- > Ivan Sergio Borgonovo > http://www.webthatworks.it > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] count (DISTINCT expression [ , ... ] ) and documentation
On Fri, Dec 26, 2008 at 03:34:33PM +0100, Ivan Sergio Borgonovo wrote: > I noticed that starting from 8.2 the documentation at > http://www.postgresql.org/docs/8.2/interactive/sql-expressions.html > say that multiple distinct expressions are supported > > aggregate_name (DISTINCT expression [, expression] ) In 8.4, you'll be able to do: WITH d AS ( SELECT DISTINCT c1, c2 FROM table1 ) SELECT count(*) FROM d; and very likely an OLAP version. :) Cheers, David. > > While previous docs just listed one: > > aggregate_name (DISTINCT expression) > > Still I'm using 8.3 and > > select count(distinct c1, c2) from table1; > > report: > > No function matches the given name and argument types. You might > need to add explicit type casts. > > What should I write in spite of? > > select count(distinct c1, c2) from table1; > > -- > Ivan Sergio Borgonovo > http://www.webthatworks.it > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] count (DISTINCT expression [ , ... ] ) and documentation
On Fri, 26 Dec 2008 16:23:52 +0100 "Pavel Stehule" wrote: > 2008/12/26 Ivan Sergio Borgonovo : > > On Fri, 26 Dec 2008 15:46:48 +0100 > > "Pavel Stehule" wrote: > > > >> count has only one argument, > > > > then what was changed between 8.1 and 8.2 to change the docs? > > None of the functions listed in: > > http://www.postgresql.org/docs/8.2/static/functions-aggregate.html > > seems to support > > aggregate(distinct exp [,exp]) > > http://www.postgresql.org/docs/8.2/interactive/functions-aggregate.html > > http://www.postgresql.org/docs/8.2/interactive/functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE yeah but no function seems to support aggregate(distinct x, y) > CREATE AGGREGATE name ( input_data_type [ , ... ] ) ( > SFUNC = sfunc, > STYPE = state_data_type > [ , FINALFUNC = ffunc ] > [ , INITCOND = initial_condition ] > [ , SORTOP = sort_operator ] > ) OK... but how am I going to implement an user defined aggregate that support without resorting to C? myaggfunc(distinct x, y)? Otherwise to what is it referring http://www.postgresql.org/docs/8.2/interactive/sql-expressions.html aggregate_name (DISTINCT expression [ , ... ] ) -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] This is a limit-offset bug?
2008/12/26 Martijn van Oosterhout : > On Fri, Dec 26, 2008 at 09:52:59AM -0200, Emanuel Calvo Franco wrote: >> Hi people, >> >> Yesterday when i was making some commands on 8.3.5 >> (on Centos) >> i found a rare behavior of limit offset. >> >> Try in psql: >> >> select * from foo limit 3; <- shows ok >> select * from foo limit3; <- shows all rows >> select * from foo offset1223raf3w4t4tgga; <- shows all rows >> select * from foo limitsdfsdfaerfgsafqaweawe; <- shows all rows >> select * from foo limit; <- this shows error ok >> select * from foo limitt; > > At a wild gues, if the string after the table name is a single token it > gets interpreted as an alias for the table. Remember that AS is > optional. > Yes, you're right. i look at: create table foo (i serial); (...inserts by default) select limit111.i from foo limit111; <- this returns all rows on i Is not a limit question, is about table alias. D'oh! Sorry... > Have a nice day, > -- > Martijn van Oosterhout http://svana.org/kleptog/ >> Please line up in a tree and maintain the heap invariant while >> boarding. Thank you for flying nlogn airlines. > > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.6 (GNU/Linux) > > iD8DBQFJVQpyIB7bNG8LQkwRAs9yAJ9Q03c2M9tXKzmaQpCtxBH3f9aquACgj7sr > HJuIKqz1NLp8B8EqlOZkym8= > =Qox3 > -END PGP SIGNATURE- > > -- Emanuel Calvo Franco Syscope Postgresql Consultant ArPUG / AOSUG Member -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] This is a limit-offset bug?
On Fri, Dec 26, 2008 at 09:52:59AM -0200, Emanuel Calvo Franco wrote: > Hi people, > > Yesterday when i was making some commands on 8.3.5 > (on Centos) > i found a rare behavior of limit offset. > > Try in psql: > > select * from foo limit 3; <- shows ok > select * from foo limit3; <- shows all rows > select * from foo offset1223raf3w4t4tgga; <- shows all rows > select * from foo limitsdfsdfaerfgsafqaweawe; <- shows all rows > select * from foo limit; <- this shows error ok > select * from foo limitt; At a wild gues, if the string after the table name is a single token it gets interpreted as an alias for the table. Remember that AS is optional. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] count (DISTINCT expression [ , ... ] ) and documentation
2008/12/26 Ivan Sergio Borgonovo : > On Fri, 26 Dec 2008 15:46:48 +0100 > "Pavel Stehule" wrote: > >> count has only one argument, > > then what was changed between 8.1 and 8.2 to change the docs? > None of the functions listed in: > http://www.postgresql.org/docs/8.2/static/functions-aggregate.html > seems to support > aggregate(distinct exp [,exp]) http://www.postgresql.org/docs/8.2/interactive/functions-aggregate.html http://www.postgresql.org/docs/8.2/interactive/functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE > > Does the change reflect the change in the possibility to write user > defined aggregates that support more then one distinct expression? CREATE AGGREGATE name ( input_data_type [ , ... ] ) ( SFUNC = sfunc, STYPE = state_data_type [ , FINALFUNC = ffunc ] [ , INITCOND = initial_condition ] [ , SORTOP = sort_operator ] ) you are able to write multi param aggregates. regards Pavel Stehule > > The first thing that comes to my mind to emulate > count(distinct a,b) > would be to > > create table test.dist (a int, b int); > insert into test.dist values(1,0); > insert into test.dist values(1,0); > insert into test.dist values(1,1); > insert into test.dist values(0,0); > select count(*) from (select distinct a,b from test.dist ) a; > > but still I can't think of anything that would work with > aggregate(distinct a,b) > not just count. > > -- > Ivan Sergio Borgonovo > http://www.webthatworks.it > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] count (DISTINCT expression [ , ... ] ) and documentation
On Fri, 26 Dec 2008 15:46:48 +0100 "Pavel Stehule" wrote: > count has only one argument, then what was changed between 8.1 and 8.2 to change the docs? None of the functions listed in: http://www.postgresql.org/docs/8.2/static/functions-aggregate.html seems to support aggregate(distinct exp [,exp]) Does the change reflect the change in the possibility to write user defined aggregates that support more then one distinct expression? The first thing that comes to my mind to emulate count(distinct a,b) would be to create table test.dist (a int, b int); insert into test.dist values(1,0); insert into test.dist values(1,0); insert into test.dist values(1,1); insert into test.dist values(0,0); select count(*) from (select distinct a,b from test.dist ) a; but still I can't think of anything that would work with aggregate(distinct a,b) not just count. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] count (DISTINCT expression [ , ... ] ) and documentation
Hello count has only one argument, try: postgres=# select * from fooa; a | b + 10 | 20 (1 row) postgres=# select count(distinct a,b) from fooa; ERROR: function count(integer, integer) does not exist LINE 1: select count(distinct a,b) from fooa; ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. postgres=# select count(distinct (a,b)) from fooa; count --- 1 (1 row) regards Pavel Stehule 2008/12/26 Ivan Sergio Borgonovo : > I noticed that starting from 8.2 the documentation at > http://www.postgresql.org/docs/8.2/interactive/sql-expressions.html > say that multiple distinct expressions are supported > > aggregate_name (DISTINCT expression [, expression] ) > > While previous docs just listed one: > > aggregate_name (DISTINCT expression) > > Still I'm using 8.3 and > > select count(distinct c1, c2) from table1; > > report: > > No function matches the given name and argument types. You might > need to add explicit type casts. > > What should I write in spite of? > > select count(distinct c1, c2) from table1; > > -- > Ivan Sergio Borgonovo > http://www.webthatworks.it > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] count (DISTINCT expression [ , ... ] ) and documentation
I noticed that starting from 8.2 the documentation at http://www.postgresql.org/docs/8.2/interactive/sql-expressions.html say that multiple distinct expressions are supported aggregate_name (DISTINCT expression [, expression] ) While previous docs just listed one: aggregate_name (DISTINCT expression) Still I'm using 8.3 and select count(distinct c1, c2) from table1; report: No function matches the given name and argument types. You might need to add explicit type casts. What should I write in spite of? select count(distinct c1, c2) from table1; -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Compiling 8.4devel on OpenSolaris2008.11 with SunStudioExpress
2008/12/24 Tom Lane : > "Emanuel Calvo Franco" writes: >> But when i want to make, i recieved an error (make and >> /opt/SunStudioExpress/bin/dmake ): >> "... >> eca...@lastchance:~/Desktop/postgresql-snapshot$ sudo make >> You must use GNU make to build PostgreSQL. > > What do you find unclear about that message? Install gmake. > >regards, tom lane > i've suppoused that Sun make o Dmake has compatibilities with gnu make. Works well, thanks -- Emanuel Calvo Franco Syscope Postgresql Consultant ArPUG / AOSUG Member -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] This is a limit-offset bug?
Hi people, Yesterday when i was making some commands on 8.3.5 (on Centos) i found a rare behavior of limit offset. Try in psql: select * from foo limit 3; <- shows ok select * from foo limit3; <- shows all rows select * from foo offset1223raf3w4t4tgga; <- shows all rows select * from foo limitsdfsdfaerfgsafqaweawe; <- shows all rows select * from foo limit; <- this shows error ok select * from foo limitt; All the chars inmediatly continue the limit or offset commands are ignored. If this kind of problems there isn't a bug, sorry. -- Emanuel Calvo Franco Syscope Postgresql Consultant ArPUG / AOSUG Member -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Conditional commit inside functions
2008/12/26 Gerhard Wiesinger : > Hello, > > Aren't there any drawbacks in postgrs on such large transaction (like in > Oracle), e.g if I would use 500.000.000 or even more? for insert no Regards Pavel > > Ciao, > Gerhard > > -- > http://www.wiesinger.com/ > > > On Fri, 26 Dec 2008, Pavel Stehule wrote: > >> Hello >> >> why do you need commit? >> >> pavel >> >> 2008/12/26 Gerhard Wiesinger : >>> >>> Hello! >>> >>> I tried the following, but still one transaction: >>> >>> SELECT insert_1Mio(); >>> >>> (parallel select count(id) from employee; is done) >>> >>> CREATE OR REPLACE FUNCTION insert_some(start_i INTEGER, end_i INTEGER) >>> RETURNS void >>> AS $func$ >>> DECLARE >>> BEGIN >>> FOR i IN start_i..end_i LOOP >>> INSERT INTO employee (id, department, firstname, lastname) VALUES (i, >>> i, >>> 'John' || i, 'Smith' || i); >>> END LOOP; >>> END; >>> $func$ LANGUAGE plpgsql; >>> >>> CREATE OR REPLACE FUNCTION insert_1Mio() RETURNS void >>> AS $func$ >>> DECLARE >>> maxcommit INTEGER; >>> start_i INTEGER; >>> end_i INTEGER; >>> now_i INTEGER; >>> BEGIN >>> maxcommit := 1; >>> start_i :=1; >>> end_i := 100; >>> >>> now_i := start_i; >>> >>> FOR i IN start_i..end_i LOOP >>> IF MOD(i, maxcommit) = 0 THEN >>> PERFORM insert_some(now_i, i); >>> now_i := i + 1; >>> END IF; >>> END LOOP; >>> PERFORM insert_some(now_i, end_i); >>> END; >>> $func$ LANGUAGE plpgsql; >>> >>> Any ideas? >>> >>> Ciao, >>> Gerhard >>> >>> -- >>> http://www.wiesinger.com/ >>> >>> >>> On Thu, 25 Dec 2008, Gerhard Wiesinger wrote: >>> Hello! I want to translate the following Oracle PL/SQL script into plpgsql. Especially I'm having problems with the transaction thing. i tried START TRANSACTION and COMMIT without success. Any ideas? Thanx. Ciao, Gerhard CREATE OR REPLACE PROCEDURE insert_1Mio IS maxcommit NUMBER; BEGIN maxcommit := 1; FOR i IN 1..100 LOOP INSERT INTO employee (id, department, firstname, lastname) VALUES (i, i, 'John' || to_char(i), 'Smith' || to_char(i)); IF MOD(i, maxcommit) = 0 THEN COMMIT; END IF; END LOOP; COMMIT; END; -- http://www.wiesinger.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general >>> >>> -- >>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-general >>> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] get the array value?
2008/12/25 Victor Nawothnig :
> On Thu, Dec 25, 2008 at 7:15 AM, Charles.Hou wrote:
>> name[] = { JOHN , ALEX , TEST ,""}
>>
>> SQL : select name from table1 where 'TEST' = any (name)
>>
>> return: { JOHN , ALEX , TEST }
>>
>> in this sql command, how can i get the index of 'TEST' is 3 ?
>
> First of all. I assume the code above is meant to be pseudo-code, otherwise
> this makes not much sense to me.
>
> But if I understand you correctly, that you want to find the index (or
> position)
> of a specific item in an array, then you have to write a function that
> iterates
> over the array and returns the index.
>
> This is a bad design however and it doesn't scale up well with large arrays.
>
> A better approach is storing the array elements as rows in a table with an
> index, which can be queried more efficiently.
>
> For example:
>
> CREATE TABLE records (
> id SERIAL PRIMARY KEY
> );
>
> CREATE TABLE names (
> record_id INTEGER REFERENCES records,
> position INTEGER NOT NULL,
> name TEXT NOT NULL,
> UNIQUE (record_id, position)
> );
>
> This way you can easily search by doing something like
>
> SELECT position FROM names
> WHERE name = 'TEST';
>
> Regards,
> Victor Nawothnig
I absolutely agree with Victor, arrays doesn't supply normalization
(but in some cases arrays are very useful). You can write SQL function
IndexOf (for small arrays):
postgres=# create or replace function indexof(anyarray, anyelement)
returns integer as $$
select i
from
generate_series(array_lower($1,1),array_upper($1,1)) g(i)
where $1[i] = $2 limit 1;
$$ language sql immutable;
CREATE FUNCTION
postgres=# select indexof(array['Pavel','Jirka'],'Jirka');
indexof
-
2
(1 row)
Regards
Pavel Stehule
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Conditional commit inside functions
Hello, Aren't there any drawbacks in postgrs on such large transaction (like in Oracle), e.g if I would use 500.000.000 or even more? Ciao, Gerhard -- http://www.wiesinger.com/ On Fri, 26 Dec 2008, Pavel Stehule wrote: Hello why do you need commit? pavel 2008/12/26 Gerhard Wiesinger : Hello! I tried the following, but still one transaction: SELECT insert_1Mio(); (parallel select count(id) from employee; is done) CREATE OR REPLACE FUNCTION insert_some(start_i INTEGER, end_i INTEGER) RETURNS void AS $func$ DECLARE BEGIN FOR i IN start_i..end_i LOOP INSERT INTO employee (id, department, firstname, lastname) VALUES (i, i, 'John' || i, 'Smith' || i); END LOOP; END; $func$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION insert_1Mio() RETURNS void AS $func$ DECLARE maxcommit INTEGER; start_i INTEGER; end_i INTEGER; now_i INTEGER; BEGIN maxcommit := 1; start_i :=1; end_i := 100; now_i := start_i; FOR i IN start_i..end_i LOOP IF MOD(i, maxcommit) = 0 THEN PERFORM insert_some(now_i, i); now_i := i + 1; END IF; END LOOP; PERFORM insert_some(now_i, end_i); END; $func$ LANGUAGE plpgsql; Any ideas? Ciao, Gerhard -- http://www.wiesinger.com/ On Thu, 25 Dec 2008, Gerhard Wiesinger wrote: Hello! I want to translate the following Oracle PL/SQL script into plpgsql. Especially I'm having problems with the transaction thing. i tried START TRANSACTION and COMMIT without success. Any ideas? Thanx. Ciao, Gerhard CREATE OR REPLACE PROCEDURE insert_1Mio IS maxcommit NUMBER; BEGIN maxcommit := 1; FOR i IN 1..100 LOOP INSERT INTO employee (id, department, firstname, lastname) VALUES (i, i, 'John' || to_char(i), 'Smith' || to_char(i)); IF MOD(i, maxcommit) = 0 THEN COMMIT; END IF; END LOOP; COMMIT; END; -- http://www.wiesinger.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Conditional commit inside functions
Hello why do you need commit? pavel 2008/12/26 Gerhard Wiesinger : > Hello! > > I tried the following, but still one transaction: > > SELECT insert_1Mio(); > > (parallel select count(id) from employee; is done) > > CREATE OR REPLACE FUNCTION insert_some(start_i INTEGER, end_i INTEGER) > RETURNS void > AS $func$ > DECLARE > BEGIN > FOR i IN start_i..end_i LOOP >INSERT INTO employee (id, department, firstname, lastname) VALUES (i, i, > 'John' || i, 'Smith' || i); > END LOOP; > END; > $func$ LANGUAGE plpgsql; > > CREATE OR REPLACE FUNCTION insert_1Mio() RETURNS void > AS $func$ > DECLARE > maxcommit INTEGER; > start_i INTEGER; > end_i INTEGER; > now_i INTEGER; > BEGIN > maxcommit := 1; > start_i :=1; > end_i := 100; > > now_i := start_i; > > FOR i IN start_i..end_i LOOP >IF MOD(i, maxcommit) = 0 THEN > PERFORM insert_some(now_i, i); > now_i := i + 1; >END IF; > END LOOP; > PERFORM insert_some(now_i, end_i); > END; > $func$ LANGUAGE plpgsql; > > Any ideas? > > Ciao, > Gerhard > > -- > http://www.wiesinger.com/ > > > On Thu, 25 Dec 2008, Gerhard Wiesinger wrote: > >> Hello! >> >> I want to translate the following Oracle PL/SQL script into plpgsql. >> Especially I'm having problems with the transaction thing. i tried START >> TRANSACTION and COMMIT without success. >> >> Any ideas? >> >> Thanx. >> >> Ciao, >> Gerhard >> >> CREATE OR REPLACE PROCEDURE insert_1Mio >> IS >> maxcommit NUMBER; >> BEGIN >> maxcommit := 1; >> >> FOR i IN 1..100 LOOP >> INSERT INTO employee (id, department, firstname, lastname) VALUES (i, i, >> 'John' || to_char(i), 'Smith' || to_char(i)); >> IF MOD(i, maxcommit) = 0 THEN >> COMMIT; >> END IF; >> END LOOP; >> >> COMMIT; >> END; >> >> >> >> -- >> http://www.wiesinger.com/ >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Information about Pages, row versions of tables, indices
2008/12/26 Gerhard Wiesinger :
> Hello Pavel,
>
> Works fine.
>
> Any ideas how to optimzize the function calls to one for the output
> parameters (multiple select from pgstattuple where only one part is used)?
postgres=# select schemaname, tablename, table_len, dead_tuple_count
from (select (pgstattuple(quote_ident(schemaname) || '.' ||
quote_ident(tablename))).*, schemaname, tablename from pg_tables where
schemaname = 'public') a;
schemaname | tablename | table_len | dead_tuple_count
+---+---+--
public | x | 8192 |0
public | foo | 0 |0
public | fooa | 8192 |0
(3 rows)
look on fce pg_size_pretty
postgres=# select schemaname, tablename, pg_size_pretty(table_len),
dead_tuple_count from (select (pgstattuple(quote_ident(schemaname) ||
'.' || quote_ident(tablename))).*, schemaname, tablename from
pg_tables) a;
schemaname |tablename| pg_size_pretty |
dead_tuple_count
+-++--
pg_catalog | pg_type | 48 kB |
0
information_schema | sql_languages | 8192 bytes |
0
information_schema | sql_packages| 8192 bytes |
0
information_schema | sql_parts | 8192 bytes |
0
information_schema | sql_sizing | 8192 bytes |
0
pg_catalog | pg_statistic| 152 kB |
0
information_schema | sql_sizing_profiles | 0 bytes|
0
pg_catalog | pg_database | 8192 bytes |
0
pg_catalog | pg_authid | 112 kB |
0
information_schema | sql_features| 56 kB |
0
information_schema | sql_implementation_info | 8192 bytes |
0
pg_catalog | pg_ts_config_map| 16 kB |
0
pg_catalog | pg_ts_dict | 8192 bytes |
0
pg_catalog | pg_ts_parser| 8192 bytes |
0
pg_catalog | pg_ts_template | 8192 bytes |
0
pg_catalo
regards
Pavel Stehule
>
> I've included some selects which might be usefull for others, too.
>
> Thnx.
call
>
> Ciao,
> Gerhard
>
> --
> http://www.wiesinger.com/
>
> ---
> -- Table info
> ---
>
> SELECT schemaname,
> tablename,
> pg_relpages(schemaname || '.' || tablename) AS rel_pages,
> (SELECT ROUND(table_len/1024.0/1024.0,3) FROM pgstattuple(schemaname
> || '.' || tablename)) AS table_len_MB,
> (SELECT tuple_count FROM pgstattuple(schemaname || '.' || tablename))
> AS tuple_count,
> (SELECT ROUND(tuple_len/1024.0/1024.0,3) FROM pgstattuple(schemaname
> || '.' || tablename)) AS tuple_len_MB,
> (SELECT tuple_percent FROM pgstattuple(schemaname || '.' ||
> tablename)) AS tuple_percent,
> (SELECT dead_tuple_count FROM pgstattuple(schemaname || '.' ||
> tablename)) AS dead_tuple_count,
> (SELECT ROUND(dead_tuple_len/1024.0/1024.0,3) FROM
> pgstattuple(schemaname || '.' || tablename)) AS dead_tuple_len_MB,
> (SELECT dead_tuple_percent FROM pgstattuple(schemaname || '.' ||
> tablename)) AS dead_tuple_percent,
> (SELECT ROUND(free_space/1024.0/1024.0,3) FROM pgstattuple(schemaname
> || '.' || tablename)) AS free_space_MB,
> (SELECT free_percent FROM pgstattuple(schemaname || '.' || tablename))
> AS free_percent
> FROM
> (SELECT cl.oid AS oid,
> cl.relkind AS relkind,
> relowner AS relowner,
> n.nspname AS schemaname,
> relname AS relname,
> CASE
> WHEN cl.relkind = 'r' THEN relname
> WHEN cl.relkind = 'i' THEN (SELECT relname FROM pg_index pi,
> pg_class cx WHERE cl.oid=pi.indexrelid and pi.indrelid=cx.oid)
> WHEN cl.relkind = 't' THEN relname
> ELSE null
> END AS tablename,
> reltoastrelid as reltoastrelid,
> reltoastidxid as reltoastidxid,
> reltype AS reltype,
> reltablespace AS reltablespace,
> CASE
> WHEN cl.relkind = 'i' THEN 0.0
> ELSE pg_relation_size(cl.oid)
> END AS tablesize,
> pg_relation_size(cl.oid),
> -- pg_relation_size(cl.relname) AS tablesize,
> CASE
> WHEN cl.relkind = 'r' THEN CAST('TABLE' AS VARCHAR(20))
> WHEN cl.relkind = 'i' THEN
>CASE WHEN cl.oid in (SELECT pi.indexrelid FROM pg_index pi,
> pg_class pc, pg_class pt WHERE pi.indexrelid = pt.oid AND pi.indexrelid =
> pc.reltoastidxid ORDER BY pi.indexrelid)
> THEN CAST('INDEX OF TOAST TABLE' AS VARCH
Re: [GENERAL] Information about Pages, row versions of tables, indices
Hello Pavel,
Works fine.
Any ideas how to optimzize the function calls to one for the output
parameters (multiple select from pgstattuple where only one part is used)?
I've included some selects which might be usefull for others, too.
Thnx.
Ciao,
Gerhard
--
http://www.wiesinger.com/
---
-- Table info
---
SELECT schemaname,
tablename,
pg_relpages(schemaname || '.' || tablename) AS rel_pages,
(SELECT ROUND(table_len/1024.0/1024.0,3) FROM
pgstattuple(schemaname || '.' || tablename)) AS table_len_MB,
(SELECT tuple_count FROM pgstattuple(schemaname || '.' ||
tablename)) AS tuple_count,
(SELECT ROUND(tuple_len/1024.0/1024.0,3) FROM
pgstattuple(schemaname || '.' || tablename)) AS tuple_len_MB,
(SELECT tuple_percent FROM pgstattuple(schemaname || '.' ||
tablename)) AS tuple_percent,
(SELECT dead_tuple_count FROM pgstattuple(schemaname || '.' ||
tablename)) AS dead_tuple_count,
(SELECT ROUND(dead_tuple_len/1024.0/1024.0,3) FROM
pgstattuple(schemaname || '.' || tablename)) AS dead_tuple_len_MB,
(SELECT dead_tuple_percent FROM pgstattuple(schemaname || '.' ||
tablename)) AS dead_tuple_percent,
(SELECT ROUND(free_space/1024.0/1024.0,3) FROM
pgstattuple(schemaname || '.' || tablename)) AS free_space_MB,
(SELECT free_percent FROM pgstattuple(schemaname || '.' ||
tablename)) AS free_percent
FROM
(SELECT cl.oid AS oid,
cl.relkind AS relkind,
relowner AS relowner,
n.nspname AS schemaname,
relname AS relname,
CASE
WHEN cl.relkind = 'r' THEN relname
WHEN cl.relkind = 'i' THEN (SELECT relname FROM pg_index pi,
pg_class cx WHERE cl.oid=pi.indexrelid and pi.indrelid=cx.oid)
WHEN cl.relkind = 't' THEN relname
ELSE null
END AS tablename,
reltoastrelid as reltoastrelid,
reltoastidxid as reltoastidxid,
reltype AS reltype,
reltablespace AS reltablespace,
CASE
WHEN cl.relkind = 'i' THEN 0.0
ELSE pg_relation_size(cl.oid)
END AS tablesize,
pg_relation_size(cl.oid),
-- pg_relation_size(cl.relname) AS tablesize,
CASE
WHEN cl.relkind = 'r' THEN CAST('TABLE' AS VARCHAR(20))
WHEN cl.relkind = 'i' THEN
CASE WHEN cl.oid in (SELECT pi.indexrelid FROM pg_index
pi, pg_class pc, pg_class pt WHERE pi.indexrelid = pt.oid AND
pi.indexrelid = pc.reltoastidxid ORDER BY pi.indexrelid)
THEN CAST('INDEX OF TOAST TABLE' AS VARCHAR(20))
ELSE CAST('INDEX' AS VARCHAR(20))
END
WHEN cl.relkind = 'S' THEN CAST('SEQUENCE' AS VARCHAR(20))
WHEN cl.relkind = 's' THEN CAST('SPECIAL' AS VARCHAR(20))
WHEN cl.relkind = 'v' THEN CAST('VIEW' AS VARCHAR(20))
WHEN cl.relkind = 't' THEN CAST('TOAST TABLE' AS
VARCHAR(18))
ELSE null
END AS object_type,
CASE
WHEN cl.relkind = 'r' THEN
COALESCE((SELECT
SUM(pg_relation_size(indexrelid))::bigint
FROM pg_index WHERE cl.oid=indrelid), 0)
ELSE pg_relation_size(cl.oid)
END AS indexsize,
CASE
WHEN reltoastrelid=0 THEN 0
ELSE pg_relation_size(reltoastrelid)
END AS toastsize,
CASE
WHEN reltoastrelid=0 THEN 0
ELSE pg_relation_size((SELECT reltoastidxid FROM pg_class ct
WHERE cl.reltoastrelid = ct.oid))
END AS toastindexsize
FROM pg_class cl
LEFT OUTER JOIN pg_namespace n ON n.oid = cl.relnamespace
) ss
WHERE schemaname='public'
AND object_type='TABLE'
ORDER BY
schemaname, tablename;
---
-- Table & Index info
---
SELECT schemaname,
tablename,
object_type,
relname,
pg_relpages(schemaname || '.' || tablename) AS rel_pages,
(SELECT ROUND(table_len/1024.0/1024.0,3) FROM
pgstattuple(schemaname || '.' || relname)) AS table_len_MB,
(SELECT tuple_count FROM pgstattuple(schemaname || '.' || relname))
AS tuple_count,
(SELECT ROUND(tuple_len/1024.0/1024.0,3) FROM
pgstattuple(schemaname || '.' || relname)) AS tuple_len_MB,
(SELECT tuple_percent FROM pgstattuple(schemaname || '.' ||
relname)) AS tuple_percent,
(SELECT dead_tuple_count FROM pgstattuple(schemaname || '.' ||
relname)) AS dead_tuple_count,
(SELECT ROUND(dead_tuple_len/1024.0/1024.0,3) FROM
pgstattuple(schemaname || '.' || relname)) AS dead_tuple_len_MB,
Re: [GENERAL] Conditional commit inside functions
Hello! I tried the following, but still one transaction: SELECT insert_1Mio(); (parallel select count(id) from employee; is done) CREATE OR REPLACE FUNCTION insert_some(start_i INTEGER, end_i INTEGER) RETURNS void AS $func$ DECLARE BEGIN FOR i IN start_i..end_i LOOP INSERT INTO employee (id, department, firstname, lastname) VALUES (i, i, 'John' || i, 'Smith' || i); END LOOP; END; $func$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION insert_1Mio() RETURNS void AS $func$ DECLARE maxcommit INTEGER; start_i INTEGER; end_i INTEGER; now_i INTEGER; BEGIN maxcommit := 1; start_i :=1; end_i := 100; now_i := start_i; FOR i IN start_i..end_i LOOP IF MOD(i, maxcommit) = 0 THEN PERFORM insert_some(now_i, i); now_i := i + 1; END IF; END LOOP; PERFORM insert_some(now_i, end_i); END; $func$ LANGUAGE plpgsql; Any ideas? Ciao, Gerhard -- http://www.wiesinger.com/ On Thu, 25 Dec 2008, Gerhard Wiesinger wrote: Hello! I want to translate the following Oracle PL/SQL script into plpgsql. Especially I'm having problems with the transaction thing. i tried START TRANSACTION and COMMIT without success. Any ideas? Thanx. Ciao, Gerhard CREATE OR REPLACE PROCEDURE insert_1Mio IS maxcommit NUMBER; BEGIN maxcommit := 1; FOR i IN 1..100 LOOP INSERT INTO employee (id, department, firstname, lastname) VALUES (i, i, 'John' || to_char(i), 'Smith' || to_char(i)); IF MOD(i, maxcommit) = 0 THEN COMMIT; END IF; END LOOP; COMMIT; END; -- http://www.wiesinger.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
