Re: [GENERAL] PLPGSQL SETOF functions

1) If you declare a return type setof TABLENAME the resultset will contain rows with field definitions like the table. 2) To call the function from another plpgsql function use: declare row record begin for row in select * from dates_pkg.getbusinessdays(...) Loop ...process...

Re: [GENERAL] Multi-tenancy in Postgres

On Tue, 28 Jun 2011 17:04:54 -0600, Rob Sargent wrote: On 06/28/2011 04:52 PM, Greg Smith wrote: On 06/28/2011 05:45 PM, Rob Sargent wrote: I think Greg might be forgetting that some of us don't always get to choose what we work on. I was in a shop that decided to go with multi-tenancy for re

Re: [GENERAL] rationale behind quotes for camel case?

Le mardi 28 juin 2011 à 11:09 -0500, dennis jenkins a écrit : > Any suggestions on how to name tables when table names contain both > multi-word nouns and mutli-table "many-many" mappings? > [..] > The real table names are normal English words with subjective > meaning. Not sure what you mean

[GENERAL] Real type with zero

Hello, how I can tell my postgresql to store last zero of real type ? I put value 2.30 and when I select that column i see 2.3 without zero. -- Regards, Condor -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org

Re: [GENERAL] Real type with zero

what you probably looking for is formatting the output into a string. Postgresql will store it as 2.3, because that is what 2.30 is anyway. Its up to you to format it before passing it on to the user/business logic/whatever. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To

[GENERAL] LOCK TABLE permission requirements

I've been looking around in the 9.0 documentation, but couldn't find the permission requirements for LOCK TABLE (in particular, LOCK TABLE IN SHARE MODE). From the source, you need at least one of UPDATE, DELETE or TRUNCATE. Is there a reason why the INSERT privilege is not sufficient for LOCK TA

Re: [GENERAL] Multi-tenancy in Postgres

Thank you so far for your perspectives on this. I especially agree some of the things raised by Radoslaw and Rob. While it may not be common to come across a scenario where this type of approach fits, I would like to point out that this type of solution is built into some commercial DBMS solution

Re: [GENERAL] Real type with zero

Your question may suggest you are more interested in storing value, as decimal not real, it's more secure to use this way for moneys, but even with decimal your trailing zeros may be removed. Regards, Radoslaw Smogura -Original Message- From: Condor Sent: 29 czerwca 2011 13:24 To: pgsql

Re: [GENERAL] Real type with zero

On 29/06/2011 7:24 PM, Condor wrote: Hello, how I can tell my postgresql to store last zero of real type ? I put value 2.30 and when I select that column i see 2.3 without zero. The real data type is an IEEE 754 floating point number. See: http://en.wikipedia.org/wiki/Floating_point http://st

Re: [GENERAL] point types in "DISTINCT" queries

On Wed, Jun 29, 2011 at 06:53, Jeff Davis wrote: > On Tue, 2011-06-28 at 18:56 -0400, Jonathan S. Katz wrote: > >> I looked into the mailing list archives and found a potential answer >> on this thread: >> http://archives.postgresql.org/pgsql-general/2009-10/msg01122.php >> However I wanted to see

Re: [GENERAL] PLPGSQL SETOF functions

Please reply to the list in the future. I don't believe you can do that. Sim On 06/29/2011 04:39 PM, David Greco wrote: Thanks that works pretty well. Is it possible to fetch the all the return of dates_pkg.getbusinessdays() into a single variable at once? i.e. in Oracle I would do somethin

Re: [GENERAL] point types in "DISTINCT" queries

On Wed, Jun 29, 2011 at 16:38, Jonathan S. Katz wrote: > On Jun 29, 2011, at 10:25 AM, Magnus Hagander wrote: > >> On Wed, Jun 29, 2011 at 06:53, Jeff Davis wrote: >>> >>> On Tue, 2011-06-28 at 18:56 -0400, Jonathan S. Katz wrote: >>> I looked into the mailing list archives and found a pote

Re: [GENERAL] point types in "DISTINCT" queries

On Jun 29, 2011, at 10:25 AM, Magnus Hagander wrote: On Wed, Jun 29, 2011 at 06:53, Jeff Davis wrote: On Tue, 2011-06-28 at 18:56 -0400, Jonathan S. Katz wrote: I looked into the mailing list archives and found a potential answer on this thread: http://archives.postgresql.org/pgsql-genera

Re: [GENERAL] point types in "DISTINCT" queries

On Jun 29, 2011, at 10:42 AM, Magnus Hagander wrote: > On Wed, Jun 29, 2011 at 16:38, Jonathan S. Katz > wrote: >> In fact that is my use-case - I will be performing nearest-neighbor lookups >> (and will be running 9.1b2 on this data set shortly). However, because most >> of the geospatial work

Re: [GENERAL] Real type with zero

-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Grzegorz Jaskiewicz Sent: Wednesday, June 29, 2011 7:44 AM To: con...@stz-bg.com Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Real type with zero what you probab

Re: [GENERAL] point types in "DISTINCT" queries

On Wed, 2011-06-29 at 11:37 -0400, Jonathan S. Katz wrote: > Which means it *should* work, but first I would need to clean up the data and > find the duplicates. I was hoping this might work: > > SELECT geocode, count(*) > FROM a > GROUP BY a.geocode > HAVING count(*) > 1

[GENERAL] Windows x64 : How do I get OSSP-UUID.sql contrib for postgresql x64

The installer for windows for 64bit versions of postgresql doesn't include ossp-uuid.sql. Does anyone know where or how to get this? Thanks -- *Grace Batumbya* Research Assistant | Seneca CDOT Phone: 416-491-5050 x3548 cdot.senecac.on.ca

Re: [GENERAL] Real type with zero

On Jun 29, 2011, at 9:50 AM, David Johnston wrote: > Aside from storing the "true" precision in a separate integer field what > solution is there is this situation. I think the only other way would be a custom data type encapsulating those 2 bits of info. Which might be the best solution, since

Re: [GENERAL] point types in "DISTINCT" queries

> On Wed, 2011-06-29 at 11:37 -0400, Jonathan S. Katz wrote: >> Which means it *should* work, but first I would need to clean up the data >> and find the duplicates. I was hoping this might work: >> >> SELECT geocode, count(*) >> FROM a >> GROUP BY a.geocode >> HAVING count(

Re: [GENERAL] Windows x64 : How do I get OSSP-UUID.sql contrib for postgresql x64

Hi Grace-san. Is this helpful to you? http://winpg.jp/~saito/pg_work/OSSP_win32/build-x86-64/ Regards, Hiroshi Saito (2011/06/30 1:14), Grace Batumbya wrote: The installer for windows for 64bit versions of postgresql doesn't include ossp-uuid.sql. Does anyone know where or how to get this? Th

Re: [GENERAL] Windows x64 : How do I get OSSP-UUID.sql contrib for postgresql x64

Grace the ossp-uuid libraries have no make system to create a 64 bit version and I guess there are some technical reasons with the compiler.  You can run them on Linux 64 bit and Windows 32 bit only.  This is the reason I'd like to see native UUID support built in to the datatype. Best Regards

Re: [GENERAL] Windows x64 : How do I get OSSP-UUID.sql contrib for postgresql x64

Thanks... I didn't know that this had been done. Will be downloading shortly, thanks Mike Gould "Hiroshi Saito" wrote: > Hi Grace-san. > > Is this helpful to you? > http://winpg.jp/~saito/pg_work/OSSP_win32/build-x86-64/ > > Regards, > Hiroshi Saito > > (2011/06/30 1:14), Grace Batumbya wrot

Re: [GENERAL] Windows x64 : How do I get OSSP-UUID.sql contrib for postgresql x64

Thanks Hiroshi, that solved the problem. If you do not mind, how did you go about to build ossp-uuid? *Grace Batumbya* Research Assistant | Seneca CDOT Phone: 416-491-5050 x3548 cdot.senecac.on.ca On 6/29/2011 12:38 PM, Hiroshi Saito wrote: Hi Grace-san. Is this he

Re: [GENERAL] Windows x64 : How do I get OSSP-UUID.sql contrib for postgresql x64

Hi. here is an excuse... http://archives.postgresql.org/pgsql-general/2011-06/msg00738.php Regard, Hiroshi Saito (2011/06/30 1:50), Grace Batumbya wrote: Thanks Hiroshi, that solved the problem. If you do not mind, how did you go about to build ossp-uuid? *Grace Batumbya* Research Assistant |

[GENERAL] Long Query and User Session

Hi! I want to ask that what happens if a long query running and user session timeout reached? 1.) For example: somebody starts a very long query or statistical stored procedure. The session timeout is 5 minutes, and the session exhausted this time. What happens? a.) The Query/STP automat

[GENERAL] How did I get 8 Exclusive locks on the same table? And how many locks is too many?

Hi, We use the fine Bucardo check_postgres Nagios plugin, and it reported a "CRITICAL" level spike in database locks (171 locks). I looked closely at my database logs and found an even bigger spike just a few minutes earlier (208 locks). I saw 8 EXCLUSIVE locks on table X. All of these queri

Re: [GENERAL] How did I get 8 Exclusive locks on the same table? And how many locks is too many?

In response to Aleksey Tsalolikhin : > > We use the fine Bucardo check_postgres Nagios plugin, > and it reported a "CRITICAL" level spike in database locks > (171 locks). > > I looked closely at my database logs and found an even bigger spike > just a few minutes earlier (208 locks). > > I saw

Re: [GENERAL] FOREIGN TABLE with dblink

Hi Shigeru, thank you for responding. This is going to be a great feature! Regards, Jasmin 2011/6/29 Shigeru Hanada > Hi Jasmin, > > (2011/06/16 19:40), Jasmin Dizdarevic wrote: > > Hi, > > > > is there any way to use the new foreign table feature with dblink? > > That's almost clear to me: >

[GENERAL] Inheritence issue scheme advice?

I'm obviously new. But making great progress in PostgreSQL with my new application... Setup: I'm running on MAC. Postgre 9.0.4 Virtual Machine with application dev in Linux. Problem: I like many other have come across the inherit issues. I found the thread here about such issue... http://pos

Re: [GENERAL] Inheritence issue scheme advice?

On 2011-06-29 22:54, Casey Havenor wrote: Problem: I like many other have come across the inherit issues. I found the thread here about such issue... http://postgresql.1045698.n5.nabble.com/FK-s-to-refer-to-rows-in-inheritance-child-td3287684.html I grabbed the "fk_inheritance.v1.patch" file

Re: [GENERAL] Real type with zero

-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of David Johnston Sent: Wednesday, June 29, 2011 11:51 AM To: 'Grzegorz Jaśkiewicz'; con...@stz-bg.com Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Real type with ze

Re: [GENERAL] LOCK TABLE permission requirements

On Wed, Jun 29, 2011 at 7:48 AM, Florian Weimer wrote: > I've been looking around in the 9.0 documentation, but couldn't find the > permission requirements for LOCK TABLE (in particular, LOCK TABLE IN > SHARE MODE).  From the source, you need at least one of UPDATE, DELETE > or TRUNCATE. > > Is th

Re: [GENERAL] Windows x64 : How do I get OSSP-UUID.sql contrib for postgresql x64

On 30/06/2011 12:42 AM, Michael Gould wrote: Grace the ossp-uuid libraries have no make system to create a 64 bit version and I guess there are some technical reasons with the compiler. You can run them on Linux 64 bit and Windows 32 bit only. This is the reason I'd like to see native UUID

Re: [GENERAL] Inheritence issue scheme advice?

What has been your take on the patch - any long term drawbacks or any other functions / triggers that I'll have to stay away from when using this patch within PostgreSQL? Also any tutorials on how to apply the patch under windows/Linux/mac would be appreciated? - Warmest regards, Casey Ha