Re: [GENERAL] RE: [GENERAL] INSERT. RETURNING for copying records

2012-09-07 Thread dinesh kumar
Hi David, I am not sure the RETURNING offers you the following behavior .. < What I'm looking for > +--+-+ | original_rid | rid | +--+-+ | 1| 4 | | 2| 5 | | 3| 6 | +--+-+**

Re: [GENERAL] Packaging of plpython

2012-09-07 Thread Gražvydas Valeika
> > > OK. It seemed to me, that plpython2 and plpython3 were introduced exactly >> for this reason. >> >> Postgres documentation (http://www.postgresql.org/** >> docs/9.1/static/plpython-**python23.html) >> states: >> >> It is not a

Re: [GENERAL] Packaging of plpython

2012-09-07 Thread John R Pierce
On 09/07/12 2:26 PM, Gražvydas Valeika wrote: OK. It seemed to me, that plpython2 and plpython3 were introduced exactly for this reason. Postgres documentation (http://www.postgresql.org/docs/9.1/static/plpython-python23.html) states: It is not allowed to use PL/Python based on Python 2 and

Re: [GENERAL] Packaging of plpython

2012-09-07 Thread Gražvydas Valeika
On Sat, Sep 8, 2012 at 12:05 AM, John R Pierce wrote: > On 09/07/12 1:57 PM, Gražvydas Valeika wrote: > >> >> I don't use RHEL, I use Scientific Linux clone of it. And >> yum.postgresql.org repository packages. It >> contains plpyton2. In Fedora 16 - fedora repostito

Re: [GENERAL] Packaging of plpython

2012-09-07 Thread John R Pierce
On 09/07/12 2:05 PM, John R Pierce wrote: plpython is dependent on python. minor correction, its dependent on python-libs, but the rest still applies. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general maili

Re: [GENERAL] Packaging of plpython

2012-09-07 Thread John R Pierce
On 09/07/12 1:57 PM, Gražvydas Valeika wrote: I don't use RHEL, I use Scientific Linux clone of it. And yum.postgresql.org repository packages. It contains plpyton2. In Fedora 16 - fedora repostitories, plpython2. In Windows - Enterprise DB 32 bit installer, cont

Re: [GENERAL] Packaging of plpython

2012-09-07 Thread Gražvydas Valeika
On Fri, Sep 7, 2012 at 11:27 PM, John R Pierce wrote: > On 09/07/12 12:41 PM, Gražvydas Valeika wrote: > >> >> What is the problem to provide both plpython2 and plpython3, or keep same >> (2 or 3) plpython available by default on both platforms? >> > > what version of Python is included in RHEL o

Re: [GENERAL] Packaging of plpython

2012-09-07 Thread John R Pierce
On 09/07/12 12:41 PM, Gražvydas Valeika wrote: What is the problem to provide both plpython2 and plpython3, or keep same (2 or 3) plpython available by default on both platforms? what version of Python is included in RHEL or Fedora? what version of Python is included in MS Windows ? -- j

Re: [GENERAL] pivot functions with variable number of columns

2012-09-07 Thread Pavel Stehule
2012/9/6 Misa Simic : > Hi Pavel, > > Hm... To me workaround looks as exactly as the same thing? > > 1) uses Dynamic SQL to bulid query (but returns refcursor insted of text) > > 2) client still needs to execute 2 commands (second is fetch instead of > execute 'result') > > However, based on your n

Re: [GENERAL] Packaging of plpython

2012-09-07 Thread Joshua D. Drake
On 09/07/2012 12:41 PM, Gražvydas Valeika wrote: Hi all, somebody please explain what is packaging policy of plpython. Now I see plpython2u packaged in Fedora and RHEL repositories and plpython3u in Windows installer by EnterpriseDB. Why there is such difference and what is advertised way to k

[GENERAL] Packaging of plpython

2012-09-07 Thread Gražvydas Valeika
Hi all, somebody please explain what is packaging policy of plpython. Now I see plpython2u packaged in Fedora and RHEL repositories and plpython3u in Windows installer by EnterpriseDB. Why there is such difference and what is advertised way to keep DB servers in Linux and Windows plpython compati

Re: [GENERAL] Moving several databases into one database with several schemas

2012-09-07 Thread Dmitriy Igrishin
2012/9/7 Merlin Moncure > On Thu, Sep 6, 2012 at 10:12 PM, Edson Richter > wrote: > > Em 06/09/2012 15:40, John R Pierce escreveu: > > > >> On 09/06/12 5:30 AM, Edson Richter wrote: > > You could change the default setting for the user with > > ALTER ROLE someuser SET search_

[GENERAL] RE: [GENERAL] INSERT. RETURNING for copying records

2012-09-07 Thread David Johnston
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Michael Sacket Sent: Friday, September 07, 2012 2:09 PM To: PG-General Mailing List Subject: [GENERAL] INSERT. RETURNING for copying records Good Afternoon, I'm attempting to write a functio

Re: [GENERAL] Multiple indexes, huge table

2012-09-07 Thread Aram Fingal
On Sep 7, 2012, at 11:15 AM, Marti Raudsepp wrote: > There's a pg_bulkload extension which does much faster incremental > index updates for large bulk data imports, so you get best of both > worlds: http://pgbulkload.projects.postgresql.org/ Thanks, I'll have to check that out. This is going t

Re: [GENERAL] Multiple indexes, huge table

2012-09-07 Thread Jeff Janes
On Thu, Sep 6, 2012 at 5:12 PM, Alan Hodgson wrote: > On Thursday, September 06, 2012 05:06:27 PM Jeff Janes wrote: >> For updating 20 million out of 500 million rows, wouldn't a full table >> scan generally be preferable to an index scan anyway? >> > > Not one table scan for each row updated ...

[GENERAL] INSERT… RETURNING for copying records

2012-09-07 Thread Michael Sacket
Good Afternoon, I'm attempting to write a function that will duplicate a few records, but the catch is I need to have a mapping of the original pk to the new pk. I know I can use the RETURNING clause to get the new ids... but how to map that to the original ones is escaping me. < Setup > CRE

Re: [GENERAL] Moving several databases into one database with several schemas

2012-09-07 Thread Merlin Moncure
On Thu, Sep 6, 2012 at 10:12 PM, Edson Richter wrote: > Em 06/09/2012 15:40, John R Pierce escreveu: > >> On 09/06/12 5:30 AM, Edson Richter wrote: You could change the default setting for the user with ALTER ROLE someuser SET search_path=... >>> >>> That is perfect! I can have

Re: [GENERAL] Multiple indexes, huge table

2012-09-07 Thread Marti Raudsepp
On Fri, Sep 7, 2012 at 12:22 AM, Aram Fingal wrote: > Should I write a script which drops all the indexes, copies the data and then > recreates the indexes or is there a better way to do this? There's a pg_bulkload extension which does much faster incremental index updates for large bulk data im

Re: [GENERAL] Multiple indexes, huge table

2012-09-07 Thread Merlin Moncure
On Thu, Sep 6, 2012 at 4:22 PM, Aram Fingal wrote: > I have a table which currently has about 500 million rows. For the most > part, the situation is going to be that I will import a few hundred million > more rows from text files once every few months but otherwise there won't be > any insert

Re: [GENERAL] return text from explain

2012-09-07 Thread Willy-Bas Loos
cool, it does work with RETURN QUERY. Thanx! WBL On Fri, Sep 7, 2012 at 11:00 AM, Виктор Егоров wrote: > Hope this helps: > > CREATE OR REPLACE FUNCTION explain(in_sql text) RETURNS TABLE(explain_line > text) AS $explain$ > BEGIN > RETURN QUERY EXECUTE 'EXPLAIN '||in_sql; > END; > $explain

Re: [GENERAL] return text from explain

2012-09-07 Thread Виктор Егоров
Hope this helps: CREATE OR REPLACE FUNCTION explain(in_sql text) RETURNS TABLE(explain_line text) AS $explain$ BEGIN RETURN QUERY EXECUTE 'EXPLAIN '||in_sql; END; $explain$ LANGUAGE plpgsql; SELECT * FROM explain('SELECT * FROM pg_locks'); -- Victor Y. Yegorov

Re: [GENERAL] return text from explain

2012-09-07 Thread Willy-Bas Loos
On Thu, Sep 6, 2012 at 10:15 PM, Tom Lane wrote: > Bruce Momjian writes: > > On Thu, Sep 6, 2012 at 07:18:50PM +0200, Willy-Bas Loos wrote: > >> Is it possible to use the output of explain as text values? > > > I think you have to do EXPLAIN in a function and call the function. > > Yeah, IIRC y