[SQL] Calling void functions
I'm informed that the last statement of a function that returns void cannot be a SELECT. How else is one supposed to call another function which also returns void? E.g., CREATE FUNCTION foo (a int, b int) RETURNS void LANGUAGE plpgsql AS $$ do important things $$; CREATE FUNCTION foo (a int) RETURNS void LANGUAGE sql AS $$ SELECT foo($1, default-value); $$; -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Calling void functions
> I'm informed that the last statement of a function that returns void > cannot be > a SELECT. How else is one supposed to call another function which also > returns void? > PERFORM ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Calling void functions
am Mon, dem 02.04.2007, um 8:52:09 -0400 mailte Daniel Caune folgendes: > > I'm informed that the last statement of a function that returns void > > cannot be > > a SELECT. How else is one supposed to call another function which > also > > returns void? > > > > PERFORM PERFORM works only in plpgsql, Peter wrote a pl/sql-function... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Update problem.
Hello I have a table created as the following CREATE TABLE mod48_00_2007 ( id text, n_gen serial NOT NULL, formstore text, te_cognome text, te_paternita text, te_nome text, te_sesso text, te_dtnasc text, te_attnasc text, te_luonasc text, te_provstato text, te_indi text, te_prov text, te_richiesta text, atto_forma text, rev_test text, atto_dt text, atto_num text, dt_olog text, cod_notaio text, pa_cognome text, pa_nome text, pa_qual text, pa_indirizzo text, pa_civ text, pa_cap text, pa_pro text, pa_sede text, pa_estero text, pa_data text, f_olo text, f_pub text, f_rev text, f_seg text, f_spe text, atto_riferimento text, ratto_dt text, ratto_num text, rdt_olog text, r_cognome text, r_nome text, r_qual text, r_sede text, r_estero text, dt_oggi text, n_pub_aut text, dt_tr_rgt text, sche_singola text, sche_multipla text, n_fog text, tot_n_fog text, reg_anno text, username text, date_inserted text, time_inserted text, deleted text, date_deleted text, time_deleted text, f_tes text, CONSTRAINT mod48_00_2007_pkey PRIMARY KEY (n_gen) ) At a surtain point i need to replace a record with another For example i have inserted 4 records. (1, 2 , 3 , 4) I need to replace all the values from the record 4 to the record 2 but keeping the n_gen serial key. And then delete the record 4. So that the next record i insert will take the n_gen 4 How can i do this? Shavonne Wijesinghe www.studioform.it Le informazioni contenute nella presente comunicazione e i relativi allegati possono essere riservate e sono, comunque destinate esclusivamente alle persone o alla Società sopra indicati. La diffusione, distribuzione e/o copiature del documento trasmesso da parte di qualsiasi soggetto diverso dal destinatario è proibita, sia ai sensi dell'art. 616 c.p., che ai sensi del D. Lgs. n. 196/2003. Se avete ricevuto questo messaggio per errore, Vi preghiamo di distruggerlo e di informarci immediatamente per telefono allo 0039362595044 o inviando un messaggio all'indirizzo e-mail [EMAIL PROTECTED] The informations in this communication is confidential and may also be legally privileged. It is intended for the addressee only. Access to this e-mail by anyone else is unauthorized. It is not to be relied upon by any person other than the addressee, except with our prior written approval. If you received this message please send an e-mail to the sender.
[SQL] Update problem.
Hello I have a table created as the following CREATE TABLE mod48_00_2007 ( id text, n_gen serial NOT NULL, formstore text, te_cognome text, te_paternita text, te_nome text, te_sesso text, te_dtnasc text, te_attnasc text, te_luonasc text, te_provstato text, te_indi text, te_prov text, te_richiesta text, atto_forma text, rev_test text, atto_dt text, atto_num text, dt_olog text, cod_notaio text, pa_cognome text, pa_nome text, pa_qual text, pa_indirizzo text, pa_civ text, pa_cap text, pa_pro text, pa_sede text, pa_estero text, pa_data text, f_olo text, f_pub text, f_rev text, f_seg text, f_spe text, atto_riferimento text, ratto_dt text, ratto_num text, rdt_olog text, r_cognome text, r_nome text, r_qual text, r_sede text, r_estero text, dt_oggi text, n_pub_aut text, dt_tr_rgt text, sche_singola text, sche_multipla text, n_fog text, tot_n_fog text, reg_anno text, username text, date_inserted text, time_inserted text, deleted text, date_deleted text, time_deleted text, f_tes text, CONSTRAINT mod48_00_2007_pkey PRIMARY KEY (n_gen) ) At a surtain point i need to replace a record with another For example i have inserted 4 records. (1, 2 , 3 , 4) I need to replace all the values from the record 4 to the record 2 but keeping the n_gen serial key. And then delete the record 4. So that the next record i insert will take the n_gen 4 How can i do this? Shavonne Wijesinghe
Re: [SQL] Update problem.
On Mon, Apr 02, 2007 at 04:52:46PM +0200, Shavonne Marietta Wijesinghe wrote: > At a surtain point i need to replace a record with another > > For example i have inserted 4 records. (1, 2 , 3 , 4) I need to > replace all the values from the record 4 to the record 2 but > keeping the n_gen serial key. And then delete the record 4. So that > the next record i insert will take the n_gen 4 If I understand you correclty, you can do an UPDATE to record 4 to record 2, then do a setval() on the sequence. The setval() is tricky, though, because you have the problem that other connections could be using it. I'd lock the table in question while you did all this. A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Calling void functions
Peter Eisentraut <[EMAIL PROTECTED]> writes: > I'm informed that the last statement of a function that returns void cannot > be > a SELECT. How else is one supposed to call another function which also > returns void? Hmm, seems like the best answer is to rejigger the order of the tests in check_sql_fn_retval. I think that logic was designed before we had an idea of VOID-returning functions. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] SQL4. I ask you to help in development (or critic).
SQL4 is project of novation technology in DBMS. I ask you to help in development (or critic). SQL4 consist of DDL, DML and TML (Tree Manipulation Language). TML4 is new advanced query language, which processes trees and counts in database, put them into it, and get them from it (tree's model of data is over rational model). SQL 4.0.4 is project of novations: * DDL4 installs right of access to each record * TML considers records of tables, bound by foreign key, as elements of tree. It's un-important, next element of tree is parental table or branch table - it's necessary only foreign key between two tables Elements of tree can be a set and a list * TML uses mask for specification necessary sub-trees, inserts and deletes elements of sets, of lists, and also whole sub-trees, changes values of field in any nodes of tree * DBMS transform got XML into records of tables; transform records of tables, bound by foreign keys, into XML * Way to solve collision is specified, when one record is bound with several records of other tables * Communication with external world occurs by HTTP, that at least one of possible client of DBMS could communicate with it without middle-ware. Client gets picture from database by its URL and separately from XML-data Documentation is on http://sql40.chat.ru ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Maxusers MaxConnections
Hi list, I would like to know some performance issues about maxconnections or maxusers in postgresql.conf. My questions are: - Have someone an application with many connections (or users, I don't know the difference in postgresql) like 1,000 connections ? - How many memory each user spent in the server (just by the fact of been connected) ? Regards, -- Ezequias Rodrigues da Rocha ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] timestamp subtraction (was Re: [SQL] formatting intervals with to_char)
Added to TODO:
o Have timestamp subtraction not call justify_hours()?
http://archives.postgresql.org/pgsql-sql/2006-10/msg00059.php
---
Jim C. Nasby wrote:
> Yes, but if it was '2004-01-02 01:00:00'-'2004-01-01 00:00:00' it should
> return 25:00:00, not 1 day 1:00.
>
> I agree with Tom that this should be changed; I'm just arguing that we
> might well need a backwards-compatibility solution for a while. At the
> very least we'd need to make this change very clear to users.
>
> On Tue, Feb 20, 2007 at 08:07:11PM -0500, Bruce Momjian wrote:
> >
> > One problem with removing justify_hours() is that this is going to
> > return '24:00:00', rather than '1 day:
> >
> > test=> select '2004-01-02 00:00:00'::timestamptz - '2004-01-01
> > 00:00:00'::timestamptz;
> > ?column?
> > --
> > 24:00:00
> > (1 row)
> >
> > ---
> >
> > Jim Nasby wrote:
> > > On Oct 5, 2006, at 11:50 AM, Tom Lane wrote:
> > > > regression=# select ('2006-09-15 23:59:00'::timestamp - '2006-09-01
> > > > 09:30:41'::timestamp);
> > > > ?column?
> > > > --
> > > > 14 days 14:28:19
> > > > (1 row)
> > > >
> > > > should be reporting '350:28:19' instead.
> > > >
> > > > This is a hack that was done to minimize the changes in the regression
> > > > test expected outputs when we changed type interval from months/
> > > > seconds
> > > > to months/days/seconds. But I wonder whether it wasn't a dumb idea.
> > > > It is certainly inconsistent, as noted in the code comments.
> > > >
> > > > I'm tempted to propose that we remove the justify_hours call, and tell
> > > > anyone who really wants the old results to apply justify_hours() to
> > > > the
> > > > subtraction result for themselves. Not sure what the fallout would
> > > > be,
> > > > though.
> > >
> > > I suspect there's applications out there that are relying on that
> > > being nicely formated for display purposes.
> > >
> > > I agree it should be removed, but we might need a form of backwards
> > > compatibility for a version or two...
> > > --
> > > Jim Nasby[EMAIL PROTECTED]
> > > EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
> > >
> > >
> > >
> > > ---(end of broadcast)---
> > > TIP 3: Have you checked our extensive FAQ?
> > >
> > >http://www.postgresql.org/docs/faq
> >
> > --
> > Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us
> > EnterpriseDB http://www.enterprisedb.com
> >
> > + If your life is a hard drive, Christ can be your backup. +
> >
> > ---(end of broadcast)---
> > TIP 2: Don't 'kill -9' the postmaster
>
> --
> Jim Nasby[EMAIL PROTECTED]
> EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
>
> ---(end of broadcast)---
> TIP 1: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to [EMAIL PROTECTED] so that your
>message can get through to the mailing list cleanly
--
Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] Calling void functions
> PERFORM works only in plpgsql, Peter wrote a pl/sql-function... > Oups, sorry! I missed the point. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Very slow DELETE on 4000 rows of 55000 row table
I've got a DELETE FROM that seems to run forever, pegging the CPU at 100%. I can't figure out why it's slow. Any clues? stage=# EXPLAIN DELETE FROM EG_INVOICE WHERE PERIOD_ID = 1017506; Index Scan using ix22f7bc70c7de2059 on eg_invoice (cost=0.00..105.39 rows=3955 width=6) Index Cond: (period_id = 1017506) stage=# select count(*) FROM EG_INVOICE; 55376 stage=# select count(*) FROM EG_INVOICE where PERIOD_ID = 1017506;; 4603 stage=# \d EG_INVOICE; Table "public.eg_invoice" Column | Type | Modifiers ++--- invoice_id | integer| not null cso_id | integer| not null period_id | integer| not null invoice_number | character varying(192) | invoice_date | date | plan_name | character varying(128) | invoice_style | integer| not null account_id | integer| Indexes: "eg_invoice_pkey" PRIMARY KEY, btree (invoice_id) "invoice_number_idx" btree (invoice_number) "ix22f7bc70c7de2059" btree (period_id) Foreign-key constraints: "fk22f7bc70c7de2059" FOREIGN KEY (period_id) REFERENCES eg_billing_period(period_id) "invoice_to_account" FOREIGN KEY (account_id) REFERENCES eg_account(account_id) "invoice_to_cso" FOREIGN KEY (cso_id) REFERENCES eg_cso(cso_id) stage=# vacuum analyze verbose EG_INVOICE; ... INFO: "eg_invoice": scanned 584 of 584 pages, containing 55376 live rows and 0 dead rows; 3000 rows in sample, 55376 estimated total rows PostgreSQL 8.1.8 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.1 20070105 (Red Hat 4.1.1-51) -- Visit http://www.obviously.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
