Re: [SQL] Changing PL/pgSQL triggers
Greetings, On Thu, 21 Jun 2001, Josh Berkus wrote: >> What's the easiest way to modify or view a function written in >> PL/pgSQL? I've been using pg_dump to get the original function, then >> dropping and creating the function and trigger after making a change. >> Is there an easier way? > Well, if you're on Win32 or on Linux, you can install PGAccess (link > from postgresql.org). PGAccess has an OK function editor, although it > doesn't support cut-and-paste on Xwindows and I've found a few bugs with > large and complex functions. One if the 'features' missing in PGAccess (at least, on the version found in the 'ports-tree' of FreeBSD 4.1) is a simple 'save-without-quit' function. When you're editing a function; and you do save, the windows closes. So, you do some debugging, you find a bug; and you need to re-open the function. Usually (on my workstation), the windows pops up at the wrong place of the screen; and I usually need to do some 'resizing' to get the window in the shape I like. Cheerio! Kr. Bonne -- KB905-RIPE Belgacom IP networking (c=be,a=rtt,p=belgacomgroup,s=Bonne,g=Kristoff) Internet, IP and IP/VPN [EMAIL PROTECTED] Faxbox : +32 2 2435122 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Difference between insert a tuple in a table by function and by datasheet
Hi, this is my 2nd attempt to get some links for my problem. I don't know whether my question is so stupid or difficult or I'm in the wrong list. Please let me know. I have the following situation: ( I use MsAccess97 as frontend, ODBC 7.1.0003) I insert tuples in a table with a plpgsql-function, called in a form via VBA-code. It works fine, but I can't update these tuples in datasheet view. ERROR: an other user has changed in the meantime.but I'm the only. When I insert tuples via datasheet view, I can update these tuples, but only these. I can't see a difference between the tuples inserted by the function and datasheet, but it looks like, that the tuples are *marked*. Please, give me some information to solve my problem. Thanks Irina E-Mail: [EMAIL PROTECTED]
[SQL] distinguishing different database connections
Hi, can I access information on the current connection from within a trigger function? I need to identify different server connections somehow. Something like a "connection id" would be enough. The background: When a user starts a frontend application (NT, Access) he starts a new connection to our Linux database server. But for all users the "postgres user" is the same, since the ODBC connection is hard linked into the application. The problem is that we need the NT user name within our trigger (C) functions. The idea: Let's execute a function "logon" whenever an instance of the application is started. This function will get the NT user name as a parameter. It will recognize the current connection id and it would store the pair (connection id, NT user name) in a table. Then, whenever some trigger needs to know the user name (for logging actions), it could lookup the user name with the current connection id. Can we do this? Please help. Thank you very much, Markus ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Dump db with LO
Hello. I wonder if someone could give me a tip how should I dump db with LO. I use pg_dump and pg_dumpall and evrything is dumped but not LO. What should I do with that. I will be very greatful fot answer. P.S. Sorry for my english ;) Mateusz Mazur [EMAIL PROTECTED] POLAND ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] CAST Problem: Difference between insert a tuple in a table by function and by datasheet
Now,I found the problem, but I don't know how to solve. It depends on type float8. I changed all type float to float8 In my function I make the following calculation: . update KalkPreislisten_float8 SET kp_ep = rkontingent.k_ep::float8 -- this works correct but I have to calculate with a numeric type too (rwaehrung.w_euro is numeric(9,5)) update KalkPreislisten_float8 SET kp_ep = rkontingent.k_ep::float8 *rwaehrung.w_euro::float8 -- this doesn't work Please, can anyone tell me, how I must cast this numeric type to get float8 Many thanks in advance Irina E-Mail: [EMAIL PROTECTED]
Re: [SQL] distinguishing different database connections
You can use backend's PID from a trigger, it is unique. On Fri, 22 Jun 2001, Markus Wagner wrote: > Hi, > > can I access information on the current connection from within a trigger > function? > I need to identify different server connections somehow. Something like > a "connection id" would be enough. > > The background: > > When a user starts a frontend application (NT, Access) he starts a new > connection to our Linux database server. But for all users the "postgres > user" is the same, since the ODBC connection is hard linked into the > application. The problem is that we need the NT user name within our > trigger (C) functions. > > The idea: > Let's execute a function "logon" whenever an instance of the application > is started. This function will get the NT user name as a parameter. It > will recognize the current connection id and it would store the pair > (connection id, NT user name) in a table. Then, whenever some trigger > needs to know the user name (for logging actions), it could lookup the > user name with the current connection id. > > Can we do this? Please help. > > Thank you very much, > > Markus > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > > -- -- Alex Pilosov| http://www.acedsl.com/home.html CTO - Acecape, Inc. | AceDSL:The best ADSL in Bell Atlantic area 325 W 38 St. Suite 1005 | (Stealth Marketing Works! :) New York, NY 10018 | ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] select tbl1.attr1, number of tuples where tbl2.attr1=tbl1.attr1
Hey,
I have three tables.
table persons
{
login,
person_id UNIQUE
}
table views
{
person_id,
timestamp
}
table partners
{
person_id UNIQUE,
domain
}
for each tuple in partners i want to know the number of tuples in views with the same
person_id.
I tried sth like
select partners.person_id, count(views.person_id) from partners, views where
views.person_id = partners.person_id
but it said, partners.person_id must be grouped.
Then i tried
select partners.person_id, count(views.person_id) from partners, views where
views.person_id = partners.person_id group by views.person_id
but this gave very strange results which are definitely not what i wanted.
How can I do that?
TIA,
Markus Bertheau
Cenes Data GmbH
Join 18 million Eudora users by signing up for a free Eudora Web-Mail account at
http://www.eudoramail.com
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] constraints,
Hello folks, wondering how to display any constraints that a table may have, as well i know alter table add constraint works, but what's the syntax to remove one ? i assume /alter table remove constraint, but i can't find any examples. jeff ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] set datestyle to European PROBLEM
Hello Alessandro, The manual I have says the following about Date/Time Styles: - 4 styles (ISO-8601, SQL, Postgres, German) The one which resembles your layout is German. I looked it up in Bruce Momjians book. Best regards, Roelof Alessandro Rossi schreef: > > I have the defaul installation of postgres 7.0.3 and on another machine > 7.1.2 on redhat 7.1 > > I cannont get the date in correct form: > > dbme=# select data_ar from equipment limit 5; > data_ar > > 2001-11-05 > 2001-05-17 > 2001-05-28 > 2001-05-28 > 2001-05-22 > (5 rows) > > then: > > dbme=# set datestyle to European; > SET VARIABLE > > dbme=# select data_ar from equipment limit 5; > data_ar > > 2001-11-05 > 2001-05-17 > 2001-05-28 > 2001-05-28 > 2001-05-22 > (5 rows) > > Is this a bug ? > > I think i should get dd-mm-yyy date format and not -mm-dd > > Is postgres using ISO date format as default ? > > Thanks > > Alex > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster -- Roelof Sondaar WM-data Zwolle B.V. Russenweg 5 P O Box 391 8000 AJ ZWOLLE The Netherlands E-mail: [EMAIL PROTECTED] Telephone:+31(0)384 977 366 Fax:+31(0)384 977 600 WWW:www.wmdata.nl *** Disclaimer This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of WM-data Zwolle B.V. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing or copying of this email is strictly prohibited. If you have received this email in error please notify WM-data Zwolle B.V. Helpdesk by telephone on +31(0)384 977 319 *** ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Help with a double left join
I am trying to do a left join FROM [a table with two columns that have foreign IDs] LEFT JOIN [two other tables, each has a unique ID]. I have the left join working where I join only two tables (not three): SELECT track.ID, track.employee, track.client, track.task, track.description, track.hours_used, track.f_date, project.project_name FROM track LEFT JOIN project ON track.project=project.project_id WHERE track.client LIKE '%MMColParam%' the two tables are track and project. Track is the left of the left join. It holds the foreign keys. project (and later clients) are the columns with the keys. I need to also left join clients ON track.client=client.ID. Would someone tell me how the SQL statement should be that allows me to do the two left joins? Would it be: SELECT track.ID, track.employee, track.task, track.description, track.hours_used, track.f_date, project.project_name, clients.name FROM track LEFT JOIN project ON track.project=project.project_id AND LEFT JOIN client ON track.client=clients.ID WHERE track.client LIKE '%MMColParam%' '%MMColParam%' is just a variable used to hold a querystring variable in case anyone was wondering. Thanks, Ari (database is MS access. language is ASP. Whole thing will eventually be redone in PostgreSQL and PHP, just as soon as I learn them:) ~ Ari Nepon MRB Communications 4520 Wilde Street, Ste. 2 Philadelphia, PA 19127 p: 215.508.4920 f: 215.508.4590 http://www.mrbcomm.com --- Sign up for our email list and receive free information about topics of interest to nonprofit communications, marketing, and community building professionals. Free resources, articles, tips. Go to http://www.mrbcomm.com and use the Mailing List form. --- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
RE: [SQL] Difference between insert a tuple in a table by function and by datasheet
REMOVE -Message d'origine-De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]De la part de DI HasenöhrlEnvoyé : vendredi 22 juin 2001 09:23À : [EMAIL PROTECTED]; [EMAIL PROTECTED]Objet : [SQL] Difference between insert a tuple in a table by function and by datasheet Hi, this is my 2nd attempt to get some links for my problem. I don't know whether my question is so stupid or difficult or I'm in the wrong list. Please let me know. I have the following situation: ( I use MsAccess97 as frontend, ODBC 7.1.0003) I insert tuples in a table with a plpgsql-function, called in a form via VBA-code. It works fine, but I can't update these tuples in datasheet view. ERROR: an other user has changed in the meantime.but I'm the only. When I insert tuples via datasheet view, I can update these tuples, but only these. I can't see a difference between the tuples inserted by the function and datasheet, but it looks like, that the tuples are *marked*. Please, give me some information to solve my problem. Thanks Irina E-Mail: [EMAIL PROTECTED] Incoming mail is certified Virus Free.Checked by AVG anti-virus system (www.grisoft.com). Version: 6.0.262 / Virus Database: 132 - Release Date: 12/06/2001 Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (www.grisoft.com). Version: 6.0.262 / Virus Database: 132 - Release Date: 12/06/2001
[SQL] What is a "tuple"
Greetings, I've been on this list for just a couple of days now; and I've seen the word 'tuple' here a couple of times. Excuse my lack my 'database'-jargon, but what is a 'tuple'? Cheerio! Kr. Bonne. -- KB905-RIPE Belgacom IP networking (c=be,a=rtt,p=belgacomgroup,s=Bonne,g=Kristoff) Internet, IP and IP/VPN [EMAIL PROTECTED] Faxbox : +32 2 2435122 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
RE: [SQL] Help with a double left join
Thanks Alex. But it was too good to be true. Or, more likely, I did something else wrong. I am still getting this error: Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'track.project=project.project_id LEFT JOIN client ON track.client=clients.ID'. when I use this SQL: SELECT track.ID, track.employee, track.task, track.description, track.hours_used, track.f_date, project.project_name, clients.name FROM track LEFT JOIN project ON track.project=project.project_id LEFT JOIN client ON track.client=clients.ID WHERE track.client LIKE '%MMColParam%' Any thoughts? Thanks in advance. (see below for explanation of my DB structure if you would like). Ari -Original Message- From: Alex Pilosov [mailto:[EMAIL PROTECTED]] Sent: Friday, June 22, 2001 9:42 AM To: Ari Nepon Cc: Pgsql-Sql Subject: Re: [SQL] Help with a double left join On Wed, 20 Jun 2001, Ari Nepon wrote: > I am trying to do a left join FROM [a table with two columns that have > foreign IDs] LEFT JOIN [two other tables, each has a unique ID]. > > I have the left join working where I join only two tables (not three): > > SELECT track.ID, track.employee, track.client, track.task, > track.description, track.hours_used, track.f_date, project.project_name > FROM track LEFT JOIN project ON track.project=project.project_id > WHERE track.client LIKE '%MMColParam%' > > > the two tables are track and project. Track is the left of the left join. It > holds the foreign keys. project (and later clients) are the columns with the > keys. I need to also left join clients ON track.client=client.ID. Would > someone tell me how the SQL statement should be that allows me to do the two > left joins? Would it be: > > SELECT track.ID, track.employee, track.task, track.description, > track.hours_used, track.f_date, project.project_name, clients.name > FROM track LEFT JOIN project ON track.project=project.project_id AND LEFT remove the 'AND' FROM track LEFT JOIN project ON track.project=project.project_id LEFT > JOIN client ON track.client=clients.ID > WHERE track.client LIKE '%MMColParam%' > > > > '%MMColParam%' is just a variable used to hold a querystring variable in > case anyone was wondering. > > Thanks, > > Ari > > (database is MS access. language is ASP. Whole thing will eventually be > redone in PostgreSQL and PHP, just as soon as I learn them:) > ~ > Ari Nepon > MRB Communications > 4520 Wilde Street, Ste. 2 > Philadelphia, PA 19127 > p: 215.508.4920 > f: 215.508.4590 > http://www.mrbcomm.com > > --- > Sign up for our email list and receive free information about > topics of interest to nonprofit communications, marketing, and > community building professionals. Free resources, articles, tips. > Go to http://www.mrbcomm.com and use the Mailing List form. > --- > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > > ---(end of broadcast)--- TIP 3: 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
Re: [SQL] select tbl1.attr1, number of tuples where tbl2.attr1=tbl1.attr1
"Markus Bertheau" <[EMAIL PROTECTED]> writes: > Then i tried > select partners.person_id, count(views.person_id) from partners, views where >views.person_id = partners.person_id group by views.person_id > but this gave very strange results which are definitely not what i wanted. I think you mistyped, because that query will also fail: regression=# create table views (person_id int, timestamp timestamp); CREATE regression=# create table partners (person_id int UNIQUE, domain text); NOTICE: CREATE TABLE/UNIQUE will create implicit index 'partners_person_id_key' for table 'partners' CREATE regression=# select partners.person_id, count(views.person_id) regression-# from partners, views where views.person_id = partners.person_id regression-# group by views.person_id; ERROR: Attribute partners.person_id must be GROUPed or used in an aggregate function How about showing us what you *really* did? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Re: Re: binary data
"Hugh Mandeville" <[EMAIL PROTECTED]> writes:
> "Alex Pilosov" <[EMAIL PROTECTED]> wrote in message
>> The rules for escaping things you want to throw at it are tricky though.
>> (and same for unescaping things you get back from database).
> test=# INSERT INTO log (data) VALUES ('special chars \n \001 \002');
> INSERT 61651 1
> test=# INSERT INTO log (data) VALUES ('null \000 null');
> INSERT 61652 1
> test=# SELECT octet_length(data), data FROM log;
> octet_length | data
> --+--
>10 | plain text
>19 | special chars \012 \001 \002
> 5 | null
> (3 rows)
He did say the rules for escaping things are tricky ;-). You need to
double the backslashes, because interpretation of the string literal
takes off one level of backslashing before bytea ever sees it:
regression=# INSERT INTO log (data) VALUES ('null \\000 null');
INSERT 273181 1
regression=# SELECT octet_length(data), data FROM log;
octet_length | data
--+--
10 | plain text
19 | special chars \012 \001 \002
5 | null
11 | null \000 null
(4 rows)
regards, tom lane
---(end of broadcast)---
TIP 3: 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
Re: [SQL] Help with a double left join
"Ari Nepon" <[EMAIL PROTECTED]> writes: > Thanks Alex. But it was too good to be true. Or, more likely, I did > something else wrong. clients.ID => client.ID, likely. regression=# create table track(project int, client int); CREATE regression=# create table project(project_id int); CREATE regression=# create table client(ID int); CREATE regression=# select * from track LEFT JOIN project ON track.project=project.pro ject_id regression-# LEFT JOIN client ON track.client=clients.ID; ERROR: Relation 'clients' does not exist regression=# select * from track LEFT JOIN project ON track.project=project.pro ject_id regression-# LEFT JOIN client ON track.client=client.ID; project | client | project_id | id -+++ (0 rows) regards, tom lane ---(end of broadcast)--- TIP 3: 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
Re: [SQL] firehouse
On Wed, Jun 20, 2001 at 07:37:39PM +, sbelow wrote: > getting this error "Transaction cannot start while in firehouse mode." I > can't find in the books what this is trying to tell me. > new at dbs. That message is not coming from PostgreSQL, I've grepped the source tree: $ find . -name \*.[chyl] | xargs grep -i 'fireh' $ What's your software environment? The error must be coming from some other layer. Ross ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] cache lookup failed ???? What is it ???!!!
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I can't execute this SQL query: DELETE FROM piezas WHERE ord_mec = '01-001' I get: ERROR: fmgr_info: function 39816: cache lookup failed Can someone help me with this error ?? ** CREATE TABLE piezas ( n_pieza INTEGER PRIMARY KEY, n_material INTEGER NOT NULL, ord_mec VARCHAR(8) NOT NULL, t_inicioTIMESTAMP DEFAULT 'invalid', t_final TIMESTAMP DEFAULT 'invalid', ref_eisenor VARCHAR(32), ref_cliente VARCHAR(32), n_plano VARCHAR(32), n_lote VARCHAR(8), observaciones VARCHAR(256), FOREIGN KEY (n_material) REFERENCES materia_prima(n_material), FOREIGN KEY (ord_mec) REFERENCES ord_mecanizado(ord_mec) ); CREATE TABLE materia_prima ( n_material INTEGER PRIMARY KEY, fecha DATE NOT NULL DEFAULT 'today', n_albaran INTEGER, n_pedido_mp INTEGER, n_lote VARCHAR(8), cantidadINTEGER, usadas INTEGER DEFAULT '0' CHECK (usadas>=0 AND usadas<=cantidad), torcidas_plano INTEGER DEFAULT '0' CHECK (torcidas_plano>=0 AND torcidas_plano<=cantidad), torcidas_canto INTEGER DEFAULT '0' CHECK (torcidas_canto>=0 AND torcidas_canto<=cantidad), dim_ancho FLOAT, dim_altoFLOAT, dim_largo FLOAT, materialVARCHAR(32), pc_CFLOAT, observaciones VARCHAR(256) ); CREATE FUNCTION materia_prima_release_one() RETURNS OPAQUE AS ' BEGIN UPDATE materia_prima SET usadas=usadas-1 WHERE n_material = OLD.n_material; RETURN OLD; END;' LANGUAGE 'plpgsql'; CREATE TRIGGER piezas_delete AFTER DELETE ON piezas FOR EACH ROW EXECUTE PROCEDURE materia_prima_release_one(); - -- Fernando Moyano Frase del día: - -- Si las mujeres fueran buenas, Dios tendria una. (*) SymeX ==> http://symex.lantik.com (*) WDBIL ==> http://wdbil.sourceforge.net (*) Informate sobre LINUX en http://www.linux.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.4 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE7M7RPoZaf9MvtDvcRApqgAJ9tQjdcd6ACuvWMDrFXR2erAIlfOQCgoyBk TJhAno7UmoZfD/CUxvnMLpM= =HDIQ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: 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
Re: [SQL] What is a "tuple"
Greetings, (and also Alex) On Fri, 22 Jun 2001, Josh Berkus wrote: > > Excuse my lack my 'database'-jargon, but what is a 'tuple'? > Also known as a "Record", or a "Row". The word "tuple" is used because > it can refer to a row returned as part of a result set as well as a > record in a table. Strictly speaking, a row returned from most queries > is not a record, as that row does not exist in permanent storage > anywhere it is created by the query. Hence, "tuple". Thanks! Cheerio! Kr. Bonne. -- KB905-RIPE Belgacom IP networking (c=be,a=rtt,p=belgacomgroup,s=Bonne,g=Kristoff) Internet, IP and IP/VPN [EMAIL PROTECTED] Faxbox : +32 2 2435122 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] unregister
unregister Please don´t send more information about you.
[SQL] timestamp conversion to unisgned long?
All, Perhaps I'm not sing hte correct datatype, but I'd like to be able to convert a timestamp over to an unsigned long to be used within C code and compare to the output of time(). I can't seem to see any easy way of doing this using the built in stuff for postgresql. Ideas? Perhaps I'm using the wrong type? Pierre
Re: [SQL] What is a "tuple"
On Fri, Jun 22, 2001 at 06:31:03PM +0200, Kristoff Bonne wrote: > Greetings, (and also Alex) > > On Fri, 22 Jun 2001, Josh Berkus wrote: > > > Excuse my lack my 'database'-jargon, but what is a 'tuple'? > > > Also known as a "Record", or a "Row". The word "tuple" is used because > > it can refer to a row returned as part of a result set as well as a > > record in a table. Strictly speaking, a row returned from most queries > > is not a record, as that row does not exist in permanent storage > > anywhere it is created by the query. Hence, "tuple". It's probably a back formation from the suffix 'tuple' as in the sequence: single, double, triple, quadruple, quintuple, sextuple, septuple, ... So, mathematicians generalized this (as is their wont) to [algebraic expression]-tuple, such as: n-tuple, (n^2)-tuple Which found their way to The Relational Algebra, simplified to just 'tuple' and hence, to SQL. Ross (way to much detail!) Reedstrom ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] distinguishing different database connections
How about creating a temporary table with the data? That'll be persistant for the session. On Fri, 22 Jun 2001, Markus Wagner wrote: > Hi, > > can I access information on the current connection from within a trigger > function? > I need to identify different server connections somehow. Something like > a "connection id" would be enough. > > The background: > > When a user starts a frontend application (NT, Access) he starts a new > connection to our Linux database server. But for all users the "postgres > user" is the same, since the ODBC connection is hard linked into the > application. The problem is that we need the NT user name within our > trigger (C) functions. > > The idea: > Let's execute a function "logon" whenever an instance of the application > is started. This function will get the NT user name as a parameter. It > will recognize the current connection id and it would store the pair > (connection id, NT user name) in a table. Then, whenever some trigger > needs to know the user name (for logging actions), it could lookup the > user name with the current connection id. ---(end of broadcast)--- TIP 3: 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
Re: [SQL] View performance question
"Josh Berkus" <[EMAIL PROTECTED]> writes: > I tried re-arranging the JOINS as you suggested. There was no impact on > gross performance (still 42 seconds to return the first row). And yes, > it is the classic "star" database. > What follows is the query plan. Hm. The query plan looks very reasonable: hashing the smaller tables is exactly what I'd think it should do. If the rows estimates shown in the plan are accurate, it's hard to see how it's spending 42 seconds on this. The primary tables seem to be big enough that they are probably being divided into hash batches, if you use the default sort_mem setting of 512K. Try increasing sort_mem (SET SORT_MEM TO 5000 or so) and see if that makes a difference. regards, tom lane ---(end of broadcast)--- TIP 3: 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
Re: [SQL] Re: Re: binary data
On Fri, Jun 22, 2001 at 10:22:32AM -0400, Tom Lane wrote:
> He did say the rules for escaping things are tricky ;-). You need to
> double the backslashes, because interpretation of the string literal
> takes off one level of backslashing before bytea ever sees it:
>
> regression=# INSERT INTO log (data) VALUES ('null \\000 null');
> INSERT 273181 1
> regression=# SELECT octet_length(data), data FROM log;
> octet_length | data
> --+--
>10 | plain text
>19 | special chars \012 \001 \002
> 5 | null
>11 | null \000 null
> (4 rows)
And what use some better encoding if you have a lot of binary chars
in data. For example base64, that code 2 chars to 3 instead \\000
that encode 1 char to 4.
Karel
--
Karel Zak <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [SQL] Re: Re: binary data
Karel Zak <[EMAIL PROTECTED]> writes: > On Fri, Jun 22, 2001 at 10:22:32AM -0400, Tom Lane wrote: >> He did say the rules for escaping things are tricky ;-). > And what use some better encoding if you have a lot of binary chars > in data. For example base64, that code 2 chars to 3 instead \\000 > that encode 1 char to 4. Yeah, it's pretty messy. Perhaps we could offer a couple of conversion functions that convert bytea to or from base64 or other popular encodings. bytea is pretty impoverished --- it hasn't received the attention it deserves. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Incremental sum ?
On Fri, Jun 22, 2001 at 12:58:46PM -0400, Tom Lane wrote: > "Ross J. Reedstrom" <[EMAIL PROTECTED]> writes: > > And here's the working example: not the need to GROUP BY, and <= > > to get the current payment. > > > select cust_id,invoice_id,val,paid, (select (sum(val) - sum(paid)) > > from invoices_not_paid where cust_id= i.cust_id and invoice_id <= > > i.invoice_id group by cust_id) as balance from invoices_not_paid i; > > Actually I think you could leave off the inner GROUP BY --- won't there > always be exactly one group, since only one value of inner cust_id is > selected? Sure enough, it works fine. My internal rule: "can't use aggregates without a group by" mis-fired. Ross ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Incremental sum ?
And here's the working example: not the need to GROUP BY, and <= to get the current payment. select cust_id,invoice_id,val,paid, (select (sum(val) - sum(paid)) from invoices_not_paid where cust_id= i.cust_id and invoice_id <= i.invoice_id group by cust_id) as balance from invoices_not_paid i; and it's output: cust_id | invoice_id |val| paid | balance -++---+--+--- 1 | 23 | 10.50 | 3.40 | 7.10 1 | 34 | 5.70 | 0.00 | 12.80 1 | 67 | 23.89 | 4.50 | 32.19 (3 rows) Ross On Fri, Jun 22, 2001 at 11:29:25AM -0400, Alex Pilosov wrote: > It should be done using subqueries. > select ..., ( >select sum(val)-sum(paid) from invoices i2 >where i2.invoice_id and i2.cust_id=i.cust_id >) > from invoices i > > > On 22 Jun 2001, Domingo Alvarez Duarte wrote: > > > I have a problem that requires what I call a incremental sum, lets say > > I have the folowing table (for simplicity): > > > > table invoices_not_paid(cust_id int, invoice_id int, val numeric, paid > > numeric); > > > > with the folowing values: > > > > cust_id invoice_id valpaid > > -- > > 1 23 10.50 3.40 > > 1 34 5.70 0.0 > > 1 67 23.89 4.50 > > > > > > I want show a list like this: > > > > cust_id invoice_id valpaid incremental_not_paid_sum > > - > > 1 23 10.50 3.40 (10.50 - 3.40)7.10 > > 1 34 5.70 0.0(7.10 + 5.70 - 0.0)12.80 > > 1 67 23.89 4.50 (12.80 + 23.89 - 4.50) 31.19 > > > > The operations betwen () are showed only to explain how the > > incremental_not_paid_sum is calculated, The operation requires a > > reference to a previous column or a partial sum of columns till that > > moment, someone has an idea how this can be done using sql ? > > > > ---(end of broadcast)--- > > TIP 4: Don't 'kill -9' the postmaster > > > > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Incremental sum ?
"Ross J. Reedstrom" <[EMAIL PROTECTED]> writes: > And here's the working example: not the need to GROUP BY, and <= > to get the current payment. > select cust_id,invoice_id,val,paid, (select (sum(val) - sum(paid)) > from invoices_not_paid where cust_id= i.cust_id and invoice_id <= > i.invoice_id group by cust_id) as balance from invoices_not_paid i; Actually I think you could leave off the inner GROUP BY --- won't there always be exactly one group, since only one value of inner cust_id is selected? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] View performance question
"Josh Berkus" <[EMAIL PROTECTED]> writes: > I created a view to support comprehensive reporting on one of the > databases I work with. Unfortunately, due to the structure of the > database, which includes 25 reference tables, this requires 3 regular > joins and about 40 LEFT OUTER JOINS, outputting about 100 columns. I suppose this is a star schema, wherein rows of the main tables join to at most one row of the "reference" tables? If so, you probably want to make sure you perform the join of the main tables before you start outerjoining the reference tables onto them. The syntax you are using is constraining the planner to use what's probably not a good plan. See http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/explicit-joins.html regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] View performance question
"Josh Berkus" <[EMAIL PROTECTED]> writes: > I can't figure out how to get Explain to output to a file instead of the > screen. Embarassing, I know, but the command seems to subbornly resist > all command-line re-direction. I think that in psql, EXPLAIN results (and notices in general) come out via stderr not stdout. regards, tom lane ---(end of broadcast)--- TIP 3: 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
