Re: [GENERAL] How to count the number of items in an Array?
Andre Lopes wrote: > Hi, > > I have googled, but I can't find how to count the number of items in an Array > in Plpgsql. > > Someone can give me a clue on that? Sure, you can use array_upper() - array_lower(): Zeit: 0,205 ms test=*# select array_upper(array[1,2,3],1) - array_lower(array[1,2,3],1); ?column? -- 2 (1 Zeile) or: test=*# select array_upper(array[1,2,3],1) - array_lower(array[1,2,3],1) + 1; ?column? -- 3 (1 Zeile) Or, if your array starts with the first element, simple: test=*# select array_upper(array[1,2,3,NULL,5],1); array_upper - 5 (1 Zeile) Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- 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] How to count the number of items in an Array?
Hi,
Thanks for the reply.
My problem is that I can't have the array with all items. I need to read
from a table and store in an Array the values.
I have this:
[code]
CREATE OR REPLACE FUNCTION "public"."apr_update_newsletter_distritos"
("pEMAIL" varchar, "pID_WEBSITE_RECOLHA" varchar) RETURNS void AS
$body$
DECLARE
pEMAIL alias for $1; -- vai ser preciso
pID_WEBSITE_RECOLHA alias for $2; -- vai ser preciso
vDISTRITOS_NA_TABELA varchar[];
vDISTRITOS_NA_TABELA_CONST varchar[];
vd integer;
vas varchar;
BEGIN
-- ### Vou gravar num array os registos da chave em q se vai mexer
-- vou entao passar os registos para o array vDISTRITOS_NA_TABELA
FOR vDISTRITOS_NA_TABELA IN
SELECT array[id_distrito] FROM am_newsletter_distritos
WHERE email = pEMAIL and id_website_recolha = pID_WEBSITE_RECOLHA
LOOP
RAISE NOTICE 'value: %', vDISTRITOS_NA_TABELA;
END LOOP;
-- array 2 string
select array_to_string(vDISTRITOS_NA_TABELA, ',') into vas;
RAISE NOTICE 'string with items of array: %', vas;
-- Vou contar o número dos registos do array
-- select array_upper(vDISTRITOS_NA_TABELA, 1) into vd;
-- RAISE NOTICE 'num array: %', vd;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
[/code]
This select returns me 4 values:
[code]
SELECT array[id_distrito] FROM am_newsletter_distritos
WHERE email = pEMAIL and id_website_recolha = pID_WEBSITE_RECOLHA
[/code]
The values are '11, 12,16, 16'. I haven't found a way to store this as an
array to the variable vDISTRITOS_NA_TABELA. Someone can give me a clue on
how to do that?
Best Regards,
On Sun, Feb 21, 2010 at 8:38 AM, Andreas Kretschmer <
akretsch...@spamfence.net> wrote:
> Andre Lopes wrote:
>
> > Hi,
> >
> > I have googled, but I can't find how to count the number of items in an
> Array
> > in Plpgsql.
> >
> > Someone can give me a clue on that?
>
> Sure, you can use array_upper() - array_lower():
>
> Zeit: 0,205 ms
> test=*# select array_upper(array[1,2,3],1) - array_lower(array[1,2,3],1);
> ?column?
> --
>2
> (1 Zeile)
>
>
> or:
>
> test=*# select array_upper(array[1,2,3],1) - array_lower(array[1,2,3],1) +
> 1;
> ?column?
> --
>3
> (1 Zeile)
>
>
> Or, if your array starts with the first element, simple:
>
> test=*# select array_upper(array[1,2,3,NULL,5],1);
> array_upper
> -
> 5
> (1 Zeile)
>
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect. (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly." (unknown)
> Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
>
> --
> 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] pgpool error, pid not found!
I don't think pgpool can be compiled on Windows without heavy modifications. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp > I tried compiling on windows. I didnt get success in that. > > I tried gcc 2 ways. > 1) gcc -o pool_status_query pool_status_query.c > 2)make pool_status_query > > none of them gave me proper answer. there are errors but I cant see all > errors or understand them. > Do you have any clue? > On Sun, Feb 21, 2010 at 7:14 AM, Tatsuo Ishii wrote: > > > > Hi, > > > > > > I made some changes in Pgpool .Now I am using Dev c++ to compile the > > code. > > > Is that the right tool to complie the pgpool code? or I should use Linux > > > compiler. > > > > Do you mean that Windows C compiler? I have no experience with > > compiling pgpool on Windows. Please let me know if you suceed. > > > > > can you advice me which linux compiler I can use? > > > > Plane gcc is fine. > > -- > > Tatsuo Ishii > > SRA OSS, Inc. Japan > > English: http://www.sraoss.co.jp/index_en.php > > Japanese: http://www.sraoss.co.jp > > > > > Thanks > > > > > > On Thu, Feb 18, 2010 at 7:26 PM, Tatsuo Ishii > > wrote: > > > > > > > The last version of pgpool(not pgpool-II) had been released almost 3 > > > > years ago. So I guess it has many bugs found during this 3 years. > > > > However as long as the bug does not bite you, it's ok. > > > > > > > > I just recommend to use pgpool-II in the real world use. > > > > -- > > > > Tatsuo Ishii > > > > SRA OSS, Inc. Japan > > > > English: http://www.sraoss.co.jp/index_en.php > > > > Japanese: http://www.sraoss.co.jp > > > > > > > > > one of the student at my school worked on this. I need to extend her > > > > thesis. > > > > > so thats why I am using this. > > > > > Is there any problem if I use this version? > > > > > Is this stable version? > > > > > Finally I am able to work both pgpool and postgres working togather. > > > > > > > > > > > > > > > From: Tatsuo Ishii [is...@sraoss.co.jp] > > > > > Sent: Thursday, February 18, 2010 1:56 AM > > > > > To: megha198...@gmail.com > > > > > Cc: pgsql-general@postgresql.org > > > > > Subject: Re: [GENERAL] pgpool error, pid not found! > > > > > > > > > > Wao. This is pgpool, right? It's not maintained anymore(it was almost > > > > > 3 years ago). Please use pgpool-II. The latest version is pgpool-II > > > > > 2.3.2.1. > > > > > -- > > > > > Tatsuo Ishii > > > > > SRA OSS, Inc. Japan > > > > > English: http://www.sraoss.co.jp/index_en.php > > > > > Japanese: http://www.sraoss.co.jp > > > > > > > > > > > Sorry for late reply. I work full time. so I work on postgres in > > night. > > > > > > here is the command that I use. > > > > > > > > > > > > I started pgpool using this command. > > > > > > pgpool/bin/pgpool -f /home/megha/pgpool/etc/pgpool.conf -a > > > > > > /home/megha/pgpool/etc/pool_hba.conf > > > > > > > > > > > > pgpool.conf is attached. > > > > > > > > > > > > I have been trying to configure postgres and pgpool for past 2 > > months > > > > and I > > > > > > am getting too many problems with this. > > > > > > I hope you help me out to solve my errors. > > > > > > > > > > > > Waiting for your reply. > > > > > > Thanks > > > > > > > > > > > > On Tue, Feb 16, 2010 at 10:20 PM, Tatsuo Ishii < > > is...@postgresql.org> > > > > wrote: > > > > > > > > > > > > > How did you start pgpool exactly(command line)? > > > > > > > > > > > > > > Also, the number "" dpends on pgpool.conf. > > > > > > > Can you show me pgpool.conf? > > > > > > > -- > > > > > > > Tatsuo Ishii > > > > > > > SRA OSS, Inc. Japan > > > > > > > English: http://www.sraoss.co.jp/index_en.php > > > > > > > Japanese: http://www.sraoss.co.jp > > > > > > > > > > > > > > > >From the README file , I found that " psql -p -c 'show > > > > pool_status' > > > > > > > > template1" command gives the internal status of pgpool. > > > > > > > > so when i start pgpool, I dont get any inernal status. > > > > > > > > I get this error message > > > > > > > > > > > > > > > > psql: could not connect to server: No such file or directory > > > > > > > > Is the server running locally and accepting > > > > > > > > connections on Unix domain socket "/tmp/.s.PGSQL."? > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > On Tue, Feb 16, 2010 at 10:09 PM, Tatsuo Ishii < > > > > is...@postgresql.org> > > > > > > > wrote: > > > > > > > > > > > > > > > > > pgpool.pid file contains pgpool's parent process id. That > > > > message > > > > > > > > > indicates that the file exists but the pgpool process id does > > not > > > > > > > > > exist. Probably pgpool was stopped in unclean way. You may > > ignore > > > > the > > > > > > > > > message as long as pgpool starts ok. > > > > > > > > > -- > > > > > > > > > Tatsuo Ishii > > > > > > > > > SRA OSS, Inc. Japan > > > > > > > > > English: http://www.sraoss.co.jp/index_en.php > > > > > > > > > Japanese: http://
Re: [GENERAL] How to count the number of items in an Array?
Andre Lopes wrote:
> Hi,
>
> Thanks for the reply.
>
> My problem is that I can't have the array with all items. I need to read from
> a
> table and store in an Array the values.
Not sure if i understand you, but how about:
test=*# select * from foo;
a
---
1
3
5
7
(4 Zeilen)
Zeit: 0,203 ms
test=*# select array_agg(a) from foo;
array_agg
---
{1,3,5,7}
(1 Zeile)
The function returns a column as an array.
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
--
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] How to count the number of items in an Array?
One more time, thanks for the reply. I'am using postgre 8.3. I think I don't
have the array_agg(), there is another way of doing it?
Best Regards,
On Sun, Feb 21, 2010 at 2:46 PM, Andreas Kretschmer <
akretsch...@spamfence.net> wrote:
> Andre Lopes wrote:
>
> > Hi,
> >
> > Thanks for the reply.
> >
> > My problem is that I can't have the array with all items. I need to read
> from a
> > table and store in an Array the values.
>
> Not sure if i understand you, but how about:
>
> test=*# select * from foo;
> a
> ---
> 1
> 3
> 5
> 7
> (4 Zeilen)
>
> Zeit: 0,203 ms
> test=*# select array_agg(a) from foo;
> array_agg
> ---
> {1,3,5,7}
> (1 Zeile)
>
>
> The function returns a column as an array.
>
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect. (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly." (unknown)
> Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
>
> --
> 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] How to count the number of items in an Array?
Andre Lopes wrote:
> One more time, thanks for the reply. I'am using postgre 8.3. I think I don't
> have the array_agg(), there is another way of doing it?
Sure.
The doc contains an example for a user-defined aggregate-function:
http://www.postgresql.org/docs/8.4/interactive/xaggr.html
It works with versions prior 8.4.
test=*# CREATE AGGREGATE array_accum (anyelement)
test-# (
test(# sfunc = array_append,
test(# stype = anyarray,
test(# initcond = '{}'
test(# );
CREATE AGGREGATE
Zeit: 44,645 ms
test=*# select array_accum(a) from foo;
array_accum
-
{1,3,5,7}
(1 Zeile)
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
--
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] How to count the number of items in an Array?
Hi,
Thanks for the reply.
I have done it in this way:
[code]
FOR vDISTRITOS_NA_TABELA IN
SELECT array[id_distrito] FROM am_newsletter_distritos
WHERE email = pEMAIL and id_website_recolha = pID_WEBSITE_RECOLHA
LOOP
vDISTRITOS_NA_TABELA_CONST := vDISTRITOS_NA_TABELA_CONST ||
vDISTRITOS_NA_TABELA;
END LOOP;
[/code]
Best Regards,
On Sun, Feb 21, 2010 at 3:25 PM, Andreas Kretschmer <
akretsch...@spamfence.net> wrote:
> Andre Lopes wrote:
>
> > One more time, thanks for the reply. I'am using postgre 8.3. I think I
> don't
> > have the array_agg(), there is another way of doing it?
>
> Sure.
>
> The doc contains an example for a user-defined aggregate-function:
> http://www.postgresql.org/docs/8.4/interactive/xaggr.html
>
> It works with versions prior 8.4.
>
>
> test=*# CREATE AGGREGATE array_accum (anyelement)
> test-# (
> test(# sfunc = array_append,
> test(# stype = anyarray,
> test(# initcond = '{}'
> test(# );
> CREATE AGGREGATE
> Zeit: 44,645 ms
> test=*# select array_accum(a) from foo;
> array_accum
> -
> {1,3,5,7}
> (1 Zeile)
>
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect. (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly." (unknown)
> Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
[GENERAL] Timing Race
Version 8.4 on Ubuntu Linux (is this still supported?) I'm getting an error upon inserting a copy version of a database dump. It's very simple, 4 tables, TableA 2 records (basically a lookup table) TableB 15 records TableC 46 records ( many side of 'one to many') TableD 55 records.( one side of 'one to many') Each table has a sequence, one has an index. TableC has a Foreign key to TableD (child/parent respectively) TableD has a Foreign Key to TableA (child/parent respectively) The problem I get is that inserting the copy into a blank database gives me errors when it gets to TableD because 2nd record in TableA seems not to be available to reference for the foreign key in TableD. It doesn't even matter if I rearrange the database dump contents to insert all the parents first, it always fails there. However, if I split the file up, and do one table at a time, it works just fine. Is this a known issue, feature, or I am doing something wrong? I thought that doing a database recovery using a copy version of a dump would be a no brainer, it would happen all in one transaction? Dennis Gearon Signature Warning EARTH has a Right To Life, otherwise we all die. Read 'Hot, Flat, and Crowded' Laugh at http://www.yert.com/film.php --- On Sat, 2/20/10, pgsql-general-ow...@postgresql.org wrote: > From: pgsql-general-ow...@postgresql.org > Subject: [pgsql-general] Daily digest v1.9718 (19 messages) > To: pgsql-general@postgresql.org > Date: Saturday, February 20, 2010, 1:42 PM > Message Digest > Volume 1 : Issue 9718 : "text" Format > > Messages in this Issue: > Procedures > Re: Procedures > Re: Procedures > Re: Procedures > Re: Procedures > Re: Procedures > Re: Procedures > Re: Procedures > Re: Procedures > Re: Procedures > Re: DDL trigger kind functionality in PostGreSQL > Re: Procedures > Transactions, How to? > Re: Transactions, How to? > Re: Transactions, How to? > Re: PostgreSQL fails to start > Re: PostgreSQL fails to start > Re: PostgreSQL fails to start > Re: PostgreSQL fails to start > > -- > > Date: Sat, 20 Feb 2010 14:02:05 +0530 > From: Nilesh Govindarajan > To: pgsql-general@postgresql.org > Subject: Procedures > Message-ID: <4b7f9e05.5030...@itech7.com> > > How do I create a procedure using plpgsql cursors to print > the output of > the query in the cursor (using for loop) ? > > In all docs I found, it seems to be a must to return data > to the call > which is not what I want. > > -- > Nilesh Govindarajan > Site & Server Adminstrator > www.itech7.com > > -- > > Date: Sat, 20 Feb 2010 01:02:37 -0800 > From: John R Pierce > To: Nilesh Govindarajan > Cc: pgsql-general@postgresql.org > Subject: Re: Procedures > Message-ID: <4b7fa52d.9010...@hogranch.com> > > Nilesh Govindarajan wrote: > > How do I create a procedure using plpgsql cursors to > print the output > > of the query in the cursor (using for loop) ? > > > > In all docs I found, it seems to be a must to return > data to the call > > which is not what I want. > > > > what is it going to print it on? the > postgres server processes have no > console or stdout device. > > -- > > Date: Sat, 20 Feb 2010 18:38:14 +0530 > From: Nilesh Govindarajan > To: pgsql-general@postgresql.org > Subject: Re: Procedures > Message-ID: <4b7fdebe.1040...@itech7.com> > > On 02/20/2010 02:32 PM, John R Pierce wrote: > > Nilesh Govindarajan wrote: > >> How do I create a procedure using plpgsql cursors > to print the output > >> of the query in the cursor (using for loop) ? > >> > >> In all docs I found, it seems to be a must to > return data to the call > >> which is not what I want. > >> > > > > what is it going to print it on? the postgres server > processes have no > > console or stdout device. > > Okay, so how do I print it to stdout ? > > -- > Nilesh Govindarajan > Site & Server Adminstrator > www.itech7.com > > -- > > Date: Sat, 20 Feb 2010 14:21:20 +0100 > From: Thomas Kellerer > To: pgsql-general@postgresql.org > Subject: Re: Procedures > Message-ID: > > Nilesh Govindarajan wrote on 20.02.2010 14:08: > > On 02/20/2010 02:32 PM, John R Pierce wrote: > >> Nilesh Govindarajan wrote: > >>> How do I create a procedure using plpgsql > cursors to print the output > >>> of the query in the cursor (using for loop) ? > >>> > >>> In all docs I found, it seems to be a must to > return data to the call > >>> which is not what I want. > >>> > >> > >> what is it going to print it on? the postgres > server processes have no > >> console or stdout device. > > > > Okay, so how do I print it to stdout ? > > > Even if you could, that would be stdout of the *server*, > not the one of the client calling the procedure! > > Regards > Thomas > > > > -- > > Date: Sat, 20 Feb 2010 18:53:51 +0530 > From:
Re: [GENERAL] Timing Race
On Sunday 21 February 2010 10:38:18 am Dennis Gearon wrote: > Version 8.4 on Ubuntu Linux (is this still supported?) It is the most current stable version, so yes it is supported. > > I'm getting an error upon inserting a copy version of a database dump. It's > very simple, 4 tables, > > TableA 2 records (basically a lookup table) > TableB 15 records > TableC 46 records ( many side of 'one to many') > TableD 55 records.( one side of 'one to many') > > Each table has a sequence, one has an index. > > TableC has a Foreign key to TableD (child/parent respectively) > TableD has a Foreign Key to TableA (child/parent respectively) > > The problem I get is that inserting the copy into a blank database gives me > errors when it gets to TableD because 2nd record in TableA seems not to be > available to reference for the foreign key in TableD. It doesn't even > matter if I rearrange the database dump contents to insert all the parents > first, it always fails there. A couple of questions. 1) Are you dumping/restoring from the same version to same version? 2) What do your dump command and restore commands look like? > > However, if I split the file up, and do one table at a time, it works just > fine. Is this a known issue, feature, or I am doing something wrong? I > thought that doing a database recovery using a copy version of a dump would > be a no brainer, it would happen all in one transaction? I am not sure what you mean by a copy version? > > > Dennis Gearon > -- Adrian Klaver adrian.kla...@gmail.com -- 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] Asynchronous queries - processing listen (notify) in a procedural language
On Sat, Feb 20, 2010 at 9:38 PM, Petr Chmelar wrote: > Hi there, > > I'd like to make a real-time enabled database and I need to process data > asynchronously. Usually, there are many quick inserts sometimes causing > vast updates. I can't use triggers and rules because the transactions > are really long when there is the update (upgrade) needed. > > Is there a way how to listen and trigger the notify messages in the > database (+-)immediately and/or to execute additional (trigger) queries > in other transactions? > > In Oracle there is ON COMMIT trigger and an anonymous transaction that > allows commit inside triggers. However this is not possible in Postgres. > Moreover I can't know the check interval of the listening procedures in > an external application (can be anything from millis to days). I was so > desperate that I was thinking about own logging function. The only way that I know of to send notify 'in-transaction' is via dblink...you just send 'notify x' as the query which commits and fires the action. It doesn't make sense to do this if your outer transaction is very short in duration. 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] Asynchronous queries - processing listen (notify) in a procedural language
Merlin Moncure writes: > On Sat, Feb 20, 2010 at 9:38 PM, Petr Chmelar wrote: >> Is there a way how to listen and trigger the notify messages in the >> database (+-)immediately and/or to execute additional (trigger) queries >> in other transactions? > The only way that I know of to send notify 'in-transaction' is via > dblink...you just send 'notify x' as the query which commits and fires > the action. It doesn't make sense to do this if your outer > transaction is very short in duration. It's not clear that it makes sense to do that in a long transaction, either. What are you notifying other sessions *about*? Not your own changes --- they won't be able to see those till you commit. There's a reason why NOTIFY is delayed till commit ... 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] Question on RETURNS TABLE example in PostgreSQL documentation
The following code snippet are picked from PostgreSQL documentation : http://www.postgresql.org/docs/current/static/plpgsql-declarations.html CREATE FUNCTION extended_sales(p_itemno int) RETURNS TABLE(quantity int, total numeric) AS $$ BEGIN RETURN QUERY SELECT quantity, quantity * price FROM sales WHERE itemno = p_itemno; END; $$ LANGUAGE plpgsql; I would like modify the following function behavior slightly : (1) Only return TABLE(quantity int, total numeric), if there is at least one row meet condition WHERE itemno = p_itemno (2) If not, create the row, and return TABLE(quantity int, total numeric) The only way I can think of is : The only way I can think of to achieve (1) is : LOOP SELECT quantity, quantity * price FROM sales WHERE itemno = p_itemno; -- Fall into creation code block. EXIT WHEN NOT FOUND; RETURN QUERY SELECT quantity, quantity * price FROM sales WHERE itemno = p_itemno; END LOOP; But that will be two duplicated SELECT statement. Inefficient, right? Thanks and Regards Yan Cheng CHEOK -- 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] Asynchronous queries - processing listen (notify) in a procedural language
On Sun, Feb 21, 2010 at 9:22 PM, Tom Lane wrote: > Merlin Moncure writes: >> On Sat, Feb 20, 2010 at 9:38 PM, Petr Chmelar wrote: >>> Is there a way how to listen and trigger the notify messages in the >>> database (+-)immediately and/or to execute additional (trigger) queries >>> in other transactions? > >> The only way that I know of to send notify 'in-transaction' is via >> dblink...you just send 'notify x' as the query which commits and fires >> the action. It doesn't make sense to do this if your outer >> transaction is very short in duration. > > It's not clear that it makes sense to do that in a long transaction, > either. What are you notifying other sessions *about*? Not your own > changes --- they won't be able to see those till you commit. There's > a reason why NOTIFY is delayed till commit ... Heh...I almost mentioned this on the listen/notify thread. There is actually a case for mid transaction notify that I rely on quite a bit: when you need to request information from some client that is attached to your database. The database needs to signal the client and go get the information and return it, preferably _inside_ the notifying transaction so that you can have the information come back as a result to the function that set up the notification. The way I currently do this currently is via dblink establish a receiving record that the client stores it's response data with and block for it in the transaction that set up the dblink, Since it's read committed I can block and wait for the data or a timeout. With immediate notification and payloads, the dblink approach wouldn't be needed. I could establish the receiving record, and notify the client with the id of the record I want the response data in as a payload. It's mainly a parlor trick, but I like being able fetch data from a client in a single transaction based on an event. So, I have to basically state that while I can work around the current state affairs quite nicely, I think that the assertion that you have to necessarily wait for the txn to end before dispatching notify is only_mostly_ true. I'm pretty happy with the way things work now though...the new notification system is awesome. 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] Question on RETURNS TABLE example in PostgreSQL documentation
Hello 2010/2/22 Yan Cheng Cheok : > The following code snippet are picked from PostgreSQL documentation : > http://www.postgresql.org/docs/current/static/plpgsql-declarations.html > > CREATE FUNCTION extended_sales(p_itemno int) RETURNS TABLE(quantity int, > total numeric) AS $$ > BEGIN > RETURN QUERY SELECT quantity, quantity * price FROM sales WHERE itemno = > p_itemno; > END; > $$ LANGUAGE plpgsql; > > I would like modify the following function behavior slightly : > > (1) Only return TABLE(quantity int, total numeric), if there is at least one > row meet condition WHERE itemno = p_itemno > > (2) If not, create the row, and return TABLE(quantity int, total numeric) > > The only way I can think of is : > > The only way I can think of to achieve (1) is : > > LOOP > SELECT quantity, quantity * price FROM sales WHERE itemno = p_itemno; > > -- Fall into creation code block. > EXIT WHEN NOT FOUND; > > RETURN QUERY SELECT quantity, quantity * price FROM sales WHERE itemno > = p_itemno; > END LOOP; > RETURN QUERY isn't final statement in procedure. so you can RETURN QUERY first_query; IF NOT FOUND THEN RETURN QUERY try_some_else END IF; RETURN; -- final return, go out Regards Pavel Stehule > But that will be two duplicated SELECT statement. Inefficient, right? > > Thanks and Regards > Yan Cheng CHEOK > > > > > > -- > 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] change path of /tmp/.s.PGSQL.5432
Is it possible to change the path of the file: /tmp/.s.PGSQL.5432 I am using postgresql 8.1 and / has no space.
Re: [GENERAL] change path of /tmp/.s.PGSQL.5432
On Mon, 2010-02-22 at 12:30 +0600, AI Rumman wrote: > Is it possible to change the path of the file: > /tmp/.s.PGSQL.5432 see unix_socket_directory parameter. > I am using postgresql 8.1 and / has no space. If it is full even for a socket file, you may have more important issues than a single socket file. -- Devrim GÜNDÜZ, RHCE PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [GENERAL] change path of /tmp/.s.PGSQL.5432
On 02/22/2010 12:00 PM, AI Rumman wrote: Is it possible to change the path of the file: /tmp/.s.PGSQL.5432 I am using postgresql 8.1 and / has no space. Somebody already answered your question. But I suggest you inspect /var/log Logfiles can take large amounts of space if not rotated regularly. -- Nilesh Govindarajan Site & Server Adminstrator www.itech7.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
