Re: [GENERAL] N + 1 replication

> Hi, > > Does anyone know if there is any N + 1 replication for Postgres? > Could someone please point me to the right direction? I would build up a multimaster-cluster with the common software like Bucardo, PgPool, PgCLuster or Sequoia and connect it with Slony-I + Heartbeat. -- Greetings, Rob

Re: [GENERAL] Moving avg using SQL

In response to ? : > How can I do a moving avg by only using SQL? Which version do you have? Since 8.4 we have CTE aka windowing functions, a simple axample: test=*# select n, last_value(n) over mywin , avg(n) over mywin from generate_series(1,20) n window mywin as (partition by (n-1)/

Re: [GENERAL] How to stop a query

hello 2009/9/4 A B : > Hi. > How can I abort a query that I see is listed in > > select * from pg_stat_activity; > look on pg_cancel_backend function http://www.postgresql.org/docs/8.2/static/functions-admin.html regards Pavel Stehule > -- > Sent via pgsql-general mailing list (pgsql-general@p

Re: [GENERAL] How to stop a query

Le vendredi 4 septembre 2009 à 07:37:20, A B a écrit : > Hi. > How can I abort a query that I see is listed in > > select * from pg_stat_activity; You have to do: SELECT pg_cancel_backend(pid of the postgres process); -- Guillaume. http://www.postgresqlfr.org http://dalibo.com -- Sent vi

[GENERAL] custom datatype - rgb color

Hi, I have a products table, with id, name, price columns. the products are shoes. now, they have a color , and in the UI we have a color palette so the user can search the shoes for colors. example: the user searches for red - [255,0,0], so i must give him all shoes close to red in color. i add th

Re: [GENERAL] libpq performance

Juan Backson wrote: Hi, For this problem, what if I use prepared statement? if I use prepared statement directly from libpq, would it help? It will possibly change the way postgres plans the query, so you may get a different execution time. http://www.postgresql.org/docs/current/static/sq

Re: [GENERAL] libpq performance

Hi, For this problem, what if I use prepared statement? if I use prepared statement directly from libpq, would it help? Thanks, JB On Mon, Aug 24, 2009 at 9:59 AM, Craig Ringer wrote: > On Mon, 2009-08-24 at 00:08 +0800, Juan Backson wrote: > > > I have a sql that only takes 0.3 ms to run when

[GENERAL] How to stop a query

Hi. How can I abort a query that I see is listed in select * from pg_stat_activity; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Moving avg using SQL

How can I do a moving avg by only using SQL?

Re: [GENERAL] easy task: concurrent select-updates

Kevin McConnell wrote: CREATE OR REPLACE FUNCTION public.getmsg() RETURNS integer LANGUAGE plpgsql AS $function$ declare rec record; begin for rec in select id from msg where busy =alse order by id loop update msg set busy =rue where id = rec.id and busy = false;

[GENERAL] N + 1 replication

Hi, Does anyone know if there is any N + 1 replication for Postgres? Could someone please point me to the right direction? Thanks, JB

Re: [GENERAL] New server disk setup?

On Thu, Sep 3, 2009 at 2:08 PM, Adam Rich wrote: > > Hello, > I'm building a new server on RHEL 5.3 and was wondering if there was an > optimized build guide published somewhere with guidelines on disk > partitioning, filesystems, etc?  For example, do you recommend putting the > data on an ext2 pa

[GENERAL] Got could not truncate directory "pg_multixact/offsets": apparent wraparound

Hello, running 8.4 on Centos. Been running production for 6 months. Never saw this message until tonight: LOG: could not truncate directory "pg_multixact/offsets": apparent wraparound In case it helps... Output of pg_controldata: Latest checkpoint's NextMultiXactId: 145725622 Latest check

Re: [GENERAL] Getting "insufficient data left in message" on copy with binary

Tom Lane-2 wrote: > > Gordon Shannon writes: > >> ERROR: insufficient data left in message >> CONTEXT: COPY mytable, line 1, column provider_id > >> Anybody seen this? > > No. Can you extract a self-contained test case? > Got it. The problem was a combination of 2 mis-matched dat

Re: [GENERAL] How do I store tables on a remote host?

On Thu, Sep 3, 2009 at 6:10 PM, Sergey Samokhin wrote: > Hello. > > Is this possible to move a table from one machine to another and then > redirect queries involving this table to the remote host in a > transparent way? > > As I have just read, tablespaces let us define where given tables > should

[GENERAL] trick problem

Consider the following relational schema about daily stock prices. StockPrice (stockid; timeid; price) We make the simplifying assumption that timeid is an integer that count the number of days from a particular date in the past (that is, the stock prices on the x-th day can be found by a simple se

Re: [GENERAL] pg_ctl with unix domain socket?

On Thursday 03 September 2009 12:20:02 pm Josef Wolf wrote: > On Tue, Sep 01, 2009 at 04:47:25PM +0200, Josef Wolf wrote: > > On Tue, Sep 01, 2009 at 06:34:21AM -0700, Adrian Klaver wrote: > > > On Tuesday 01 September 2009 4:28:22 am Josef Wolf wrote: > > > > [ ... ] > > > > > > Any hints how to u

[GENERAL] How do I store tables on a remote host?

Hello. Is this possible to move a table from one machine to another and then redirect queries involving this table to the remote host in a transparent way? As I have just read, tablespaces let us define where given tables should be stored on the file system. What I'm looking for is like tablespac

Re: [GENERAL] column level, uid based authorization to update columns

* Gauthier, Dave (dave.gauth...@intel.com) wrote: > In linux, given the linux based uid of the user, how might someone implement > column level update restrictions on a uid basis? For example... The first issue is getting the linux uid to equate to a PG role. That can be done using 'ident' auth

Re: [GENERAL] query speed question

> > I have two tables that are georeferenced (although in this case I'm > not using PostGIS) that I need to join. > > A ( lat | lon | depth | value) > > |A| = 1,100,000 > > > > B ( lat | lon | attributes) > > |B| = 14,000,000 > > > > A is a special case because the lat / lon values are all at half

Re: [GENERAL] Regex substring help

On Thu, Sep 03, 2009 at 03:22:12PM -0700, Nick wrote: > Im trying to get all the text before the '' tag. > > SELECT SUBSTRING('onetwothree','(^.*).*$'); > > returns "onetwo" > > How do I get it to return "one"? You can either use a non-greedy regex like this: SELECT substring('onetwothree','(^

[GENERAL] Regex substring help

Im trying to get all the text before the '' tag. SELECT SUBSTRING('onetwothree','(^.*).*$'); returns "onetwo" How do I get it to return "one"? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-ge

Re: [GENERAL] Snow Leopard and plpython problem

On Sep 3, 2009, at 11:01 AM, Jan Otto wrote: hi jerry, Hi I am having a problem building Postgresql 8.4.0 in Snow Leopard. I cannot get plpython to build... I have installed MacPython 2.6.2 in /Library/Frameworks/ Python.framework do you really need python 2.6.2? snow leopard has python 2

Re: [GENERAL] What happens when syslog gets blocked?

decibel wrote: > On Aug 6, 2009, at 2:00 PM, Bill Moran wrote: > >Well ... "life better" really depends on which failure scenario you're > >more comfortable with ... personally, I'd rather lose log messages > >than > >have the DB system go down. Of course, if auditing is critical to > >your > >sc

Re: [GENERAL] query speed question

Christopher Condit wrote: > > I have two tables that are georeferenced (although in this case I'm not using > PostGIS) that I need to join. > A ( lat | lon | depth | value) > |A| = 1,100,000 > > B ( lat | lon | attributes) > |B| = 14,000,000 > > A is a special case because the lat / lon values

Re: [GENERAL] Add Large Object support to database programmatically

acordner wrote: > .CommandText = "SET search_path = public; CREATE DOMAIN lo AS > pg_catalog.oid;" _ > & "CREATE FUNCTION lo_oid(lo) RETURNS pg_catalog.oid > AS" _ > & "'SELECT $1::pg_catalog.oid' LANGUAGE SQL STRICT > IMMUTABLE;" _ >

[GENERAL] query speed question

I have two tables that are georeferenced (although in this case I'm not using PostGIS) that I need to join. A ( lat | lon | depth | value) |A| = 1,100,000 B ( lat | lon | attributes) |B| = 14,000,000 A is a special case because the lat / lon values are all at half degree intervals (ie 33.5, -12

[GENERAL] Add Large Object support to database programmatically

I have been working to create a VB6 program to automatically create a PostgreSQL database, tables and triggers for an application I am updating. I have everything working great, except one of my tables needs to store a bitmap image. I am using the Large Object (lo) contrib module to do this. Using

Re: [GENERAL] add a value to an ENUM type

On 03/09/09 22.13, APseudoUtopia wrote: On Thu, Sep 3, 2009 at 12:52 PM, Edoardo Panfili wrote: I have an enum type CREATE TYPE shapeName AS ENUM('rectangle','circle'); now I need another value: 'square' the pg_type.oid of 'shapename is 16458 It works, but Is it safe to use this? insert into

Re: [GENERAL] Getting "insufficient data left in message" on copy with binary

Gordon Shannon writes: > I'm running 8.4 on Linux/Centos. I am doing a "copy (select ) to > '/absolute/path/to/file.dat' with binary". That works fine. But when I load > that file into a table... > copy mytable (id, mlid, parent_mlid, author_id, date_id, time_id, > content_type_id, provid

Re: [GENERAL] add a value to an ENUM type

On Thu, Sep 3, 2009 at 12:52 PM, Edoardo Panfili wrote: > I have an enum type > CREATE TYPE shapeName AS ENUM('rectangle','circle'); > > now I need another value: 'square' > > the pg_type.oid of 'shapename is 16458 > > It works, but Is it safe to use this? > insert into pg_enum (enumtypid,enumlabel

[GENERAL] New server disk setup?

Hello, I'm building a new server on RHEL 5.3 and was wondering if there was an optimized build guide published somewhere with guidelines on disk partitioning, filesystems, etc? For example, do you recommend putting the data on an ext2 partition mounted noatime, and the logs on ext3? Or shoul

[GENERAL] Getting "insufficient data left in message" on copy with binary

Hello, I'm running 8.4 on Linux/Centos. I am doing a "copy (select ) to '/absolute/path/to/file.dat' with binary". That works fine. But when I load that file into a table... copy mytable (id, mlid, parent_mlid, author_id, date_id, time_id, content_type_id, provider_id, is_duplica

Re: [GENERAL] pg_ctl with unix domain socket?

On Tue, Sep 01, 2009 at 04:47:25PM +0200, Josef Wolf wrote: > On Tue, Sep 01, 2009 at 06:34:21AM -0700, Adrian Klaver wrote: > > On Tuesday 01 September 2009 4:28:22 am Josef Wolf wrote: > [ ... ] > > > Any hints how to use pg_ctl to start/stop postgresql on a unix domain > > > socket? > > 1) In po

Re: [GENERAL] easy task: concurrent select-updates

Kevin McConnell wrote: CREATE OR REPLACE FUNCTION public.getmsg() RETURNS integer LANGUAGE plpgsql AS $function$ declare rec record; begin for rec in select id from msg where busy = false order by id loop update msg set busy = true where id = rec.id and busy = false;

Re: [GENERAL] PL/Perl 64-bit and sending emails

On Sep 3, 2009, at 11:30 AM, Mark Lubratt wrote: Hello! I've been running the 64-bit version of 8.3.4 on OpenSolaris 2009.06 for over a year. Now, I need to put a perl function call into it to allow emails to be sent by the database backend. I tried installing plperl, but it looks like

Re: [GENERAL] Create language PLPERL error

Shakil Shaikh wrote: > Hi all, > > Running Postgres 8.4 on Ubuntu 9.04, installed via the clickonce > installer. Remove that, and install them from Martin Pitt's repository: https://launchpad.net/~pitti/+archive/postgresql The one-click installer does not integrate well with the platform. Avoid

Re: [GENERAL] easy task: concurrent select-updates

> CREATE OR REPLACE FUNCTION public.getmsg() RETURNS integer LANGUAGE plpgsql > AS $function$ > declare >       rec record; > begin >       for rec in select id from msg where busy = false order by id loop >               update msg set busy = true where id = rec.id and busy = false; >            

Re: [GENERAL] Create language PLPERL error

From: "Tom Lane" You've apparently got a version of libperl.so that is not compatible with the one that your Postgres was built against. There are lots of compile-time options for Perl that affect this, so it's not exactly a surprising situation. The easiest fix is to be sure you get your pos

Re: [GENERAL] Create language PLPERL error

"Shakil Shaikh" writes: > Running Postgres 8.4 on Ubuntu 9.04, installed via the clickonce installer. > I'm getting a curious error when trying to create/add support for plperl to > any database: > ERROR: could not load library > "/opt/PostgreSQL/8.4/lib/postgresql/plperl.so": > /opt/Postgre

[GENERAL] PL/Perl 64-bit and sending emails

Hello! I've been running the 64-bit version of 8.3.4 on OpenSolaris 2009.06 for over a year. Now, I need to put a perl function call into it to allow emails to be sent by the database backend. I tried installing plperl, but it looks like only a 32-bit version is available. Does the 64-

[GENERAL] Create language PLPERL error

Hi all, Running Postgres 8.4 on Ubuntu 9.04, installed via the clickonce installer. I'm getting a curious error when trying to create/add support for plperl to any database: ERROR: could not load library "/opt/PostgreSQL/8.4/lib/postgresql/plperl.so": /opt/PostgreSQL/8.4/lib/postgresql/plp

Re: [GENERAL] column level, uid based authorization to update columns

On Thu, Sep 03, 2009 at 10:17:15AM -0700, Gauthier, Dave wrote: > In linux, given the linux based uid of the user, how might someone > implement column level update restrictions on a uid basis? For > example... You can use ident authentication in pg_hba.conf and per-column GRANT/REVOKE in 8.4. h

Re: [GENERAL] column level, uid based authorization to update columns

On Sep 3, 2009, at 12:17 PM, Gauthier, Dave wrote: In linux, given the linux based uid of the user, how might someone implement column level update restrictions on a uid basis? For example... create table foo (strcol varchar(256), intcol integer); Now, I want linux processes runing under

[GENERAL] column level, uid based authorization to update columns

In linux, given the linux based uid of the user, how might someone implement column level update restrictions on a uid basis? For example... create table foo (strcol varchar(256), intcol integer); Now, I want linux processes runing under uid "joesmith" to be able to update strcol but not intco

[GENERAL] add a value to an ENUM type

I have an enum type CREATE TYPE shapeName AS ENUM('rectangle','circle'); now I need another value: 'square' the pg_type.oid of 'shapename is 16458 It works, but Is it safe to use this? insert into pg_enum (enumtypid,enumlabel) VALUES('16458','square'); thank you Edoardo -- Sent via pgsql-gene

Re: [GENERAL] auto-increment in a view

> Just a wild notion, but would the generate_series function be any use to > you? Good idea, but i can't get it to work. create or replace view testview3 as ( select generate_series(1,(select count(*) from test group by "value")), value from test group by value); select * from testview3 limit 5;

Re: [GENERAL] auto-increment in a view

Willy-Bas Loos wrote: Hi, I'm trying to figure out to generate a auto-increment column in a view. There is no physical column to base it on, the view contains a group by clause, which renders that impossible. In a normal query i can create a sequence for that purpouse and drop it afterwards, but

Re: [GENERAL] handle audiofiles in postgres

urs.edi...@gmail.com (edisan) writes: > Can anyone used or tell me how to handle audio files in postgres > > Audio files may be in  wav / vox / dss format and each have average 30 min > running time. In principle, you could store these as "BLOB" data; I'd quite prefer storing this as "bytea" data.

Re: [GENERAL] [Q] optmizing postgres for 'single client' / many small queries

On Thu, Sep 03, 2009 at 07:24:50AM -0700, Richard Broersma wrote: > On Wed, Sep 2, 2009 at 4:35 PM, David Fetter wrote: > > > Hibernate has the very nice feature of being able to get out of > > your way.  Properly used, it can keep completely out of the > > business of making (wrong) guesses based

Re: [GENERAL] auto-increment in a view

On 03/09/2009 17:22, Willy-Bas Loos wrote: > Hi, > > I'm trying to figure out to generate a auto-increment column in a view. > There is no physical column to base it on, the view contains a group > by clause, which renders that impossible. > In a normal query i can create a sequence for that purpo

[GENERAL] auto-increment in a view

Hi, I'm trying to figure out to generate a auto-increment column in a view. There is no physical column to base it on, the view contains a group by clause, which renders that impossible. In a normal query i can create a sequence for that purpouse and drop it afterwards, but apart form it being ugl

Re: [GENERAL] PosgreSQL Service does not Start In Vista

Inigo Barandiaran, 03.09.2009 14:47: Finally, I installed posgre database out of "program Files" as Thomas suggested and I gave full privileges to posgre user to data folder and everything works correctly :) Do you know how can I install posgre in "program Files" in Vista?. I might some users of

Re: [GENERAL] Problem with leaking connections

Mark Lange wrote: Hi, we have an webapplication running on tomcat6 with hibernate. We are using the apache DBCP connection pool (tomcat built in) and an postgres 8.19 database server on an different host. Every few days the pool is running out of connections. The pool is configured t

Re: [GENERAL] Problem with leaking connections

On Thu, 2009-09-03 at 17:55 +0200, Mark Lange wrote: > Hi, > The pool is configured to aggressively close connections when it is > exhausted, but the application gets very slow when this happens, > mostly we have to restart > > the tomcat server. > > #ps –aux | grep postgres on the database host

[GENERAL] Problem with leaking connections

Hi, we have an webapplication running on tomcat6 with hibernate. We are using the apache DBCP connection pool (tomcat built in) and an postgres 8.19 database server on an different host. Every few days the pool is running out of connections. The pool is configured to aggressively close co

Re: [GENERAL] easy task: concurrent select-updates

Nickolay wrote: one important addition: the message cannot be removed from queue table until it is transmitted, so DELETE is not an option :) Hi All, I have a trivial task. There is a table with messages queue, let's say "msg_queue". There are a few processes and each of them is taking one mes

Re: [GENERAL] Audit Trigger puzzler

David Kerr wrote: On Wed, Sep 02, 2009 at 11:44:20PM -0500, Adam Rich wrote: - In Oracle, the way we handle audit triggers is by using Package - Variables. We emulate some of that functionality in postgresql by - adding a custom variable to the configuration file: - - custom_variable_classes

Re: [GENERAL] PostgreSQL Live CD based on CentOS 5.3 and PG 8.4 released

On Thu, 2009-09-03 at 11:38 -0300, Emanuel Calvo Franco wrote: > Other thing that you can implement is a test or example database. Done. It will now create pagila database and load pagila data to it. :) This will also appear in next week's release. > For the lazy admins, you can add postgres to s

Re: [GENERAL] install postgis in linux server without desktop

Thank you very much, I successfully install postgis. Robert Dörfler wrote: > > Am Mittwoch, den 02.09.2009, 16:54 -0700 schrieb shane_china: >> I do follow your instruction. I successfully install postgis, but I can't >> find postgis.sql under any folder. >> My postgresql installed by apt-get.

Re: [GENERAL] Audit Trigger puzzler

On Wed, Sep 02, 2009 at 11:44:20PM -0500, Adam Rich wrote: - In Oracle, the way we handle audit triggers is by using Package - Variables. We emulate some of that functionality in postgresql by - adding a custom variable to the configuration file: - - custom_variable_classes = 'mysess' - - Then

Re: [GENERAL] Snow Leopard and plpython problem

hi jerry, Hi I am having a problem building Postgresql 8.4.0 in Snow Leopard. I cannot get plpython to build... I have installed MacPython 2.6.2 in /Library/Frameworks/ Python.framework do you really need python 2.6.2? snow leopard has python 2.6.1 on board and there are no problems compi

Re: [GENERAL] PostgreSQL Live CD based on CentOS 5.3 and PG 8.4 released

2009/9/2 Devrim GÜNDÜZ : > Hi, > > On Wed, 2009-09-02 at 13:47 -0300, Emanuel Calvo Franco wrote: >> > I released initial version of my PostgreSQL 8.4 live CD, which i >> > based on CentOS 5.3. It includes the PostgreSQL related packages >> > that I build on http://yum.pgsqlrpms.org , along with Po

Re: [GENERAL] Snow Leopard and plpython problem

2009/9/3 Jerry LeVan : > Hi I am having a problem building Postgresql 8.4.0 in Snow Leopard. > > I cannot get plpython to build... > > I have installed MacPython 2.6.2 in /Library/Frameworks/Python.framework > > The binary: > In Debian I had to install these libs to get work 8.4:libsdl-perl, libpe

[GENERAL] Snow Leopard and plpython problem

Hi I am having a problem building Postgresql 8.4.0 in Snow Leopard. I cannot get plpython to build... I have installed MacPython 2.6.2 in /Library/Frameworks/Python.framework The binary: mbp:postgresql-8.4.0 postgres$ which python /Library/Frameworks/Python.framework/Versions/Current/bin/pytho

Re: [GENERAL] [Q] optmizing postgres for 'single client' / many small queries

On Wed, Sep 2, 2009 at 4:35 PM, David Fetter wrote: > Hibernate has the very nice feature of being able to get out of your > way.  Properly used, it can keep completely out of the business of > making (wrong) guesses based on DDL, which is what ORMs often do. > DBIx::Class

Re: [GENERAL] array weirdity

On Sep 3, 2009, at 9:19 , Sim Zacks wrote: How can these both be true? Is there a way to see if a value is not in an array? select 1229 <> any('{1220,0,0,1228,1229,1231,0,0,0}'::int[]) as result result --- t Here you're comparing 1229 to each element in the array in turn, and retu

Re: [GENERAL] array weirdity

Sim Zacks wrote: > How can these both be true? Is there a way to see if a value is not in > an array? > > > select 1229 <> any('{1220,0,0,1228,1229,1231,0,0,0}'::int[]) as result > > result > --- > t Not "any()", "all()" - it *is* different from some of the numbers there. -- Richard Hux

[GENERAL] array weirdity

How can these both be true? Is there a way to see if a value is not in an array? select 1229 <> any('{1220,0,0,1228,1229,1231,0,0,0}'::int[]) as result result --- t select 1229 = any('{1220,0,0,1228,1229,1231,0,0,0}'::int[]) as result result --- t -- Sent via pgsql-general ma

[GENERAL] How do I use tsvector_update_trigger to index non-character columns?

Hello. I have several tables in a database that I want to use with "full text searching". Some of the fields in some of these tables are not character types, but for the purposes of searching, I'd like to include their "character representation" in the tsvector. Unfortunately, I cannot make t

Re: [GENERAL] install postgis in linux server without desktop

> Thank you very much, I successfully install postgis. no problem ;) -- Greetings, Robert -- 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] PosgreSQL Service does not Start In Vista

Hi. I've tried to install postgree in Vista in program files and the service does not start. I gave manually full privileges to the posgre user to data folder, and still the service does not start. Finally, I installed posgre database out of "program Files" as Thomas suggested and I gave full pr

Re: [GENERAL] Data folder in differnet filesystem

In response to S Arvind : > I have 2 doubts related to Filsesytem and Postgres data folder > > 1.Currently in CentOS,  our postgres data folder is in single filesystem. Is > there any possibility to have a single data folder of single postgres in more > then one file system. Don't understand ...

[GENERAL] Data folder in differnet filesystem

I have 2 doubts related to Filsesytem and Postgres data folder 1.Currently in CentOS, our postgres data folder is in single filesystem. Is there any possibility to have a single data folder of single postgres in more then one file system. 2.I am having three big DB, is it possible to have a data

Re: [GENERAL] maximum count of contiguous years

gorsa wrote: > [...] > is there a select statement containing 'AND award_year BETWEEN 1994 > AND 2002' that could generate the following? > scholar_idconsistent_yrs > 1 4 > 2 5 > 3 2 You could either do some wild fanc

Re: [GENERAL] To pass schemaname as a function parameter

On Thu, Sep 3, 2009 at 7:37 AM, A. Kretschmer wrote: > In response to Kalai R : >> Hi, >> >> I wrote a function in plpgsql, to dispaly the student list. >> >> In a Database all schemas contains studentlist table. so I wrote the >> function with schemaname as a parameter(text data type). My code is

Re: [GENERAL] To pass schemaname as a function parameter

In response to Kalai R : > Hi, > > I wrote a function in plpgsql, to dispaly the student list. > > In a Database all schemas contains studentlist table. so I wrote the function > with schemaname as a parameter(text data type). My code is like > > CREATE FUNCTION disp_fn(schemaname text) AS $$ >

[GENERAL] To pass schemaname as a function parameter

Hi, I wrote a function in plpgsql, to dispaly the student list. In a Database all schemas contains studentlist table. so I wrote the function with schemaname as a parameter(text data type). My code is like CREATE FUNCTION disp_fn(schemaname text) AS $$ BEGIN SELECT * FROM schemaname.studentl

Re: [GENERAL] Subselect problem

Ok, here's some more details. This is my query: SELECT ah.FEATURE_ID, FG.TILE_NUM FROM nndb.admin_hierarchy ah JOIN NNDB.LINK_ADMIN LA ON ah.FEATURE_ID = LA.ADMIN_ID JOIN psi.FEATURE_GEOMETRY FG ON LA.LINK_ID = FG.NNDB_FEATURE_ID WHERE fg.tile_num = 8613949 and ah.feature_id in (SELECT AH2.FEAT

Re: [GENERAL] install postgis in linux server without desktop

Am Mittwoch, den 02.09.2009, 16:54 -0700 schrieb shane_china: > I do follow your instruction. I successfully install postgis, but I can't > find postgis.sql under any folder. > My postgresql installed by apt-get. > > After apt-get postgis, What should I to do? Only execute sql in > postgis.sql? >

Re: [GENERAL] easy task: concurrent select-updates

one important addition: the message cannot be removed from queue table until it is transmitted, so DELETE is not an option :) Hi All, I have a trivial task. There is a table with messages queue, let's say "msg_queue". There are a few processes and each of them is taking one message from this t

[GENERAL] easy task: concurrent select-updates

Hi All, I have a trivial task. There is a table with messages queue, let's say "msg_queue". There are a few processes and each of them is taking one message from this table at a time to transmit into communication channel. I've done it my way, but I have postgresql's messages about deadlocks a

[GENERAL] Does PG cache results of an aggregate function, (and results of non-volatile functions)?

Hi, I do have a query which make use of the results of an aggregate function (for example bit_or) several times in the output column list of the SELECT clause, does PostgreSQL simply execute the aggregate function only once and provide the output to the other calls to the same aggregate function. H

[GENERAL] maximum count of contiguous years

is there a way to get the maximum count of contiguous years? for example: SELECT version(); PostgreSQL 8.3.1, compiled by Visual C++ 1400 CREATE TABLE sch_applform ( scholar_id integer NOT NULL, award_year numeric(4) NOT NULL, CONSTRAINT sch_applform_pkey PRIMARY KEY (scholar_id, award_year

Re: [GENERAL] Domain types versus anyelement

2009/9/3 Brendan Jurd : > # select greater(1::aud, 0); > ERROR:  function greater(aud, integer) does not exist > LINE 1: select greater(1::aud, 0); >               ^ > HINT:  No function matches the given name and argument types. You > might need to add explicit type casts. > Apologies for the spa

[GENERAL] Domain types versus anyelement

I'm getting some surprising behaviour from domain types in 8.3.7. I have a domain "aud" on numeric(20,2). When I try to use a value of this type with the built-in greater() function, I get this: # select greater(1::aud, 0); ERROR: function greater(aud, integer) does not exist LINE 1: select gre