[SQL] how to use recursion to find end nodes of a tree
Hello All, I have been having a really hard time trying to come up with a pl/pgsql recursive function to returns the end nodes of a tree. Here is an example table definition: CREATE TABLE parent_child ( parent_id integer NOT NULL, child_id integer NOT NULL ); INSERT INTO parent_child (parent_id, child_id) VALUES (1, 2); INSERT INTO parent_child (parent_id, child_id) VALUES (1, 3); INSERT INTO parent_child (parent_id, child_id) VALUES (1, 4); INSERT INTO parent_child (parent_id, child_id) VALUES (2, 5); INSERT INTO parent_child (parent_id, child_id) VALUES (2, 6); INSERT INTO parent_child (parent_id, child_id) VALUES (4, 7); INSERT INTO parent_child (parent_id, child_id) VALUES (4, 8); INSERT INTO parent_child (parent_id, child_id) VALUES (4, 9); INSERT INTO parent_child (parent_id, child_id) VALUES (9, 10); This produces the following tree of data: 1 ___|___ | | | 2 3 4 _|_ _|_ | | | | | 5 6 7 8 9 | 10 I want to create a function that returns the terminating nodes of of this tree below a certain level i.e. if I input 1 to the function I need it to return 5,6,3,7,8,10. If I input 4 to the function I would get 7,8,10. I have written recursive functions which return all nodes on a branch of a tree but I can't think of a way to return the end nodes does anyone know of a solution? Many thanks, Mike ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] how to use recursion to find end nodes of a tree
Thankyou very much Yasir and Ross for your help and advice.
I have created a pl/pgsql version of Yasirs algorithm which
works perfectly, I am also looking into improving efficiency
by flaging leaf records. Here is my pl/pgsql solution in case
it helps anyone out:
CREATE OR REPLACE FUNCTION parentchildtest(int4)
RETURNS _int4 AS
'DECLARE
node ALIAS FOR $1;
s INTEGER[];
leaves INTEGER[];
top INTEGER;
counter INTEGER;
leaf_id INTEGER;
popped INTEGER;
child RECORD;
childCount RECORD;
BEGIN
leaf_id := 0;
top := 0;
s := ''{}'';
leaves := ''{}'';
s[top] := node;
counter := 1;
-- t a depth first search
WHILE (counter <> 0) LOOP
popped := s[top];
top := top - 1;
counter := counter - 1;
FOR child IN SELECT pc.child_id FROM parent_child AS pc
WHERE pc.parent_id = popped
LOOP
SELECT INTO childCount COUNT(*) AS count FROM parent_child AS pc
WHERE pc.parent_id = child.child_id;
--a count of zero indicates that child node has no children
IF (childCount.count = 0) THEN
leaves[leaf_id] = child.child_id;
leaf_id := leaf_id + 1;
ELSE
-- not a leaf, so add it to the stack for the next time
through
-- the loop
top := top + 1;
s[top] = child.child_id;
counter := counter + 1;
END IF;
END LOOP;
END LOOP;
RETURN leaves;
END;'
LANGUAGE 'plpgsql' VOLATILE;
-Original Message-
From: Yasir Malik [mailto:[EMAIL PROTECTED]
Sent: 10 April 2006 17:13
To: [EMAIL PROTECTED]
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] how to use recursion to find end nodes of a tree
> Hello All,
>
> I have been having a really hard time trying to come up with a
> pl/pgsql recursive function to returns the end nodes of a tree. Here
> is an example table definition:
>
> CREATE TABLE parent_child (
> parent_id integer NOT NULL,
> child_id integer NOT NULL
> );
>
> INSERT INTO parent_child (parent_id, child_id) VALUES (1, 2); INSERT
> INTO parent_child (parent_id, child_id) VALUES (1, 3); INSERT INTO
> parent_child (parent_id, child_id) VALUES (1, 4); INSERT INTO
> parent_child (parent_id, child_id) VALUES (2, 5); INSERT INTO
> parent_child (parent_id, child_id) VALUES (2, 6); INSERT INTO
> parent_child (parent_id, child_id) VALUES (4, 7); INSERT INTO
> parent_child (parent_id, child_id) VALUES (4, 8); INSERT INTO
> parent_child (parent_id, child_id) VALUES (4, 9); INSERT INTO
> parent_child (parent_id, child_id) VALUES (9, 10);
>
> This produces the following tree of data:
>
> 1
>___|___
> | | |
> 2 3 4
> _|_ _|_
> | | | | |
> 5 6 7 8 9
> |
> 10
>
> I want to create a function that returns the terminating nodes of of
> this tree below a certain level i.e. if I input 1 to the function I
> need it to return 5,6,3,7,8,10. If I input 4 to the function I would
> get 7,8,10. I have written recursive functions which return all nodes
> on a branch of a tree but I can't think of a way to return the end
> nodes does anyone know of a solution?
>
I haven't programmed in PL/pgSQL in a while, but I'll write some pseudo
code. I think the code should be similar:
func(int node)
{
dynamic_array s;
dynamic_array leaves;
int top, count, leaf_id, popped, child;
leaf_id = top = 0;
s[top] = node;
count = 1;
// to a depth first search
while(count != 0)
{
popped = s[top];
top--;
count--;
foreach(select pc.child_id into child from parent_child pc where
pc.parent_id = popped)
{
select * from parect_child pc where parent_id = child;
// a count of zero indicates that child node has no children
if(count_of_above_query = 0)
{
leaves[leaf_id] = child;
leaf_id++;
}
else
{
// not a leaf, so add it to the stack for the next time through
// the loop
top++;
s[top] = child;
count++;
}
}
}
return leaves;
}
Regards,
Yasir
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] The best option to insert data with primary id
Quoting - :
I have a question about how best to insert and manipulate the table with
primary key id for better productivity. I need to insert data into the table
and get last id.
1. First option to take counter Postgres SEQUENCE:
INSERT INTO table (id, ...) VALUES ((SELECT nextval ('seq_table')), ...)
RETURNING (SELECT currval ('seq_table')) AS id
Much simplier:
INSERT INTO table (name, email, salary) VALUES ('joe', 'j...@example.com',
100) RETURNING id;
Cheers,
-Mike
--
Michał Roszka
m...@if-then-else.pl
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] The best option to insert data with primary id
Quoting - :
I know you can skip SEQUENCE - ((SELECT nextval ('seq_table')) do not put
this in the query, my question was that such concurrency, and ids
omitted which can not be inserted but increased with SEQUENCE ?
In the initial message you have been wondering, if you should be worried
about "wasted" sequence tokens. You have mentioned, that your primary key
is of type int8 and so is the sequence range. Do you really expect as many
records and/or insert queries?
If so, consider the id column int8 DEFAULT NULL and an AFTER INSERT trigger
function that would take a nextval of the sequence and update the id
accordingly once the record *has been actually inserted* instead of poking
the sequence each time you *are going to insert* something.
I am pretty sure, that the table is locked to prevent inserts until the
after-insert-trigger is finished.
Cheers,
-Mike
--
Michał Roszka
m...@if-then-else.pl
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] lo_export and files permissions
De: Mike Baroukh <[EMAIL PROTECTED]> À: <[EMAIL PROTECTED]> Objet: lo_export and files permissions Date : lundi 14 août 2000 10:44 Hi everybody. Who can help me with a lo_export() problem ? : I'm using lo_export function in sql statement with pgsql 6.5 on Linux RH 6.2. Data are exported fine. My problem is I can't delete created objects cause they have writing permissions for user postgres only. I tried changing umask for postmaster with no success. When I try to delete, it's within a servlet so running as user nobody.what would be fine is adding write permissions to group postgres so I can add nobody to this group ... Thanks in advance for your help. Mike Baroukh i-panema - 14 Rue Ballu Paris IXeme 06 63 57 27 22 Mike Baroukh i-panema - 10 rue Ballu - 75009 Paris 06 63 57 27 22 - La messagerie itinérante sans abonnement NetCourrier - Web : www.netcourrier.com Minitel : 3615 et 3623 NETCOURRIER Tél : 08 36 69 00 21
Re: [SQL] lo_export and files permissions
Hi, > I was using sql lo_export function before upgraded to > 7.0 (which doesn't allow non-admin to call it). So if > possible, you can use the client function lo_export to > extract the blob. I know it's simple in Perl DBI but > not sure about java. I actually use lo_export with java. But this only allow to export dta, not to delete exported data ?? > Another work-around might be to lo_export the file > to a directory writable by nobody. I tried, don't work. In fact, I can delete by hand, even if i'm not root or postgres by using command "rm -f". But I can't delete using Java's File object. Mike - Original Message - From: Guo Bin <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, August 16, 2000 11:31 AM Subject: Re: [SQL] lo_export and files permissions > Hi, > I was using sql lo_export function before upgraded to > 7.0 (which doesn't allow non-admin to call it). So if > possible, you can use the client function lo_export to > extract the blob. I know it's simple in Perl DBI but > not sure about java. > Another work-around might be to lo_export the file > to a directory writable by nobody. > > Regards, > -- > Guo Bin > > --- [EMAIL PROTECTED] wrote: > > De: Mike Baroukh <[EMAIL PROTECTED]> > > À: <[EMAIL PROTECTED]> > > Objet: lo_export and files permissions > > Date : lundi 14 août 2000 10:44 > > > > Hi everybody. > > > > Who can help me with a lo_export() problem ? : > > > > I'm using lo_export function in sql statement with pgsql > > 6.5 on Linux RH 6.2. > > Data are exported fine. > > My problem is I can't delete created objects cause they > > have writing permissions for user postgres only. > > I tried changing umask for postmaster with no success. > > When I try to delete, it's within a servlet so running as > > user nobody.what would be fine is adding write > > permissions to group postgres so I can add nobody to this > > group ... > > > > Thanks in advance for your help. > > > > > > Mike Baroukh > > i-panema - 14 Rue Ballu Paris IXeme > > 06 63 57 27 22 > > > > Mike Baroukh > > i-panema - 10 rue Ballu - 75009 Paris > > 06 63 57 27 22 > > > > - La messagerie itinérante sans abonnement > > NetCourrier - > > Web : www.netcourrier.com Minitel : 3615 et 3623 > > NETCOURRIER > > Tél : 08 36 69 00 21 > > > __ > Do You Yahoo!? > Send instant messages & get email alerts with Yahoo! Messenger. > http://im.yahoo.com/ >
Re: [SQL] Trigger
There is a sample in postgres documentation. (See below). the only problem is for using langage plpgsql. If it is not understand by your database, you must use command createlang plpgsql dbname as the owner of the database. CREATE TABLE emp ( empname text, salary int4, last_date datetime, last_user name); CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS BEGIN -- Check that empname and salary are given IF NEW.empname ISNULL THEN RAISE EXCEPTION ''empname cannot be NULL value''; END IF; IF NEW.salary ISNULL THEN RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname; END IF; -- Who works for us when she must pay for? IF NEW.salary < 0 THEN RAISE EXCEPTION ''% cannot have a negative salary'', NEW.empname; END IF; -- Remember who changed the payroll when NEW.last_date := ''now''; NEW.last_user := getpgusername(); RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp FOR EACH ROW EXECUTE PROCEDURE emp_stamp(); - Original Message - From: Craig May <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, September 06, 2000 10:27 PM Subject: [SQL] Trigger > > Could someone send me a quick example of a trigger. >
Re: [SQL] problems with postmaster
ipcclean pg_ctl stop Though finding out about ipcclean was not easy considering the output given. There is no mention of it in any of the FAQs. mrc -- Mike Castle Life is like a clock: You can work constantly [EMAIL PROTECTED] and be right all the time, or not work at all www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc We are all of us living in the shadow of Manhattan. -- Watchmen
Re: [SQL] reinitialize a sequence?
setval(); Sometimes it's good to read files in the source tree (such as HISTORY). mrc -- Mike Castle Life is like a clock: You can work constantly [EMAIL PROTECTED] and be right all the time, or not work at all www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc We are all of us living in the shadow of Manhattan. -- Watchmen
Re: [SQL] plpgsql
On Sat, Dec 09, 2000 at 08:16:06AM +, feblec wrote: > FUNCTION: 'plpgsql'. Recognized languages are sql, C, internal and the > created procedural languages. Look at: postgresql-7.0.3/src/pl/plpgsql/enable_plpgsql mrc -- Mike Castle Life is like a clock: You can work constantly [EMAIL PROTECTED] and be right all the time, or not work at all www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc We are all of us living in the shadow of Manhattan. -- Watchmen
Re: [SQL] Invoice number
On Thu, Dec 21, 2000 at 11:10:00AM +0100, Kaare Rasmussen wrote: > - Sequences are not rollback'able. Did you mean SERIAL instead of sequence here? If so, why is no rollbackable an issue? All you should need is unique numbers. Not necessarily exactly sequential numbers. mrc -- Mike Castle Life is like a clock: You can work constantly [EMAIL PROTECTED] and be right all the time, or not work at all www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc We are all of us living in the shadow of Manhattan. -- Watchmen
Re: [SQL] Create table doesn't work in plpgsql
On Thu, Dec 21, 2000 at 11:51:38AM +0100, Volker Paul wrote: > select famname || ', ' || givname from person where id=1234; > I know it's possible by building the select e.g. in bash > and calling psql with it as an argument, but do you see a possibility > that is closer to Postgres, e.g. in plpgsql? Can this be done using tcl or perl? mrc -- Mike Castle Life is like a clock: You can work constantly [EMAIL PROTECTED] and be right all the time, or not work at all www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc We are all of us living in the shadow of Manhattan. -- Watchmen
Re: [SQL] Invoice number
On Thu, Dec 21, 2000 at 05:50:43PM +, Oliver Elphick wrote: > Mike Castle wrote: > >On Thu, Dec 21, 2000 at 11:10:00AM +0100, Kaare Rasmussen wrote: > >> - Sequences are not rollback'able. > > > >Did you mean SERIAL instead of sequence here? > > > >If so, why is no rollbackable an issue? All you should need is unique > >numbers. Not necessarily exactly sequential numbers. > > For invoice numbers, it matters. > > Numbers missing from such a sequence are likely to provoke questions from > auditors and taxmen; why borrow trouble? What do you do on the following scenario: Client 1 is placing an order, gets invoice #1. Client 2 is simultaneously placing an order, and gets invoice #2. Client 1 changes mind and cancels order. Invoice #1 is not used. Invoice #2 is. Client 3 comes along. Do they use invoice #1, out of order, or invoice #3? I suppose of the assignment of the invoice number is set up in such a way as it is the very last action performed just before the commit, you should be able to ensure that indeed the situation of having to deal with a rollback would never occur (outside of a system crash between assignment of invoice and commit, and that could be explained to auditors). [What happens in a paper world if a cup of coffee is spilt on some invoices, and these precious items are thrown in the trash?] mrc -- Mike Castle Life is like a clock: You can work constantly [EMAIL PROTECTED] and be right all the time, or not work at all www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc We are all of us living in the shadow of Manhattan. -- Watchmen
Re: [SQL] Using INDEX on date/time values // Re: how to select a time frame on timestamp rows.
On Sun, Jan 14, 2001 at 04:46:08AM +0100, Alvar Freude wrote: > Peter Eisentraut schrieb: > > > > > today i was trying to perform a query on a database using a time stamp > > > field, i need to get all records which belong to year 2000, month 11, > [...] > > select user_name from tbacct where extract(month from acct_timestamp) = 11 ... > > is there any way of using an index for selecting some rows, e.g. > selecting all data from one month? What about select blah from foo where month >= 2000-11-01 and month < 2000-12-01 Fix up as appropriate. mrc -- Mike Castle Life is like a clock: You can work constantly [EMAIL PROTECTED] and be right all the time, or not work at all www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc We are all of us living in the shadow of Manhattan. -- Watchmen
Re: [SQL] Problem with Dates
On Thu, Jan 25, 2001 at 08:49:27AM +1300, Glen and Rosanne Eustace wrote: > I am using 7.0.3, > I have a column tstamp defined to be 'date'; > > With a current value of '31-12-2000', > if I update tstamp=tstamp+'1 year'::timespan > I get '1-1-2002' This almost sounds like it takes the year 2000, figures out it needs 366 days, and uses that for "1 year". However, I don't see that error myself: template1=> select '2000-12-31'::timestamp+'1 year'; ?column? 2001-12-31 00:00:00-08 (1 row) template1=> select '2000-12-31'::timestamp+'1 year'::timespan; ?column? 2001-12-31 00:00:00-08 (1 row) mrc -- Mike Castle Life is like a clock: You can work constantly [EMAIL PROTECTED] and be right all the time, or not work at all www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc We are all of us living in the shadow of Manhattan. -- Watchmen
[SQL] Re: Problem with Dates
On Thu, Jan 25, 2001 at 03:06:38PM +1300, Glen and Rosanne Eustace wrote: > pressie# select '31/12/2000'::date + '1 year'::timespan; > ?column? > - > 01/01/2002 00:00:00.00 NZDT > (1 row) What are the outputs of select '31/12/2000'::date; select '31/12/2000'::date + '365 days'::timespan; and 364, 363, etc. Not sure if gets you anywhere. But data points. There is an email archive on the postgresql.org website you could search if you think it's been answered before. mrc -- Mike Castle Life is like a clock: You can work constantly [EMAIL PROTECTED] and be right all the time, or not work at all www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc We are all of us living in the shadow of Manhattan. -- Watchmen
[SQL] Is there anything like DESCRIBE?
Hi, I have a number of empty tables and I want to get the column names and data types with an SQL statement. I want to do this procedurally, not interactively (so I can't use \d in psql). Postgres doesn't support DESCRIBE... is there any other way to do this? Thanks! Mike
[SQL] Re: Is there anything like DESCRIBE?
I got a response in email, so I assume the author wants to remain anonymous. He wrote: >There's an option to psql (I think -E or -e) that will make it echo all >the SQL queries it does when you do something like "\d table" So running a "psql -E " and then doing a "\d " will give you information similar to what you would normally get from a DESCRIBE. Mike -- "Mike D'Agosta" <[EMAIL PROTECTED]> wrote in message 94n93j$2j6j$[EMAIL PROTECTED]">news:94n93j$2j6j$[EMAIL PROTECTED]... > Hi, > >I have a number of empty tables and I want to get the column names and > data types with an SQL statement. I want to do this procedurally, not > interactively (so I can't use \d in psql). Postgres doesn't > support DESCRIBE... is there any other way to do this? > > Thanks! > Mike
RE: [GENERAL] Date question
How about: SELECT '2001-03-06'::timestamp + '1 Year'; Hope that helps, Mike Mascari -Original Message- From: Boulat Khakimov [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, March 06, 2001 2:20 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject:[GENERAL] Date question Hi, Im a little bit stuck here. Does anyone know how to get date in format '-MM-DD' of a date one year from now. So for example today is '2001-03-06' I need to get date 12 months from now which will be '2002-03-06' in todays case... In mysql I used DATE_ADD(CURDATE(), INTERVAL 12 MONTH) , but that doesnt work in PG. Regards, Boulat Khakimov -- Nothing Like the Sun ---(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 ---(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] Where are the 7.1 RPM's?
On Tue, Apr 17, 2001 at 06:12:40PM -0700, clayton wrote: > if a spec file was made avail we could all make rpms! You too can ./configure ; make ; make install !! -- Mike Castle Life is like a clock: You can work constantly [EMAIL PROTECTED] and be right all the time, or not work at all www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc We are all of us living in the shadow of Manhattan. -- Watchmen ---(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] has anyone tried running in MAC OS X
On Thu, May 17, 2001 at 08:48:39PM -0400, postgresql wrote: > I guess the subject line says ithas anyone tried running > PostgreSQL in MAC OS X. Isn't OS X fairly close to NEXTSTEP (or whatever the casing is these days)? How well does PostgreSQL work on NS? Did you try building it yet? :-> mrc -- Mike Castle Life is like a clock: You can work constantly [EMAIL PROTECTED] and be right all the time, or not work at all www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc We are all of us living in the shadow of Manhattan. -- Watchmen ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] [GENERAL] arrays
On Sun, 29 Sep 2002, Bruce Momjian wrote: Apologies in advance if there is a more appropriate list. We are currently developing a database to host some complicated, XMl layered data. We have chosen postgres because of its ability to store multidimensional arrays. We feel that using these will allow us to simplify the database structure considerably by storing some data in multidimensional arrays. However, we currently have some dissenters who believe that using the multidimensional arrays will make queries slower and unneccesarily complicated. Its hard for us to evaluate in advance because none of us have much experience with postgres (we are web based and have relied on MySQL for most projects up to this point). I have several questions related to the scenario above. 1) are SQL queries slower when extracting data from multidimensional arrays 2) are table joins more difficult or unneccesarily complicated 3) can you do selects on only a portion of a multidimensional array. That is, if you were storing multilanguage titles in a two dimensional array, [en], "english title" [fr], "french title" could you select where title[0] = 'en' I know these may sound like terribily stupid questions. but we need some quick guidance before proceeding with a schema that relies on these advanced data features of postgres tia mike ___ This communication is intended for the use of the recipient to whom it is addressed, and may contain confidential, personal, and or privileged information. Please contact us immediately if you are not the intended recipient of this communication, and do not copy, distribute, or take action relying on it. Any communications received in error, or subsequent reply, should be deleted or destroyed. --- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [GENERAL] arrays
On Mon, 30 Sep 2002, Bruno Wolff III wrote: > > 3) can you do selects on only a portion of a multidimensional array. That > > is, if you were storing multilanguage titles in a two dimensional array, > > > > [en], "english title" > > [fr], "french title" > > > > could you select where title[0] = 'en' > > It is unusual to want to store arrays in a database. Normally you want to > use additional tables instead. For example multilanguage titles is something > I would expect to be in a table that had a column referencing back to > another table defining the object a title was for, a column with the > title and a column with the language. > The chances are very very good that in 99% of the cases we'd only ever have a single title. multiple titles would be rare. and, to make it worse, there are several instances of this where you need a table but its seems overkill for the odd 1% time when you actually need teh extra row. of course, the there'd be a language lookup table. what about the speed and query issue? m ___ This communication is intended for the use of the recipient to whom it is addressed, and may contain confidential, personal, and or privileged information. Please contact us immediately if you are not the intended recipient of this communication, and do not copy, distribute, or take action relying on it. Any communications received in error, or subsequent reply, should be deleted or destroyed. --- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [GENERAL] arrays
On Mon, 30 Sep 2002, Josh Berkus wrote: I have a very good sense of the strengths of relational databases. But they are also limited when it comes to object orientaed data (like XML records). I though arrays would be a way to simply the complexity you get when you try and map objects to relations. so a couple more questions then Is Cache open source? are the XML databases that are evolved and sophisticated enough to use in production environments. m > of thought behind it -- it can solve a lot of problems. > > 2) Shift over to an XML database or a full-blown OODB (like Cache'). > > Good luck. > > -Josh Berkus > > > > Mike Sosteric <[EMAIL PROTECTED]> Managing Editor, EJS <http://www.sociology.org/> Department of Global and Social Analysis Executive Director, ICAAP <http://www.icaap.org/> Athabasca University Cell: 1 780 909 1418 Simon Fraser University Adjunct Professor Masters of Publishing Program -- This troubled planet is a place of the most violent contrasts. Those that receive the rewards are totally separated from those who shoulder the burdens. It is not a wise leadership - Spock, "The Cloud Minders." ___ This communication is intended for the use of the recipient to whom it is addressed, and may contain confidential, personal, and or privileged information. Please contact us immediately if you are not the intended recipient of this communication, and do not copy, distribute, or take action relying on it. Any communications received in error, or subsequent reply, should be deleted or destroyed. --- ---(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] [GENERAL] arrays
On Mon, 30 Sep 2002, Josh Berkus wrote: thanks for this. we will stick with the relational model. m > > Mike, > > > I have a very good sense of the strengths of relational databases. But > > they are also limited when it comes to object orientaed data (like XML > > records). I though arrays would be a way to simply the complexity you get > > when you try and map objects to relations. > > In my experience, most XML records are, in fact, simple tree structures that > are actually easy to represent in SQL. But I don't know about yours. > > Certainly the translation of XML --> SQL Tree Structure is no more complex > than XML --> Array, that I can see. > > > Is Cache open source? > > No. It's a proprietary, and probably very expensive, database. There are no > open source OODBs that I know of, partly because of the current lack of > international standards for OODBs. > > > are the XML databases that are evolved and sophisticated enough to use in > > production environments. > > I don't know. The last time I evaluated XML databases was a year ago, when > there was nothing production-quality in existence. But I don't know what > the situation is now. > > -- > -Josh Berkus > Aglio Database Solutions > San Francisco > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > Mike Sosteric <[EMAIL PROTECTED]> Managing Editor, EJS <http://www.sociology.org/> Department of Global and Social Analysis Executive Director, ICAAP <http://www.icaap.org/> Athabasca University Cell: 1 780 909 1418 Simon Fraser University Adjunct Professor Masters of Publishing Program -- This troubled planet is a place of the most violent contrasts. Those that receive the rewards are totally separated from those who shoulder the burdens. It is not a wise leadership - Spock, "The Cloud Minders." ___ This communication is intended for the use of the recipient to whom it is addressed, and may contain confidential, personal, and or privileged information. Please contact us immediately if you are not the intended recipient of this communication, and do not copy, distribute, or take action relying on it. Any communications received in error, or subsequent reply, should be deleted or destroyed. --- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [GENERAL] arrays
On Mon, 30 Sep 2002, Josh Berkus wrote: Don't worry. Our biggest problem is that each XML data entry, say This is the title has an language attribute. if there are, say 67 seperate items, each with multiple languages, then the comlexity of the table structure skyrockets because you have to allow for multiple titles, multiple names, multiple everything. the resulting relational model is icky to say the least. The question, is how to simplify that. I had thought arrays would help because you can store the multiple language strings in a single table along with other records.. any ideas? m > > Mike, > > > thanks for this. we will stick with the relational model. > > Hey, don't make your decision entirely based on my advice.Do some > research! I'm just responding "off the cuff" to your questions. > > If you do take the relational approach, post some sample problems here and > people can help you with how to represent XML data relationally. > > -- > -Josh Berkus > Aglio Database Solutions > San Francisco > > Mike Sosteric <[EMAIL PROTECTED]> Managing Editor, EJS <http://www.sociology.org/> Department of Global and Social Analysis Executive Director, ICAAP <http://www.icaap.org/> Athabasca University Cell: 1 780 909 1418 Simon Fraser University Adjunct Professor Masters of Publishing Program -- This troubled planet is a place of the most violent contrasts. Those that receive the rewards are totally separated from those who shoulder the burdens. It is not a wise leadership - Spock, "The Cloud Minders." ___ This communication is intended for the use of the recipient to whom it is addressed, and may contain confidential, personal, and or privileged information. Please contact us immediately if you are not the intended recipient of this communication, and do not copy, distribute, or take action relying on it. Any communications received in error, or subsequent reply, should be deleted or destroyed. --- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [GENERAL] arrays
On Mon, 30 Sep 2002, Josh Berkus wrote:
>
> Question #1: If each record has 67 fields, and each field may appear in
> several languages, is it possible for some fields to be in more languages
> than others? I.e. if "title-en" and "title-de" exist, does it follow that
> "content-en" and "content-de" exist as well? Or not?
yes.
>
> Question #2: Does your XML schema allow locall defined attributes? That is,
> do some records have entire attributes ("fields" ) that other records do not?
yes.
>
> Suggestion #1: Joe Celko's "SQL for Smarties, 2nd Ed." is an excellent book
> for giving you ideas on how to adapt SQL structures to odd purposes.
I have ordered the book from amazon.ca
m
>
> --
> -Josh Berkus
> Aglio Database Solutions
> San Francisco
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>
Mike Sosteric <[EMAIL PROTECTED]> Managing Editor, EJS
<http://www.sociology.org/>
Department of Global and Social Analysis Executive Director, ICAAP
<http://www.icaap.org/>
Athabasca University Cell: 1 780 909 1418
Simon Fraser University Adjunct Professor
Masters of Publishing Program
--
This troubled planet is a place of the most violent contrasts.
Those that receive the rewards are totally separated from those who
shoulder the burdens. It is not a wise leadership - Spock, "The Cloud Minders."
___
This communication is intended for the use of the recipient to whom it
is addressed, and may contain confidential, personal, and or privileged
information. Please contact us immediately if you are not the intended
recipient of this communication, and do not copy, distribute, or take
action relying on it. Any communications received in error, or
subsequent reply, should be deleted or destroyed.
---
---(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] [pgadmin-support] hi
lz,
You
could use the plpgsql function language and create a function that tests for the
existence of the file and drop it if it does.
Something like this:
select
dropTableIfExists('test');
The
dropTableIfExists would be the plpgsql function that you would need to
write.
Later,
Mike
Hepworth..
-Original Message-From: lz John
[mailto:[EMAIL PROTECTED]]Sent: Thursday, October 17, 2002 1:07
AMTo: [EMAIL PROTECTED]Cc:
[EMAIL PROTECTED]Subject: [pgadmin-support]
hi
i don't send mail to [EMAIL PROTECTED],but i need
help
how to migrate sql from MS sql server to postgresql?
i'd like to tranfer sql schema from MS
serverExample:***1*if
exists (select * from sysobjects where id = object_id(N'[admin].[test]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [admin].[test]
go***2*CREATE
TABLE [admin].[test] ([test_name] [char] (50) NOT NULL ,[test_value]
[int] NOT NULL ) i can only realize part 2. i don't know
how to realize part 1.in other words,i want to know how to check if a
table exist in
postgresql***2*
create table test( test_name char (50) not
null, test_value int not null )thanks for any
advice!!
Do You Yahoo!?"·¢¶ÌÐÅÓ®ÊÖ»ú,¿ìÀ´²Î¼ÓÑÅ»¢¾ÞÐÇÐã!"
[SQL] Question about slow Select when using 'IN'.
Hi all, I hope someone can help me out. I'm doing single-table select statements on a large table and I could use some help in speeding it up. My query is of the form: SELECT col, count(col) FROM tab WHERE id IN (3, 4,7,2, ...) GROUP BY COL ORDER BY count for a very large number of rows. I have an index on id, so the explain looks like: Aggregate (cost=12.12..12.14 rows=1 width=5) -> Group (cost=12.12..12.13 rows=4 width=5) -> Sort (cost=12.12..12.12 rows=4 width=5) -> Index Scan using col_id_idx2, col_id_idx2, col_id_idx2, col_id_idx2 on tab (cost=0.00..12.08 rows=4 width=5) So, it does a separate index scan for each row in the IN statement, which takes forever. How do I force the query parser to emulate the behaviour displayed by this query: SELECT col, count(col) FROM tab WHERE (0 = id % 5) GROUP BY COL ORDER BY count Aggregate (cost=3.75..3.86 rows=2 width=5) -> Group (cost=3.75..3.81 rows=21 width=5) -> Sort (cost=3.75..3.75 rows=21 width=5) -> Index Scan using col_id_idx2 on tab (cost=0.00..3.29 rows=21 width=5) Which only does one index scan for an equivelant number of records. Thanks for any help. Please cc to my e-mail. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Large Query Question. (Slow Select while using 'IN')
Hi all, I hope someone can help me out. I'm doing single-table select statements on a large table and I could use some help in speeding it up. My query is of the form: SELECT col, count(col) FROM tab WHERE id IN (3, 4,7,2, ...) GROUP BY COL ORDER BY count for a very large number of rows. I have an index on id, so the explain looks like: Aggregate (cost=12.12..12.14 rows=1 width=5) -> Group (cost=12.12..12.13 rows=4 width=5) -> Sort (cost=12.12..12.12 rows=4 width=5) col_id_idx2 on tab (cost=0.00..12.08 rows=4 width=5) So, it does a separate index scan for each row in the IN statement, which takes forever. How do I force the query parser to emulate the behaviour displayed by this query: SELECT col, count(col) FROM tab WHERE (0 = id % 5) GROUP BY COL ORDER BY count Aggregate (cost=3.75..3.86 rows=2 width=5) -> Group (cost=3.75..3.81 rows=21 width=5) -> Sort (cost=3.75..3.75 rows=21 width=5) -> Index Scan using col_id_idx2 on tab (cost=0.00..3.29 rows=21 width=5) Which only does one index scan for an equivelant number of records. Thanks for any help. Please cc to my e-mail. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Ran out of connections
Hi all. Twice this week, I've come to work to find my Postgres server out of connections... effectively freezing my web server. Today, before I rebooted the entire system, I did a ps -auxw and kept the file to study. I didn't find too many clients running. But I did find a whole LOT of postgres processes running, idle. BTW, one of the postgres processes was doing a vacuum analyze. I'm running 7.2. Can anyone tell me how to fix this? The out put of the ps command can be seen at http://dominion.dyndns.org/~mdiehl/ps.txt Thanx in advance, -- Mike Diehl Network Tools Devl. SAIC at Sandia Labs (505) 284-3137 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Ran out of connections
On Wednesday 04 December 2002 03:25 pm, Roberto Mello wrote: > On Wed, Dec 04, 2002 at 03:08:35PM -0700, Mike Diehl wrote: > > Can anyone tell me how to fix this? The out put of the ps command > > can be seen at http://dominion.dyndns.org/~mdiehl/ps.txt > > Are you using PHP by chance? I've seen this behavior under Apache+PHP > before. My "fix" (workaround rather) was to disable persistent > connections. Nope. I'm using Perl and cgi. I've got some perl that runs via cron, and some more that runs via apache. I'm not even using ModPerl. It did occur to me that since some of my scripts communicate with other devices, that I may have some IO blocking, or zombies, but the ps output didn't indicate that. I can't see that many scripts running. Usually, I see one postgres process for each script/cgi that is running. Not in this case. -- Mike Diehl Network Tools Devl. SAIC at Sandia Labs (505) 284-3137 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] SCHEMA's
Need some help with schema's. I want to be able make a user have a default schema other than public. I want to be able to have several schema's with the same group of tables define in each one. (Example) schema a (users 1,2,3) table a table b table c schema b (users 4,5,6) table a table b table c schema c (users 7,8,9) table a table b table c When a user logs into the database they will go to there default schema and not the public schema. (Example) User 5 logs in and by default uses a, b, c tables under schema b. Any ideas? Thanks, Mike Hepworth. ---(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
[SQL] Rule for updating through a view.
Ok, I found the documentation on using views to udpate the underlying database. But I can't seem to get my head around making it actually work, because updates may change only a few columns, and the columns in my views come from multiple tables. Could someone provide an example (CC'ing me, please, as I'm not on the list) of setting up a rule on a joined view to udpated both the underlying tables? Thanks, http://www.mired.org/consulting.html Independent WWW/Perforce/FreeBSD/Unix consultant, email for more information. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] casting to arrays
I have a rather odd table structure that I would like to simplify to be a view (for some definition of simplify). The current idea I have is to shovel values from multiple rows in one table into an array in the view. The tables look something like this: create table person ( id serial, name varchar ); create table stuff ( person_id references person (id) on delete restrict, stuff_name varchar ); The view would go something like: create view person_with_stuff as select p.id as id, p.name as name, ( select s.stuff_name from stuff where s.person_id = p.id )::varchar[] from person p; Is anything like this possible? I know this may not be good form, but unfortunately (or perhaps fortunately, since it means I have a job) there are business reasons for this, supporting old apps and such. Thanks in advance! -- Mike Rylander ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] casting to arrays
Thank you! This is great news. Is there a projected release date for 7.4?
Also, is there a published roadmap, or should I just get on the developers
list?
Thanks again.
---
Mike Rylander
On Friday 18 July 2003 05:34 pm, Joe Conway wrote:
> Mike Rylander wrote:
> > I have a rather odd table structure that I would like to simplify to be a
> > view (for some definition of simplify). The current idea I have is to
> > shovel values from multiple rows in one table into an array in the view.
> > The tables look something like this:
>
>
>
> > Is anything like this possible? I know this may not be good form, but
> > unfortunately (or perhaps fortunately, since it means I have a job) there
> > are business reasons for this, supporting old apps and such.
>
> Not possible in current releases, but it will be in 7.4 (about to start
> beta). It looks like this:
>
> create table person (id integer, name varchar);
> insert into person values(1,'Bob');
> insert into person values(2,'Sue');
>
> create table stuff (person_id integer, stuff_name text);
> insert into stuff values(1,'chair');
> insert into stuff values(1,'couch');
> insert into stuff values(1,'lamp');
> insert into stuff values(2,'table');
> insert into stuff values(2,'shirt');
>
> create or replace view person_with_stuff as select p.id as id, p.name as
> name, ARRAY(select s.stuff_name from stuff s where s.person_id = p.id)
> as stuff from person p;
>
> regression=# select * from person_with_stuff;
> id | name | stuff
> +--+
>1 | Bob | {chair,couch,lamp}
>2 | Sue | {table,shirt}
> (2 rows)
>
> HTH,
>
> Joe
---(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] converting interval to timestamp
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 If the number of seconds between timestamps is acceptable then this is the solution I use: SELECT ABS(EXTRACT(EPOCH FROM t.field1) - EXTRACT(EPOCH FROM t.field2)) AS diff FROM table AS t; Adjust to your column and WHERE needs. One caveat: this only works for dates within the UNIX epoch ( after 1969-12-31). On Thursday 31 July 2003 12:05 pm, teknokrat wrote: > The difference of two dates/timestamps always gives an interval. is > there a way to convert this interval into number such as number of > milliseconds or number of days? > > Also does anyone know what field type an interval would map to in jdbc? > > thanks > > > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match - -- Mike Rylander -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (GNU/Linux) iD8DBQE/KmAOgRDV1fFA+3cRAgbVAJ9B03Pxsn+N+Xg2C/a4gw3j28KSsgCeNA7+ y2rYedgRdTY/BiNSfVJTvOs= =kVkm -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] "SELECT IN" Still Broken in 7.4b
I'm sure many on this list are sick of hearing about this problem, but it was on the fix list for 7.4, but doesn't appear to have been changed. You can see one of the many threads on the problem at: http://archives.postgresql.org/pgsql-sql/2003-05/msg00352.php Basically, queries of the form SELECT FROM WHERE IN () take forever for high numbers of rows in the IN clause. We've done timing on 7.3 and 7.4b and there is no speed improvement on these queries. Does anyone know what the status of this bug is? -- ___ Front Logic Inc. Tel: 306.653.2725 x14 226 Pacific Ave or 1.800.521.4510 x14 Saskatoon, SK Fax: 306.653.0972 S7K 1N9 Canada Cell: 306.717.2550 http://www.frontlogic.com [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 IN" Still Broken in 7.4b
On Wed, 20 Aug 2003, Stephan Szabo wrote: > > On Wed, 20 Aug 2003, Mike Winter wrote: > > > I'm sure many on this list are sick of hearing about this problem, but it > > was on the fix list for 7.4, but doesn't appear to have been changed. > > IN (subselect) was changed for 7.4 (although I'm not sure of the list > mentions the difference). I don't know of any major changes to IN > (valuelist) though. Thanks, Stephan. I was really hoping that the IN(valuelist) was going to be changed at the same time, because it really is unusable for anything over a couple of thousand values. -- ___ Front Logic Inc. Tel: 306.653.2725 x14 226 Pacific Ave or 1.800.521.4510 x14 Saskatoon, SK Fax: 306.653.0972 S7K 1N9 Canada Cell: 306.717.2550 http://www.frontlogic.com [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] "SELECT IN" Still Broken in 7.4b
On Wed, 20 Aug 2003, Rod Taylor wrote: > Ensure your IN list is unique. You might find better times by through > an indexed temp table. That is what I ended up doing, but it's not a very elegant solution. MySQL does queries of this type orders of magnitudes faster than Postgres on large value lists, although I have no specific algorithmic solutions to offer for how to make it faster. I don't believe making the IN lists unique has any affect on performance. -- ___ Front Logic Inc. Tel: 306.653.2725 x14 226 Pacific Ave or 1.800.521.4510 x14 Saskatoon, SK Fax: 306.653.0972 S7K 1N9 Canada Cell: 306.717.2550 http://www.frontlogic.com [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] new max function
Here's mine: CREATE FUNCTION max2 (INTEGER,INTEGER) RETURNS INTEGER LANGUAGE SQL AS 'SELECT CASE WHEN $1 > $2 THEN $1 ELSE $2 END'; This returns: database=# select max2(1,2); max2 -- 2 (1 row) database=# select max2(3,1); max2 -- 3 (1 row) On Friday 17 October 2003 02:13 pm, Rodrigo Gesswein wrote: > Hello! > >I'm looking for a function to calculate the max value from two numbers, > something like max2(a,b) with a,b int > >Does anyone have the trick ? > >Thank you in advance.. > > Rodrigo! > > ---(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 -- Mike Rylander ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] How Do I Toggle Quoted Identifiers?
I'm on PHP 4.2.2 and RedHat 9 with PGSQL. I want to turn quoted identifiers off with my SQL queries. What SQL statement or .CONF setting do I need to change so that I can turn quoted identifiers off? Quoted identifiers, as I understand them, are where you must put double quotes around any table or column name in order to preserve its case, or in order to preserve an internal space it may have in the identifier name. I was looking for something like: SET QUOTED_IDENTIFIERS = OFF; SELECT Username FROM Logins; For that matter, how do I turn case-sensitivity off? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Dramatic slowdown of sql when placed in a function
Jeff Boes <[EMAIL PROTECTED]> wrote in message news:<[EMAIL PROTECTED]>... > At some point in time, [EMAIL PROTECTED] (Mike Moran) wrote: > > >Hi. I currently have a fairly large query which I have been optimizing > >for later use in a function. There are things in the query which I > >have been keeping constant whilst optimizing, but which are variables > >in the function. When I run this query as sql, with the `variables' > >constant, I get a runtime of about 3 or 4 seconds. However, when I > >place this same sql in an sql function, and then pass my constants > >from before in as arguments, I get a runtime of about 215 seconds. > > [ ... ] > > My first guess would be that the indexes being used in the query are > mis-matching on data type compared to your function arguments. For instance, [ ... ] Hi. I think it is something like this that is going on. A couple of the variables are dates which are specified in the table as 'timestamp without time zone', whilst the function was using 'timestamp with time zone'. I confirmed the slowdown by casting the types to the 'slow' type in the original query. However, when I change the signature of the function and do a cast of the variable within the function body I still get the same speed. I even cast the arguments to the function given at the psql prompt and still I get the same speed. I will have to sanity-check this again tomorrow (posting from home) but I couldn't see anywhere else that I could force the type to be the same as that specified on the table. Many thanks, -- Mike ---(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] How do i extract a certain bit from a bigint column
On Wednesday 12 May 2004 07:05 am, Mats Sjöberg wrote: > Hello everyone > In a table i have a column status of type bigint. > I need to create a view of the table including all rows with bit 4 set > (value 8). > At the same time i need to exclude excludig all rows with bit 2 set. > > What is the syntax to extract those bits? > I have tested get_bit(string, offset) but this requires a string, and not a > bigint. > Is there a function to The easiest way is to test for a bit using bitwise and: SELECT * FROM table WHERE (status & 2::BIGINT) <> 0 AND (status & 8::BIGINT) = 0; -miker ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] [HACKERS] MERGE-esque Functionallity (was: Adding MERGE to the TODO list (resend with subject))
On Tuesday 11 May 2004 09:44 am, Bruce Momjian wrote: [snip] > > > > Bruce Momjian kirjutas E, 10.05.2004 kell 06:58: > > > > > Added to TODO: > > > > > > > > > > * Add MERGE command that does UPDATE, or on failure, INSERT > > > > [snip] Hello all. I have been lurking here for a bit and the MERGE topic (on [HACKERS]) caught my eye, so I had a go at implementing a basic version of MERGE-on-INSERT in pl/pgsql. It is attached below, and any comments are welcome. I find it useful on "status" type tables, though it is not very nice when there are many clients (table locking to avoid race conditions). Hope someone will find it useful! -miker -- -- Merge on INSERT functionallity for Postgres 7.3+ -- -- [EMAIL PROTECTED] / 5-14-04 -- -- CAVEAT EMPTOR: Uses table locks to avoid concurrency issues, -- so it WILL slow down heavily loaded tables. -- This effecivly puts the table into -- TRANSACTION ISOLATION LEVEL SERIALIZABLE mode. -- CREATE OR REPLACE FUNCTION add_merge_on_insert ( TEXT, -- table name TEXT, -- key column TEXT[] -- column list to update on deduplication ) RETURNS TEXT RETURNS NULL ON NULL INPUT SECURITY INVOKER LANGUAGE 'plpgsql' AS ' DECLARE tablename ALIAS FOR $1; keycol ALIAS FOR $2; updatecols ALIAS FOR $3; trig TEXT; arraydims TEXT; BEGIN trig := \' CREATE FUNCTION "\' || tablename || \'_merge_on_insert_proc" () RETURNS TRIGGER AS \'\' DECLARE orig \' || quote_ident(tablename) || \'%ROWTYPE; BEGIN LOCK TABLE \' || quote_ident(tablename) || \' IN ROW EXCLUSIVE MODE; SELECT INTO orig * FROM \' || quote_ident(tablename) || \' WHERE \' || quote_ident(keycol)|| \' = NEW.\' || quote_ident(keycol) || \'; IF NOT FOUND THEN RETURN NEW; END IF; UPDATE \' || quote_ident(tablename) || \' SET \'; arraydims := array_dims(updatecols); FOR i IN 1 .. (substring(arraydims from (position(\':\' in arraydims) + 1 ) for ( position(\']\' in arraydims) - (position(\':\' in arraydims) + 1 ) )))::INT LOOP trig := trig || quote_ident(updatecols[i]) || \' = COALESCE( NEW.\' || quote_ident(updatecols[i]) || \', orig.\' || quote_ident(updatecols[i]) || \'), \'; END LOOP; trig := substring( trig from 0 for (character_length(trig) - 1)); trig := trig || \' WHERE \' || quote_ident(keycol)|| \' = NEW.\' || quote_ident(keycol) || \'; RETURN NULL; END; \'\' LANGUAGE \'\'plpgsql\'\'; \'; EXECUTE trig; EXECUTE \' CREATE TRIGGER "\' || tablename || \'_merge_on_insert_trig" BEFORE INSERT ON \' || quote_ident(tablename) || \' FOR EACH ROW EXECUTE PROCEDURE "\' || tablename || \'_merge_on_insert_proc" (); \'; RETURN \'FUNCTION \' || tablename || \'_merge_on_insert_proc (); TRIGGER \' || tablename || \'_merge_on_insert_trig;\'; END; '; CREATE OR REPLACE FUNCTION remove_merge_on_insert ( TEXT -- table name ) RETURNS TEXT RETURNS NULL ON NULL INPUT SECURITY INVOKER LANGUAGE 'plpgsql' AS ' BEGIN EXECUTE \'DROP FUNCTION "\' || $1 || \'_merge_on_insert_proc" () CASCADE;\'; RETURN \'FUNCTION \' || $1 || \'_merge_on_insert_proc (); TRIGGER \' || $1 || \'_merge_on_insert_trig;\'; END; '; ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Trigger problem
kasper wrote: > Hi guys > > Im tryint to make a trigger that marks a tuble as changed whenever someone > has updated it > > my table looks something like this > > create table myTable ( > ... > changed boolean; > ) > > now ive been working on a trigger and a sp that looks like this, but it > doesnt work... > > create function myFunction returns trigger as ' > begin > new.changed = true; The line above is using the SQL equaliy opperator, you want the assignment operator: := as in new.changed := true; > return new; > end; > ' language 'plpgsql'; > > create trigger myTrigger > after update on lektioner > for each row > execute procedure myFunction(); > > > the code compiles, runs, and doesnt whine about anything, but nothing > changes... > > any ideas?? > > - Kasper -miker ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] How to filter on timestamps?
B.W.H. van Beest wrote: > > > I have a table where one of the columns is of type 'TIMESTAMP' > > How can I do a query to filter on the TIMESTAMP value, e.g. to obtain > all rows earlier than a certain time stamp? Think of the math opperators '<' and '>' as 'before' and 'after', respectively. ie: SELECT * FROM table WHERE begin_date > '2004-07-06'; You can also use BETWEEN: SELECT * FROM table WHERE update_timestamp BETWEEN '2004-07-01' AND '2004-07-06'; Remember that when timestamps are cast to dates, they are cast with 00:00:00.0 as the time part. See the docs on this at http://www.postgresql.org/docs/7.4/interactive/datatype-datetime.html and http://www.postgresql.org/docs/7.4/interactive/functions-datetime.html . --miker > > Regards, ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] implementing an out-of-transaction trigger
>I've come across a situation where I'd like to use some kind of "out-of-transaction >trigger" to do some processing after changes to some tables, but without extending >the duration of the main transaction. Of course, it's important that the processing be >completed so it has to be, as far as possible, reliable and "safe". The extra >processing should be completed within a reasonable time after the original >transaction, but it needn't happen immediately. Check out http://www.postgresql.org/docs/7.4/static/sql-listen.html and http://www.postgresql.org/docs/7.4/static/sql-notify.html Then look at the Rules system for generating a NOTIFY: http://www.postgresql.org/docs/7.4/static/sql-createrule.html --miker ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] sql
On Mon, 25 Oct 2004 10:13:37 +0200, cristivoinicaru <[EMAIL PROTECTED]> wrote: > I have a postgres table like this: > > CREATE TABLE "temp50" ( > "gc" character varying(36), > "co" character varying(7), > "data" date, > "ora" smallint > > ); > > It contains the following records: > > 5003707G9G9419 22012BB 10-14-2004 82 > 5003707G9G9419 22012BC 10-14-2004 44 > 5003707G9G9419 22022BB 10-14-2004 82 > 5003707G9G9420 22022BC 10-18-2004 49 > > I'd like the result of the sql interogation to be like this: > > 5003707G9G9419 22012BB 10-14-2004 82 > 5003707G9G9420 22022BC 10-18-2004 49 > > Explanations: > I want like sql interogation to select only one record from each "gc" group > record (who has the same code "gc" (that means that the "gc" field will be > unique key)) with the following two condition: > 1)from each "gc" group of records to select the record who has the value of > "ora" field maxim. > 2)if two ore more record have the same value of the maxim value of the "ora" > to select oly one of them Try: SELECT * FROM temp50 GROUP BY gc ORDER BY ora DESC; --miker > > Thanks! > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] sql
On Mon, 25 Oct 2004 05:44:06 -0600, Andrew J. Kopciuch <[EMAIL PROTECTED]> wrote: > On Monday 25 October 2004 05:20, Mike Rylander wrote: > > SELECT * FROM temp50 GROUP BY gc ORDER BY ora DESC; > > You can not have have expressions (columns etc.) in the SELECT list that are > either not in a GROUP BY clause, or used in an aggregate function when you > use GROUP BY in the statement. By saying SELECT *, means you would have to > GROUP BY gc, co, data, ora ... Doh! Of course. Should have had coffee before writing that ;) > > That isn't going to do what he wants. And the SQL you stated should give you > an error: > > ERROR: column "temp50.co" must appear in the GROUP BY clause or be used in an > aggregate function > > What he wants to do is use DISTINCT ON: > > SELECT DISTINCT ON (gc) gc, co, data, ora FROM temp50 ORDER BY gc, ora DESC; > > Andy > ---(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] tree structure photo gallery date quiery
p1.lft between p2.lft and p2.rgt and p1.id = 7; > id | parent |name > ++ > 1 | 0 | Root > 3 | 1 | Middleton > 7 | 3 | From The Footplate > (3 rows) > > -- Select parent and subordinates - also want to convert to view > nymr=# select p1.*, g.* from tree as p1, tree as p2, gallery g where > g.id = p1.id and p1.lft between p2.lft and p2.rgt and p2.id = 1; > id | lft | rgt | id | parent |name > +-+-+++ > 1 | 1 | 14 | 1 | 0 | Root > 2 | 2 | 9 | 2 | 1 | NYMR > 3 | 10 | 13 | 3 | 1 | Middleton > 4 | 3 | 4 | 4 | 2 | Steam Gala > 5 | 5 | 6 | 5 | 2 | Diesel Gala > 6 | 7 | 8 | 6 | 2 | From The Footplate > 7 | 11 | 12 | 7 | 3 | From The Footplate > (7 rows) > > -- use the one above to select photos - another view > nymr=# select count(pid), max(added) from photos where id in ( > nymr(# select p1.id from tree as p1, tree as p2 where p1.lft between > p2.lft and p2.rgt and p2.id = 1 > nymr(# ); > count | max > ---+ > 4 | 2004-11-10 12:12:00+00 > (1 row) > > nymr=# select count(pid), max(added) from photos where id in ( > nymr(# select p1.id from tree as p1, tree as p2 where p1.lft between > p2.lft and p2.rgt and p2.id = 2 > nymr(# ); > count | max > ---+ > 3 | 2004-11-10 12:12:00+00 > (1 row) > > nymr=# select count(pid), max(added) from photos where id in ( > nymr(# select p1.id from tree as p1, tree as p2 where p1.lft between > p2.lft and p2.rgt and p2.id = 3 > nymr(# ); > count | max > ---+ > 1 | 2004-01-01 09:12:12+00 > (1 row) > > Here is the photo_count function, photo_updates just has differnt > attribute names/types > > create function photo_count(int4) returns int4 as 'DECLARE > gallery_id alias for $1; > pcount int4; > begin > select count(pid) into pcount from photos where id in ( > select p1.id from tree as p1, tree as p2 where p1.lft between p2.lft > and p2.rgt and p2.id = gallery_id > ); > return pcount; > end' language 'plpgsql'; > > > > -- > Gary Stainburn > > This email does not contain private or confidential material as it > may be snooped on by interested government parties for unknown > and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 > > ---(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 > -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer ---(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] Recursive SETOF function
I'm feeling sausey today, so here is my (untested) attempt to translate your function. It's inline below, and you'll want to look here http://www.postgresql.org/docs/7.4/interactive/plpgsql.html for more information. On Mon, 22 Nov 2004 09:18:13 -0600, Richard Rowell <[EMAIL PROTECTED]> wrote: > I'm trying to port some TSQL to PLPGSQL. The DB has a table with a > recursive foreign key that represents a tree hierarchy. I'm trying to > re-create a TSQL function that pulls out all the ancestors of a given > node in the hierarchy. > > I'm rather new to PLSQL and I have several questions. > > 1. In TSQL, I can assign a scalar to the result of query like so: > SET @var1 = (SELECT foo FROM bar WHERE [EMAIL PROTECTED]) > > How would I do this in PLSQL? > > 2. In TSQL the "result table" can be inserted into manually. IE: > > CREATE FUNCTION foo () RETURNS @ttable TABLE( uid INTEGER) AS BEGIN > INSERT @ttable VALUES (1) > RETURN > END > > Is there a way to manually insert rows into the result table in PLSQL? > > What follows is my TSQL function if that helps give context. > > CREATE FUNCTION svp_getparentproviderids (@child_provider INTEGER) > RETURNS @provider_ids TABLE ( uid INTEGER ) > AS > BEGIN > DECLARE @cid AS INTEGER > IF (SELECT count(*) FROM providers WHERE uid [EMAIL PROTECTED]) > 0 > BEGIN > SET @cid = @child_provider > WHILE @cid IS NOT NULL > BEGIN > INSERT @provider_ids VALUES (@cid) > SET @cid = (SELECT parent_id FROM providers WHERE [EMAIL > PROTECTED]) > END > END > RETURN > END > -- This TYPE will get you a named column... easier to use SRFs with a preexisting type. CREATE TYPE svp_getparentproviderids_uid_type AS ( uid INTEGER ); CREATE FUNCTION svp_getparentproviderids (INTEGER) RETURNS SETOF svp_getparentproviderids_uid_type AS ' DECLARE child_provider ALIAS FOR $1; cid INTEGER; BEGIN SELECT count(*) FROM providers WHERE uid [EMAIL PROTECTED]) > 0 LOOP cid := child_provider IF cid IS NULL THEN EXIT; END IF; RETURN NEXT cid; SELECT INTO cid parent_id FROM providers WHERE [EMAIL PROTECTED]; END LOOP; RETURN END;' LANGUAGE 'plpgsql'; Hope that helps! > -- > > ---(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 > -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Recursive SETOF function
Forgot one line. See below On Mon, 22 Nov 2004 11:54:30 -0500, Mike Rylander <[EMAIL PROTECTED]> wrote: > I'm feeling sausey today, so here is my (untested) attempt to [snip] > > CREATE FUNCTION svp_getparentproviderids (@child_provider INTEGER) > > RETURNS @provider_ids TABLE ( uid INTEGER ) > > AS > > BEGIN > > DECLARE @cid AS INTEGER > > IF (SELECT count(*) FROM providers WHERE uid [EMAIL PROTECTED]) > 0 > > BEGIN > > SET @cid = @child_provider > > WHILE @cid IS NOT NULL > > BEGIN > > INSERT @provider_ids VALUES (@cid) > > SET @cid = (SELECT parent_id FROM providers WHERE [EMAIL > > PROTECTED]) > > END > > END > > RETURN > > END > > > > -- This TYPE will get you a named column... easier to use SRFs with a > preexisting type. > CREATE TYPE svp_getparentproviderids_uid_type AS ( uid INTEGER ); > > CREATE FUNCTION svp_getparentproviderids (INTEGER) >RETURNS SETOF svp_getparentproviderids_uid_type >AS ' > DECLARE > child_provider ALIAS FOR $1; > cid INTEGER; > BEGIN SELECT * FROM providers WHERE uid [EMAIL PROTECTED]) > 0 IF NOT FOUND RETURN; END IF; > LOOP > cid := child_provider > IF cid IS NULL THEN > EXIT; > END IF; > RETURN NEXT cid; > SELECT INTO cid parent_id FROM providers WHERE [EMAIL PROTECTED]; > END LOOP; > RETURN > END;' LANGUAGE 'plpgsql'; > > Hope that helps! > > > -- > > > > > > ---(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 > > > > > -- > Mike Rylander > [EMAIL PROTECTED] > GPLS -- PINES Development > Database Developer > -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [postgres] Re: [SQL] Recursive SETOF function
Sorry about that... try this: CREATE OR REPLACE FUNCTION svp_getparentproviderids (INTEGER) RETURNS SETOF svp_getparentproviderids_uid_type AS ' DECLARE child_provider ALIAS FOR $1; cid svp_getparentproviderids_uid_type%ROWTYPE; tmp_cid INTEGER; BEGIN SELECT INTO tmp_cid count(*) FROM providers WHERE uid =child_provider; IF tmp_cid = 0 THEN RAISE EXCEPTION ''Inexistent ID --> %'', child_provider; RETURN; END IF; cid.uid := child_provider; LOOP EXIT WHEN tmp_cid IS NULL; RETURN NEXT cid; SELECT INTO tmp_cid parent_id FROM providers WHERE uid=cid.uid; END LOOP; RETURN; END;' LANGUAGE 'plpgsql'; On Mon, 22 Nov 2004 12:51:41 -0600, Richard Rowell <[EMAIL PROTECTED]> wrote: > I have been fiddling with what you sent. I have it working mostly, save > for I keep getting syntax errors on the "RETURN NEXT cid;" line. If I > remove this line then the function works ( but returns nothing of > course). Any ideas? > > sp_demo_505=# CREATE OR REPLACE FUNCTION svp_getparentproviderids > (INTEGER) >RETURNS SETOF svp_getparentproviderids_uid_type >AS ' > DECLARE > child_provider ALIAS FOR $1; > cid INTEGER; > BEGIN > SELECT INTO cid count(*) FROM providers WHERE uid =child_provider; > IF cid = 0 THEN > RAISE EXCEPTION ''Inexistent ID --> %'', child_provider; > RETURN; > END IF; > cid := child_provider; > LOOP > EXIT WHEN cid IS NULL; > RETURN NEXT cid; > SELECT INTO cid parent_id FROM providers WHERE uid=cid; > END LOOP; > RETURN; > END;' LANGUAGE 'plpgsql'; > CREATE FUNCTION > sp_demo_505=# select * from svp_getparentproviderids(21112); > ERROR: incorrect argument to RETURN NEXT at or near "cid" > CONTEXT: compile of PL/pgSQL function "svp_getparentproviderids" near > line 13 > > -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Breadth first traversal in PLSQL (How to implement Queue?)
On Wed, 15 Dec 2004 12:54:44 -0600, Richard Rowell <[EMAIL PROTECTED]> wrote: > I have a table with a unary (recursive) relationship that represents a > hierarchy. With the gracious help of Mike Rylander I was able to port a > TSQL function that would traverse "up" the hierarchy to PL/SQL. Now I > need help porting the "down" the hierarchy function. Glad I could help! > > As implemented in TSQL I utilized a simple breadth first tree traversal. > I'm not sure how to replicate this in PL/SQL as I haven't figured out > how to implement the queue required for the breadth first algorithm. My > queue is declared "queue SETOF INTEGER" and I'm able to "SELECT INTO" > this variable. However when I try to delete the "current" value, I get > a syntax error. If I comment the delete out, I also get an error when I > try to fetch the "next" value from the front of the queue. > You probably want to use a temp table to hold the queue. Edits inline below. > Below is the function, followed by the psql output: > > CREATE OR REPLACE FUNCTION svp_getchildproviderids (INTEGER) > RETURNS SETOF INTEGER > AS ' > DECLARE > parent_provider ALIAS FOR $1; > cid INTEGER; -- Comment out the next line... -- queue SETOF INTEGER; > BEGIN -- We need to use execute to create the queue, otherwise -- the OID will be cached and the next invocation will cause -- an exception. EXECUTE ''CREATE TEMP TABLE cid_queue (id SERIAL, cid INTEGER ) WITHOUT OIDS;''; >SELECT INTO cid count(*) FROM providers WHERE uid =parent_provider; >IF cid = 0 THEN >RAISE EXCEPTION ''Inexistent ID --> %'', parent_provider; >RETURN; >END IF; >cid := parent_provider; >LOOP >EXIT WHEN cid IS NULL; >RETURN NEXT cid; -- Put the CID into the queue EXECUTE ''INSERT INTO cid_queue VALUES ((SELECT uid FROM providers WHERE parent_id = '' || quote_literal( cid ) || ''));''; -- We'll use EXECUTE to delete the current cid from the queue EXECUTE ''DELETE FROM cid_queue WHERE cid = '' || quote_literal( cid ) || '';''; -- And a short loop to grab the next one FOR cid IN EXECUTE ''SELECT cid FROM cid_queue ORDER BY id LIMIT 1;'' END LOOP; >END LOOP; >RETURN; > END;' LANGUAGE 'plpgsql'; Let me know if that works. As before, it's untested, so YMMV... :) -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Breadth first traversal in PLSQL (How to implement Queue?)
Arg! One more change below On Wed, 15 Dec 2004 21:48:57 -0500, Mike Rylander <[EMAIL PROTECTED]> wrote: > On Wed, 15 Dec 2004 12:54:44 -0600, Richard Rowell > <[EMAIL PROTECTED]> wrote: > > I have a table with a unary (recursive) relationship that represents a > > hierarchy. With the gracious help of Mike Rylander I was able to port a > > TSQL function that would traverse "up" the hierarchy to PL/SQL. Now I > > need help porting the "down" the hierarchy function. > > Glad I could help! > > > > > As implemented in TSQL I utilized a simple breadth first tree traversal. > > I'm not sure how to replicate this in PL/SQL as I haven't figured out > > how to implement the queue required for the breadth first algorithm. My > > queue is declared "queue SETOF INTEGER" and I'm able to "SELECT INTO" > > this variable. However when I try to delete the "current" value, I get > > a syntax error. If I comment the delete out, I also get an error when I > > try to fetch the "next" value from the front of the queue. > > > > You probably want to use a temp table to hold the queue. Edits inline below. > > > Below is the function, followed by the psql output: > > > > CREATE OR REPLACE FUNCTION svp_getchildproviderids (INTEGER) > > RETURNS SETOF INTEGER > > AS ' > > DECLARE > > parent_provider ALIAS FOR $1; > > cid INTEGER; > > -- Comment out the next line... > -- queue SETOF INTEGER; > > > BEGIN > > -- We need to use execute to create the queue, otherwise > -- the OID will be cached and the next invocation will cause > -- an exception. > EXECUTE ''CREATE TEMP TABLE cid_queue >(id SERIAL, cid INTEGER ) WITHOUT OIDS;''; > > >SELECT INTO cid count(*) FROM providers WHERE uid =parent_provider; > >IF cid = 0 THEN > >RAISE EXCEPTION ''Inexistent ID --> %'', parent_provider; > >RETURN; > >END IF; > >cid := parent_provider; > >LOOP > >EXIT WHEN cid IS NULL; > >RETURN NEXT cid; > > -- Put the CID into the queue > EXECUTE ''INSERT INTO cid_queue VALUES >((SELECT uid FROM providers WHERE > parent_id = '' || > quote_literal( cid ) || ''));''; > > -- We'll use EXECUTE to delete the current cid from the queue > EXECUTE ''DELETE FROM cid_queue WHERE cid = '' || > quote_literal( cid ) || '';''; > > -- And a short loop to grab the next one >FOR cid IN EXECUTE ''SELECT cid FROM cid_queue ORDER BY id LIMIT > 1;'' >END LOOP; > > >END LOOP; -- We need to drop the temp table, since this will probably be called -- more than once in a transaction. EXECUTE ''DROP TABLE cid_queue;''; > >RETURN; > > END;' LANGUAGE 'plpgsql'; > > Let me know if that works. As before, it's untested, so YMMV... :) > > -- > Mike Rylander > [EMAIL PROTECTED] > GPLS -- PINES Development > Database Developer > http://open-ils.org > -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Constraint on 2 column possible?
On Thu, 27 Jan 2005 13:44:32 +0200, Andrei Bintintan <[EMAIL PROTECTED]> wrote: > > Hi, > > I have a table: > CREATE TABLE werke1( > id SERIAL, > id_hr int4 NOT NULL, > id_wk int4 NOT NULL > ); > > CREATE TABLE contact( > id SERIAL, > type varchar(20), > ); > > > Now id_hr and id_wk are all referencing the same table contact(id). In the > contact table I have another column called type. > How can I write a constraint that checks that id_hr references contact(id) > and the contact(type='t1') > and that id_wk references contact(id) and the contact(type='t2'). If I understand what you want, you can do this with a multi-column foreign key and check constraints. CREATE TABLE werke1 ( id SERIAL, id_hr NOT NULL, hr_contact NOT NULL CHECK (hr_contact = 't1'), id_wk int4 NOT NULL, wk_contact NOT NULL CHECK (hr_contact = 't2'), CONSTRAINT werke1_hr FOREIGN KEY (id_hr,hr_contact) references contact(id,type), CONSTRAINT werke1_wk FOREIGN KEY (id_wk, wk_contact) references contact(id,type) ); This will cause the FKEY to match only contact entries that have the correct combination of id and type. Hope that helps! > > More explicit: the id_hr shows to the id from contact, and this line from > contact must have the line type='t1'. The same for id_wk just the type is > another. > > I can write: > CREATE TABLE werke1( > id SERIAL, > id_hr int4 NOT NULL references contact(id), > id_wk int4 NOT NULL references contact(id) > ); > but how do I check also the type column? > > Best regards, > Andy. -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Serial and Index
On Sun, 27 Feb 2005 12:54:52 +, Sam Adams <[EMAIL PROTECTED]> wrote: > I read at http://jamesthornton.com/postgres/FAQ/faq-english.html#4.15.1 > that when a serial is created then an index is created on the column. > However I can't seem to find this anywhere in the PoistgreSQL manual. Is > this true? Thanks. The FAQ entry is incorrect. If you make your SERIAL column the PRIMARY KEY of the table, however, a UNIQUE index will be created. -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] detaching triggers
On Sun, 27 Mar 2005 17:41:02 +0200, Enrico Weigelt <[EMAIL PROTECTED]> wrote: > > Hi folks, > > is it somehow possible to detach trigger, so the calling transaction > can return immediately, even before the trigger function has > returned. No, but see below. > The only solution I currently know is to fill somethings in a queue > table by rule and have an external daemon looking at it every second. > But this doesnt seem very optimal for me. Take a look at the LISTEN/NOTIFY interfaces in the docs. This will allow async post-transaction processing. You can set up an ALSO rule to notify when a particular type of statement has executed against your target table. http://www.postgresql.org/docs/8.0/static/sql-listen.html http://www.postgresql.org/docs/8.0/static/sql-notify.html See the bottom of: http://www.postgresql.org/docs/8.0/static/sql-createrule.html Hope that helps! -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] gone blind - can't see syntax error
On Apr 1, 2005 11:36 AM, Gary Stainburn <[EMAIL PROTECTED]> wrote: > Hi folks. > > I've been looking at this for 10 minutes and can't see what's wrong. > Anyone care to enlighten me. > > Thanks > > Gary > > [EMAIL PROTECTED] webroot]# psql -a -f new-view.sql > SELECT r.r_id, r.r_registration, r.r_chassis, r.r_vehicle, r.r_fuel, > r.r_pack_mats, r.r_delivery, > (date(r.r_delivery) - date(now())) AS r_remaining, r.r_created, > r.r_completed, r.r_salesman, > sm.r_salesman as salesman_name, > d.d_des, de.de_des, > u.u_id, u.u_userid, u.u_username, u.u_salesman, u.u_target, > t.t_id, t.t_des, > s.s_id, s.s_des, > c.c_id, c.c_des, > co.com_count, co.com_unseen > FROM requests r, ^^^ > left outer join users sm on sm.u_id = r.r_salesman, > left outer join users u on r.r_u_id = u.u_id, > left outer join request_types t on r.r_t_id = t.t_id, > left outer join request_states s on r.r_s_id = s.s_id, > left outer join dealerships d on r.r_d_id = d.d_id, > left outer join departments de on r.r_de_id = de.de_id, > left outer join customers c on r.r_c_id = c.c_id, > left outer join comment_tallies co on r.r_id = co.r_id > ORDER BY r.r_id; > psql:new-view.sql:19: ERROR: parser: parse error at or near "left" Don't put commas between your joins. -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Speed up slow select - was gone blind
Can you send the EXPLAIN ANALYZE of each? We can't really tell where the slowdown is without that. On Apr 1, 2005 12:32 PM, Gary Stainburn <[EMAIL PROTECTED]> wrote: > Hi folks. > > I've got my select working now, but I haven't received the speed > increase I'd expected. It replaced an earlier select which combined a > single explicit join with multiple froms. > > The first select is the old one, the second one is the new one (with a > new join). The new one takes 24 seconds to run while the old one took > 29. > > How can I redo the select to improve the speed, or what else can I do to > optimaise the database? > > original (ugly) > ~ > > SELECT r.r_id, r.r_registration, r.r_chassis, r.r_vehicle, r.r_fuel, > r.r_pack_mats, r.r_delivery, > (date(r.r_delivery) - date(now())) AS r_remaining, > r.r_created, r.r_completed, r.r_salesman, r.salesman_name, > d.d_des, de.de_des, > u.u_id, u.u_userid, u.u_username, u.u_salesman, u.u_target, > t.t_id, t.t_des, > s.s_id, s.s_des, > c.c_id, c.c_des, > co.com_count, co.com_unseen > FROM (SELECT r.r_id, r.r_t_id, r.r_d_id, r.r_de_id, r.r_s_id, > r.r_registration, r.r_chassis, r.r_c_id, r.r_vehicle, > r.r_fuel, r.r_pack_mats, r.r_delivery, r.r_salesman, > r.r_created, r.r_completed, r.r_u_id, > u.u_username AS salesman_name > FROM (requests r LEFT JOIN users u ON > ((r.r_salesman = u.u_id r, > users u, > request_types t, > request_states s, > dealerships d, > departments de, > customers c, > comment_tallies co > WHERE (r.r_d_id = d.d_id) AND > (r.r_s_id = s.s_id) AND > (r.r_c_id = c.c_id) AND > (r.r_t_id = t.t_id) AND > (r.r_d_id = d.d_id) AND > (r.r_de_id = de.de_id) AND > (r.r_u_id = u.u_id) AND > (r.r_id = co.r_id)) > ORDER BY r.r_id; > > new > ~~~ > SELECT r.r_id, r.r_registration, r.r_chassis, r.r_vehicle, r.r_fuel, > r.r_pack_mats, r.r_delivery, > (date(r.r_delivery) - date(now())) AS r_remaining, r.r_created, > r.r_completed, r.r_salesman, > sm.u_username as salesman_name, > d.d_des, de.de_des, > u.u_id, u.u_userid, u.u_username, u.u_salesman, u.u_target, > t.t_id, t.t_des, > s.s_id, s.s_des, > c.c_id, c.c_des, > co.com_count, co.com_unseen, > pl.pl_id, pl.pl_desc as plates > FROM requests r > left outer join users sm on sm.u_id = r.r_salesman > left outer join users u on r.r_u_id = u.u_id > left outer join request_types t on r.r_t_id = t.t_id > left outer join request_states s on r.r_s_id = s.s_id > left outer join dealerships d on r.r_d_id = d.d_id > left outer join departments de on r.r_de_id = de.de_id > left outer join customers c on r.r_c_id = c.c_id > left outer join comment_tallies co on r.r_id = co.r_id > left outer join plates pl on r.r_plates = pl.pl_id > ORDER BY r.r_id; > > -- > Gary Stainburn > > This email does not contain private or confidential material as it > may be snooped on by interested government parties for unknown > and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] ENUM like data type
On 6/21/05, MRB <[EMAIL PROTECTED]> wrote:
> Hi All,
>
> I have something in mind I'm not certain is do-able.
>
> I'm working with a lot of data from MySQL where the MySQL ENUM type is used.
>
MySQL's ENUM is basically a wrapper for CHECK. You can use a CHECK
constraint like this:
CREATE TABLE test(
testfield TEXT CHECK (testfield IN( 'Bits', 'of', 'data'))
);
> This is not a big problem per se but creating the proper lookup tables
> is becoming a bit tedious so I was hoping to make something better of it.
>
> Here is where I get uncertain as to if this is possible. My idea is to
> create a pseudo type that triggers the creation of it's lookup tables
> the same way the SERIAL type triggers creation of a sequence and returns
> an int with the right default value.
Although you can't create a generic type to handle this, you can
create a DOMAIN to wrap up your constraint for each "enum" type field
that you want:
CREATE DOMAIN fruit AS TEXT CHECK (VALUE IN ('apple','orange','banana'));
CREATE TABLE eat (
food fruit
);
http://www.postgresql.org/docs/8.0/interactive/sql-createdomain.html
Hope that helps.
--
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer
http://open-ils.org
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [DOCS] [SQL] Update timestamp on update
Is a working example something that people would like to see? Or is this considered a good use of research time? On Thursday 13 October 2005 11:20 am, Jim C. Nasby wrote: > On Wed, Oct 12, 2005 at 10:52:04PM -0400, Tom Lane wrote: > > Jeff Williams <[EMAIL PROTECTED]> writes: > > > Thanks. Triggers was my first thought, but chapter 35 on Triggers > > > didn't really indicate a way I could do this easily and scared me with > > > a lot of c code. > > > > Yeah. This is a documentation issue that's bothered me for awhile. > > The problem is that we treat the PL languages as add-ons and therefore > > the documentation of the "core" system shouldn't rely on them ... but > > that leaves us presenting C-code triggers as the only examples in > > chapter 35. There is a paragraph in there suggesting you go look at > > the PL languages first, but obviously it's not getting the job done. > > Chapter 35 is plpgsql.. do you mean chapter 32.4? > > > Anybody have a better idea? > > What about a See Also section ala man pages that links to trigger info > for other languages? -- Mike Diehl, Network Monitoring Tool Devl. SAIC at Sandia National Laboratories. (505) 284-3137 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Question about functions
I have been looking through FAQs and the docs and I cannot seem to find the answer to this question. If someone can point me to documentation I would really appreciate it. I am trying to run this function but the return is not correct. If I run the select statement from the psql command line it works. My guess is that the WHERE clause could be causing the problem. Then again, it may be how I am using the FOR loop. The ides column is of type TEXT. CREATE OR REPLACE FUNCTION sp_description_search(varchar) RETURNS varchar AS $$ DECLARE myrec record; BEGIN FOR myrec IN SELECT * FROM tblStuff WHERE ides LIKE '%$1%' LOOP RETURN NEXT myrec; END LOOP; RETURN; END; $$ LANGUAGE 'plpgsql'; Thanks so much for any insight you can give me!!! Mike
Re: [SQL] simple? query
> From: pgsql-sql-ow...@postgresql.org
[mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Jan Verheyden
> Subject: [SQL] simple? query
>
> Hi,
> I was trying to run following query but doesn't work:
> if (uid='janvleuven10') then
> insert into test (registered) values ('1');
> else
> insert into test (registered) values ('0');
> end if;
Perhaps UPDATE is what you're looking for?
http://www.postgresql.org/docs/8.4/static/sql-update.html
UPDATE test SET registered = '1' WHERE uid = 'janvleuven10';
UPDATE test set registered = '0' WHERE uid <> 'janvleuven10';
Mike
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] simple? query
> From: Relyea, Mike [mailto:mike.rel...@xerox.com]
> Sent: Thursday, August 13, 2009 10:47 PM
>
> > From: pgsql-sql-ow...@postgresql.org
> [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Jan Verheyden
> > Subject: [SQL] simple? query
> >
> > Hi,
> > I was trying to run following query but doesn't work:
> > if (uid='janvleuven10') then
> > insert into test (registered) values ('1'); else
> > insert into test (registered) values ('0'); end if;
>
> Perhaps UPDATE is what you're looking for?
> http://www.postgresql.org/docs/8.4/static/sql-update.html
>
> UPDATE test SET registered = '1' WHERE uid = 'janvleuven10';
> UPDATE test set registered = '0' WHERE uid <> 'janvleuven10';
>
>
>
>
> From: Jan Verheyden [mailto:jan.verhey...@uz.kuleuven.ac.be]
> Sent: Friday, August 14, 2009 3:11 AM
> To: Relyea, Mike
> Subject: RE: [SQL] simple? query
>
> Hi,
>
> Thanks for the reply. I was thinking of that, but the problem
> is that if it's not registered, the uid is not in the test database...
> I think in your example all the other rows will be signed as
> 'not registered, is this possible?'
>
> Regards,
>
> Jan
You're right, my suggestion will not insert new records. It will only
update existing ones. And yes, in my example all of the existing rows
where uid is not equal to janvleuven10 will have the registered value
set to 0. That's how I interpreted the example you gave in your
original post.
Mike
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] simple? query
> From: Jan Verheyden [mailto:jan.verhey...@uz.kuleuven.ac.be] > Sent: Friday, August 14, 2009 9:03 AM > To: Relyea, Mike > Subject: RE: [SQL] simple? query > > The goal is, where uid not equals to 'janvleuven10' a new > record should be inserted with the uid, and registered=0 > > Regards, > > Jan So if a record is found you want to update it and if a record isn't found you want to insert it. I think you'll probably want to use plpgsql http://www.postgresql.org/docs/8.4/static/plpgsql.html or some other language like Jasen suggested. I don't know of a way to do this with straight sql. Mike -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Aggregating results across multiple partitions
Hi I see that some queries are not Order(n) where n=number of partitions. However, if one were to run the query separately against different partitions and aggregate the results it could be Order(n). Can such an approach be implemented in a more generic manner in pgsql? Thanks Mike
[SQL] Lowest 2 items per
I need a little help putting together a query. I have the tables listed below and I need to return the lowest two consumables (ranked by cost divided by yield) per printer, per color of consumable, per type of consumable. CREATE TABLE printers ( printerid serial NOT NULL, make text NOT NULL, model text NOT NULL, CONSTRAINT printers_pkey PRIMARY KEY (make , model ), CONSTRAINT printers_printerid_key UNIQUE (printerid ), ) CREATE TABLE consumables ( consumableid serial NOT NULL, brand text NOT NULL, partnumber text NOT NULL, color text NOT NULL, type text NOT NULL, yield integer, cost double precision, CONSTRAINT consumables_pkey PRIMARY KEY (brand , partnumber ), CONSTRAINT consumables_consumableid_key UNIQUE (consumableid ) ) CREATE TABLE printersandconsumables ( printerid integer NOT NULL, consumableid integer NOT NULL, CONSTRAINT printersandconsumables_pkey PRIMARY KEY (printerid , consumableid ), CONSTRAINT printersandconsumables_consumableid_fkey FOREIGN KEY (consumableid) REFERENCES consumables (consumableid) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT printersandconsumables_printerid_fkey FOREIGN KEY (printerid) REFERENCES printers (printerid) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE ) I've pulled together this query which gives me the lowest consumable per printer per color per type, but I need the lowest two not just the first lowest. SELECT printers.make, printers.model, consumables.color, consumables.type, min(cost/yield) AS cpp FROM printers JOIN printersandconsumables ON printers.printerid = printersandconsumables.printerid JOIN consumables ON consumables.consumableid = printersandconsumables.consumableid WHERE consumables.cost Is Not Null AND consumables.yield Is Not Null GROUP BY printers.make, printers.model, consumables.color, consumables.type ORDER BY make, model; After doing a google search I didn't come up with anything that I was able to use so I'm asking you fine folks! Mike -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Lowest 2 items per
> -Original Message- > From: David Johnston [mailto:pol...@yahoo.com] > Sent: Friday, June 01, 2012 11:13 AM > To: Relyea, Mike > Cc: > Subject: Re: [SQL] Lowest 2 items per > > > I would recommend using the "RANK" window function with an appropriate > partition clause in a sub-query then in the outer query you simply WHERE > rank <= 2 > > You will need to decide how to deal with ties. > > David J. David, I've never used window functions before and rank looks like it'd do the job quite nicely. Unfortunately I'm using 8.3 - which I should have mentioned in my original request but didn't. Window functions weren't introduced until 8.4 from what I can tell. Mike -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Lowest 2 items per
> -Original Message- > From: Oliveiros d'Azevedo Cristina [mailto:oliveiros.crist...@marktest.pt] > Sent: Friday, June 01, 2012 11:21 AM > To: Oliveiros d'Azevedo Cristina; Relyea, Mike; pgsql-sql@postgresql.org > Subject: Re: [SQL] Lowest 2 items per > > Sorry, Mike, previous query was flawed. > > This is (hopefully) the correct version > > Best, > Oliver > > SELECT make, model, color,type, subquery1.cpp, min(cost/yield) as cpp2 ( > SELECT printers.make, printers.model, consumables.color, > consumables.type, min(cost/yield) AS cpp FROM printers JOIN > printersandconsumables ON printers.printerid = > printersandconsumables.printerid JOIN consumables ON > consumables.consumableid = printersandconsumables.consumableid > WHERE consumables.cost Is Not Null > AND consumables.yield Is Not Null > GROUP BY printers.make, printers.model, consumables.color, > consumables.type > ) subquery1 > JOIN > ( > SELECT printers.make, printers.model, consumables.color, > consumables.type,cost,yield FROM printers JOIN printersandconsumables > ON printers.printerid = printersandconsumables.printerid JOIN > consumables ON consumables.consumableid = > printersandconsumables.consumableid > WHERE consumables.cost Is Not Null > AND consumables.yield Is Not Null > ) subquery2 > ON (subquery1.make = subquery2.make > AND subquery1.model = subquery2.model > AND subquery1.color = subquery2.color > AND subquery1.type = subquery2.type) > WHERE subquery2.cost / subquery2.yield <> subquery1.cpp GROUP BY > subquery2.make,subquery2. model, > subquery2.color,subquery2.type,subquery1.cpp > ORDER BY make, model; > Oliver, I had to make a few grammatical corrections on your query to get it to run, but once I did it gave me almost correct results. It leaves out all of the printer models that only have one consumable with a cost. Some printers might have more than two black inks and some might have only one. Your query only returns those printers that have two or more. Here's your query with the corrections I had to make SELECT subquery2.Make, subquery2.Model, subquery2.Color,subquery2.Type, subquery1.cpp, min(Cost/Yield) as cpp2 FROM( SELECT Printers.Make, Printers.Model, Consumables.Color, Consumables.Type, min(Cost/Yield) AS cpp FROM Printers JOIN PrintersAndConsumables ON Printers.PrinterID = PrintersAndConsumables.PrinterID JOIN Consumables ON Consumables.ConsumableID = PrintersAndConsumables.ConsumableID WHERE Consumables.Cost Is Not Null AND Consumables.Yield Is Not Null GROUP BY Printers.Make, Printers.Model, Consumables.Color, Consumables.Type ) subquery1 JOIN ( SELECT Printers.Make, Printers.Model, Consumables.Color, Consumables.Type,Cost,Yield FROM Printers JOIN PrintersAndConsumables ON Printers.PrinterID = PrintersAndConsumables.PrinterID JOIN Consumables ON Consumables.ConsumableID = PrintersAndConsumables.ConsumableID WHERE Consumables.Cost Is Not Null AND Consumables.Yield Is Not Null ) subquery2 ON (subquery1.Make = subquery2.Make AND subquery1.Model = subquery2.Model AND subquery1.Color = subquery2.Color AND subquery1.Type = subquery2.Type) WHERE subquery2.Cost / subquery2.Yield <> subquery1.cpp GROUP BY subquery2.Make,subquery2.Model, subquery2.Color,subquery2.Type,subquery1.cpp ORDER BY Make, Model; Mike -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Lowest 2 items per
> -Original Message- > From: Oliveiros d'Azevedo Cristina [mailto:oliveiros.crist...@marktest.pt] > Sent: Friday, June 01, 2012 12:28 PM > To: Relyea, Mike > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] Lowest 2 items per > > Yes, you are right, now, thinking about the way I built it, the query, indeed, > leaves out the corner case of models which have just one consumable. > > I didn't try ur version of the query. > Does itork now with your improvements ? > Or were they only gramatical ? > > Best, > Oliver I only made grammatical changes necessary for the query to function (adding a missing FROM, fully qualifying "SELECT Make" as " SELECT subquery2.Make", etc.) I tried changing the join type to right and left but that did not have the desired result. Mike -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Lowest 2 items per
> -Original Message- > From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql- > ow...@postgresql.org] On Behalf Of Mario Dankoor > Sent: Friday, June 01, 2012 2:31 PM > To: Relyea, Mike > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] Lowest 2 items per > Mike, > > try following query it's a variation on a top N ( = 3) query SELECT FRS.* FROM > ( >SELECT PRN.make > ,PRN.model > ,CSM.color > ,CSM.type > ,cost/yield rank >FROM consumableCSM > ,printers PRN > ,printersandconsumable PCM >WHERE 1 = 1 >AND PCM.printerid= PRN.printerid >AND PCM.consumableid = CSM.consumableid >group by PRN.make > ,PRN.model > ,CSM.color > ,CSM.type >) FRS > WHERE 3 > ( > SELECT COUNT(*) > FROM ( > SELECT PRN.make >,PRN.model >,CSM.color >,CSM.type >,cost/yield rank > FROM consumableCSM > ,printers PRN > ,printersandconsumable PCM > WHERE 1 = 1 > AND PCM.printerid= PRN.printerid > AND PCM.consumableid = CSM.consumableid > group by PRN.make > ,PRN.model > ,CSM.color > ,CSM.type >) NXT > WHERE 1 = 1 > AND NXT.make = FRS.make > AND NXT.model= FRS.model > AND NXT.color= FRS.color > AND NXT.type = FRS.type > AND NXT.cost <= FRS.cost >) Mario, This works quite nicely! I had to add a few criteria to it and the results it gives does have some ties that I need to figure out how to break - but that'll be easy because if there is a tie then I don't care which one wins. Here's the working query that I am going to modify a little bit more. SELECT FRS.* FROM ( SELECT PRN.Make ,PRN.Model ,CSM.Color ,CSM.Type ,CSM.PartNumber ,Cost/Yield as rank FROM ConsumablesCSM ,Printers PRN ,PrintersAndConsumables PCM WHERE 1 = 1 AND PCM.PrinterID= PRN.PrinterID AND PCM.ConsumableID = CSM.ConsumableID group by PRN.Make ,PRN.Model ,CSM.Color ,CSM.Type ,CSM.Cost ,CSM.Yield ,CSM.PartNumber ) FRS WHERE 3 > ( SELECT COUNT(*) FROM ( SELECT PRN.Make ,PRN.Model ,CSM.Color ,CSM.Type ,Cost/Yield as rank FROM ConsumablesCSM ,Printers PRN ,PrintersAndConsumables PCM WHERE 1 = 1 AND PCM.PrinterID= PRN.PrinterID AND PCM.ConsumableID = CSM.ConsumableID group by PRN.Make ,PRN.Model ,CSM.Color ,CSM.Type ,CSM.Cost ,CSM.Yield ) NXT WHERE 1 = 1 AND NXT.Make = FRS.Make AND NXT.Model= FRS.Model AND NXT.Color= FRS.Color AND NXT.Type = FRS.Type AND NXT.rank <= FRS.rank ) AND rank IS NOT NULL ORDER BY Make, Model, Color, Type; Thanks for the help! Mike -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Lowest 2 items per
> -Original Message- > From: Oliveiros d'Azevedo Cristina [mailto:oliveiros.crist...@marktest.pt] > Sent: Friday, June 01, 2012 12:59 PM > To: Relyea, Mike > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] Lowest 2 items per > > * I see... > > If we add a query with a union that selects only the single ink printers. > > Something like > > SELECT subquery2.Make, subquery2.Model, > subquery2.Color,subquery2.Type, subquery1.cpp, min(Cost/Yield) as cpp2 > FROM( SELECT Printers.Make, Printers.Model, Consumables.Color, > Consumables.Type, min(Cost/Yield) AS cpp FROM Printers JOIN > PrintersAndConsumables ON Printers.PrinterID = > PrintersAndConsumables.PrinterID JOIN Consumables ON > Consumables.ConsumableID = PrintersAndConsumables.ConsumableID > WHERE Consumables.Cost Is Not Null > AND Consumables.Yield Is Not Null > GROUP BY Printers.Make, Printers.Model, Consumables.Color, > Consumables.Type > ) subquery1 > JOIN > ( > SELECT Printers.Make, Printers.Model, Consumables.Color, > Consumables.Type,Cost,Yield FROM Printers JOIN PrintersAndConsumables > ON Printers.PrinterID = PrintersAndConsumables.PrinterID JOIN > Consumables ON Consumables.ConsumableID = > PrintersAndConsumables.ConsumableID > WHERE Consumables.Cost Is Not Null > AND Consumables.Yield Is Not Null > ) subquery2 > ON (subquery1.Make = subquery2.Make > AND subquery1.Model = subquery2.Model > AND subquery1.Color = subquery2.Color > AND subquery1.Type = subquery2.Type) > WHERE subquery2.Cost / subquery2.Yield <> subquery1.cpp GROUP BY > subquery2.Make,subquery2.Model, > subquery2.Color,subquery2.Type,subquery1.cpp > UNION > SELECT Printers.Make, Printers.Model, Consumables.Color, > Consumables.Type, min(Cost/Yield) AS cpp,min(Cost/Yield) AS cpp2 > FROM Printers JOIN > PrintersAndConsumables ON Printers.PrinterID = > PrintersAndConsumables.PrinterID JOIN Consumables ON > Consumables.ConsumableID = PrintersAndConsumables.ConsumableID > WHERE Consumables.Cost Is Not Null > AND Consumables.Yield Is Not Null > GROUP BY Printers.Make, Printers.Model, Consumables.Color, > Consumables.Type HAVING COUNT(*)=1 ORDER BY Make, Model; > > Can this be the results we're after > ? > > Best, > Oliver > Oliver, Thanks for your help. You gave me a workable query. I made a few minor changes to your idea but I really like the solution offered by Mario. It provides more flexibility and is cleaner. For example, with Mario's I can take the lowest 3 easily instead of just the lowest 2. Mike -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Summing & Grouping in a Hierarchical Structure
> From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] > On Behalf Of Don Parris > Sent: Thursday, February 14, 2013 8:58 PM > To: pgsql-sql@postgresql.org > Subject: [SQL] Summing & Grouping in a Hierarchical Structure > > Hi all, > I posted to this list some time ago about working with a hierarchical > category structure. I had great difficulty with my problem and gave up for > a time. > I recently returned to it and resolved a big part of it. I have one step > left to go, but at least I have solved this part. > > Here is the original thread (or one of them): > http://www.postgresql.org/message-id/CAJ-7yonw4_qDCp-ZNYwEkR2jdLKeL8nfGc+-TLLSW=rmo1v...@mail.gmail.com > > > Here is my recent blog post about how I managed to show my expenses summed > and grouped by a mid-level category: > http://dcparris.net/2013/02/13/hierarchical-categories-rdbms/ > > > Specifically, I wanted to sum and group expenses according to categories, not > just at the bottom tier, but at higher tiers, so as to show more summarized > information. > A CEO primarily wants to know the sum total for all the business units, yet > have the ability to drill down to more detailed levels if something is > unusually high or low. > In my case, I could see the details, but not the summary. Well now I can > summarize by what I refer to as the 2nd-level categories. > Anyway, I hope this helps someone, as I have come to appreciate - and I mean > really appreciate - the challenge of working with hierarchical structures in > a 2-dimensional RDBMS. > If anyone sees something I should explain better or in more depth, please let > me know. > > Regards, > Don > -- > D.C. Parris, FMP, Linux+, ESL Certificate > Minister, Security/FM Coordinator, Free Software Advocate > http://dcparris.net/ > GPG Key ID: F5E179BE My two cents would be to actually use a different tool for the job of presenting this data. I'd have used a pivot table in Microsoft Excel. Not sure what your environment or requirements are but pivot tables are widely used in business, easy to share, can be formatted, and give the user the ability to drill down and navigate to the data they want to see. I'd set up a query to pull the raw data you need with all of the categories and associated data you need. Then bring that data to Excel to present and summarize it. Mike -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Problem with phone list.
Hi all. I've qot a problem I need to solve. I'm sure it's pretty simple; I just can't seem to get it, so here goes... I've got a table, actually a view that joins 3 tables, that contains a phone number, a unique id, and a call duration. The phone number has duplicates in it but the unique id is unique. I need to get a list of distinct phone numbers and the coorisponding largest call duration. I've got the idea that this should be a self-join on phone number where a.id<>b.id, but I just can't seem to get the max duration. Any hints would be much appreciated. Mike. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Problem with phone list.
Yup, that did it. I don't know why I made it harder than it had to be. Thank you. Mike. On Wednesday 15 August 2007 02:58:22 pm Fernando Hevia wrote: > Try this: > > Select * > from view v1 > where duration = (select max(duration) from view v2 where v2.phone_number = > v1.phone_number) > > You could get more than one call listed for the same number if many calls > match max(duration) for that number. > > > -Mensaje original- > De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > En nombre de Mike Diehl > Enviado el: Miércoles, 15 de Agosto de 2007 17:28 > Para: SQL Postgresql List > Asunto: [SQL] Problem with phone list. > > Hi all. > > I've qot a problem I need to solve. I'm sure it's pretty simple; I just > can't > seem to get it, so here goes... > > I've got a table, actually a view that joins 3 tables, that contains a > phone > > number, a unique id, and a call duration. > > The phone number has duplicates in it but the unique id is unique. > > I need to get a list of distinct phone numbers and the coorisponding > largest > > call duration. > > I've got the idea that this should be a self-join on phone number where > a.id<>b.id, but I just can't seem to get the max duration. > > Any hints would be much appreciated. > > Mike. > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org -- Mike Diehl ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Refactored queries needing validation of syntactic equivalence
Hello! I'm a long time lurker who has become responsible for maintaining / updating utility queries at work. I've reworked two queries (as text attachment as they are wide lines) to enhance the planner's chance of speeding up the queries (Oracle8i's). I'm looking for someone to eyeball them and let me know if I've folded the sub-selects up correctly (I'm the ONLY sql speaking person at work so having a coworker do so is unfortunately not possible). Also unfortunately, there currently aren't any issues in the database that these queries are designed to find. All I can say for sure is (as you can see below each query) my refactored queries *at the least* return *no* data faster than the legacy queries... Thank you in advance and I wish the application at work used postgresql as it's backend! Michael Adams legacy| refactor -+ select m.co_cd, | select m.co_cd, m.ve_cd, | m.ve_cd, m.ivc_cd,| m.ivc_cd, m.po_cd, | m.po_cd, m.itm_cd,| m.itm_cd, m.qty, | m.qty, m.unit_cst, | m.unit_cst, (m.qty*m.unit_cst) as ext_cst, | (m.qty*m.unit_cst) as ext_cst, to_char(m.rcv_dt,'-MM-DD') as received, | to_char(m.rcv_dt,'-MM-DD') as received, origin_cd, | m.origin_cd, to_char(m.assigned_dt,'-MM-DD') as assigned | to_char(m.assigned_dt,'-MM-DD') as assigned from rcv_mo m | from rcv_mo m, rcv_mo r where ( m.origin_cd= 'MOM' ) | where ( m.origin_cd= 'MOM' ) and ( m.ASSIGNED_DT <= '31-Oct-2007' |and ( m.ASSIGNED_DT <= '31-Oct-2007' or | or m.ASSIGNED_DT is null | m.ASSIGNED_DT is null )|) and ( exists ( select 1 |and ( m.po_cd = r.po_cd ) from rcv_mo o |and ( m.itm_cd= r.itm_cd ) where ( m.po_cd= o.po_cd ) |and ( r.assigned_dt is null ) and ( m.itm_cd = o.itm_cd ) |and ( r.rcv_dt <= '31-Oct-2007') and ( o.assigned_dt is null ) | order by m.VE_CD, m.po_cd, m.itm_cd and ( o.rcv_dt <= '31-Oct-2007') | ) | -- 0 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms] )| -- [Executed: 10/10/07 9:24:09 AM CDT ] [Execution: 937/ms] order by m.VE_CD, m.po_cd, m.itm_cd | | -- 0 record(s) selected [Fetch MetaData: 16/ms] [Fetch Data: 0/ms] | -- [Executed: 10/10/07 8:47:39 AM CDT ] [Execution: 2054333/ms] | | -+ -+ select o.co_cd, | select o.co_cd, o.ve_cd, | o.ve_cd, o.ivc_cd,| o.ivc_cd, o.po_cd, | o.po_cd, o.itm_cd,| o.itm_cd, o.qty, | o.qty, o.unit_cst, | o.unit_cst, (o.qty*o.unit_cst) as ext_cst, | (o.qty*o.unit_cst) as ext_cst, to_ch
Re: [SQL] Refactored queries needing validation of syntactic equivalence
Richard Huxton wrote: (quoted OP lines edited for brevity...) Mike Adams wrote: ... I've reworked two queries (as text attachment as they are wide lines) to enhance the planner's chance of speeding up the queries (Oracle8i's). Well, I can't say it's standard procedure to look at Oracle queries, but if you don't tell anyone I won't :-) No prob, my lips are sealed... ;-) It's basically generic sql sanity checking that's needed anyhow. I'm looking for someone to eyeball them and let me know if I've folded the sub-selects up correctly ... Also unfortunately, there currently aren't any issues in the database that these queries are designed to find. All I can say for sure is (as you can see below each query) my refactored queries *at the least* return *no* data faster than the legacy queries... Test data time then. No alternative to testing these things. I do plan to run the old and the new until I'm sure the new queries aren't borked and return the same set of info. Thank you in advance ... OK, you've substituted and EXISTS check against a sub-query with a self-join. The key question of course is whether your join can return more than one match from "rcv_mo m" for each row in "rcv_mo o". I can't say without knowing more about your schema, and even then I'd want to test it. Thanks for the response! The schema is basically: table rcv_mo( CO_CD VCHR(3), --COMPANY CODE. VE_CD NOT NULL VCHR(4), --VENDOR CODE. IVC_CDVCHR(20), --INVOICE CODE. PO_CD NOT NULL VCHR(13), --PURCHASE ORDER CODE. ITM_CDNOT NULL VCHR(9), --ITEM CODE. QTY NUM(13,2), --QUANTITY. UNIT_CST NUM(13,2), --UNIT COST. RCV_DTDATE, --RECEIVED DATE. ORIGIN_CD NOT NULL VCHR(5), --CODE REPRESENTING THE PROGRAM WHICH --CREATED THE RCV_MO RECORD. STORE_CD VCHR(2), --RECEIVING STORE CODE. WAYBILL_NUM VCHR(20), --WAYBILL NUMBER FROM RECEIVING BOL ASSIGNED_DT DATE, --ASSIGNED DATE IS THE SYSTEM DATE WHEN --THE INVOICE AND THE RECEIVINGS ARE --LINKED TOGETHER. TMP_ADJ_ROWID VCHR(40), --THIS FIELD WAS CREATED TO BE USED FOR --SPECIAL PROCESSING DONE IN MPOI. UPON --COMMITTING, THE TMP_ADJ_ROWID WILL --ALWAYS BE NULL. RCVR_ID VCHR(15), --ID OF THE PERSON RECEIVING THE ORDER. EMP_CDVCHR(15), --ID OF THE LAST PERSON TO POST A --CHANGE TO RCV_MO. ); indexes: NONUNIQE (CO_CD, VE_CD, IVC_CD, PO_CD, ITM_CD); NONUNIQE (VE_CD, PO_CD); Notice the date columns aren't indexed! If they were, even the original queries would be *much* faster! Unfortunately I cannot get indexes implemented (not w/o more aggravation than the wait for the original queries provides). Here's the "process": inventory mgmt system inserts tuples when/as product arrives. If more than one of an item (itm_cd) is in the same batch, it may, or may not, (it's very POM dependent ;) ) be split into multiple tuples. Then the accounting dpt enters the product invoice into the "Match Off Management" system and assigns received product to the appropriate vendor invoice. Occasionally, the receiving dpt may post oh say 48 of an item in one table entry, however, the acctng dpt has 2 invoices for 24 items each. In MOM the acctng dpt /manually/ splits the entry, thus inserting 2 records who's origin is 'MOM', each for 24 items, and assigns them to the invoice(s) (or maybe just assigns one since they've not yet rec'd an invoice for the other 24). So, we can have *many* 'MOM' records. They are /supposed/ to let me know so I can immediately assign the original record to a "fake" invoice called "SPLIT IN MOM" and it drops off the radar. So of course, I'm rarely notified. This table is used to accrue for received but unpaid merchandise: "orphaned" entries inflate the accrual and inflate the value of inventory (not good). So. The first query should pull all 'MOM' records that have one or more corresponding, and possibly orphaned, unassigned receiving records belonging to the same po_cd and item_cd. The second query should pull all unassigned, and possibly orphaned receiving records that have one or more corresponding 'MOM' records once again matching on po_cd and item_cd. Using the results of both queries to double check each other, I can figure out which (if any) open records are, in fact, orphans and do an "after the fact" assignment to the "SPLIT IN MOM" invoice to reduce our accrual. Of course,
Re: [SQL] Refactored queries needing validation of syntactic equivalence
Richard Huxton wrote: Mike Adams wrote: So. The first query should pull all 'MOM' records that have one or more corresponding, and possibly orphaned, unassigned receiving records belonging to the same po_cd and item_cd. The second query should pull all unassigned, and possibly orphaned receiving records that have one or more corresponding 'MOM' records once again matching on po_cd and item_cd. Using the results of both queries to double check each other, I can figure out which (if any) open records are, in fact, orphans and do an "after the fact" assignment to the "SPLIT IN MOM" invoice to reduce our accrual. Of course, our ERMS should take care of this automagically; but, tragically, it seems "real" inventory cost flow was attached to the system using duct tape, hot glue, and a couple of thumb tacks. So, given all the administriva above, have I actually refactored them correctly? Well, clearly you could have multiple possible matches, because apart from anything else you could in theory have multiple entries with the same item-code on the same purchase-order-code. In practice it will be rare, but it could happen. Yep! and it's not rare: if we receive 20 serialized items, we *will* get 20 entries of same "itm_cd,po_cd" as serialized items are individually posted in inventory (in accounting speak, they have a "specific item" costing basis, whereas "non serialized" items (parts etc) are (by us) considered to have a "FIFO" costing basis and can be lumped into "lots"). Yesterday I ran both the "legacy" and "refactor" versions of each query after the AP clerk (for once) let me know that her assistant had "joined" a number of receivings (did the reverse of a split for some reason). The "orphans" query (select o.co_cd, ...) came back with the same result set for both the legacy and refactor versions. The "moms" query (select m.co_cd, ...) did not! What I had for the "moms" result sets were (fake products replacing the real ones in the results below): legacy | refactor + 2 hotplate | 2 hotplate 6 scooper | 2 hotplate | 6 scooper | 6 scooper | 6 scooper | 6 scooper | 6 scooper | 6 scooper The "orphans" result sets were the same (faked products in results below): result set - 1 hotplate 1 hotplate 1 scooper 1 scooper 1 scooper 1 scooper 1 scooper 1 scooper In truth those eight records returned by both "orphans" versions *were* actually orphaned by the *2* "moms" records that /do/ exist and were correctly reported by the legacy version... Oops! the refactored "moms" query is an unintentional (by me) cross product! However, since the purpose is to provide you with a list so you can make manual changes there's no problem with that. Except for the unwanted cross productions! Well, there isn't an available "natural" way to prevent that as the table /doesn't/ have a pkey or even a good candidate key. What I did, and it did fix the result set to reflect reality, was change the select o.co_cd, ... from ... to select distinct o.co_cd, ..., o.rowid from ... rowid being Oracle's version of ctid and is the only "unique" item "in" the table ( oh the shame ). What I might be tempted to do is restrict the dates more - you had <= '31 Oct 2007' I'd also apply >= '1 Aug 2007' (or whatever period is reasonable). You can always run an unconstrained match once a month to catch any that slip through the net, but presumably most will fall within a 90-day period. HTH I may restrict the dates more, however the refactored queries both run in under 1000 ms, and given the rcv_mo table currently has >5 && <7 years worth of historical data for them to plow through, and the plan is to only keep the data in the table for 7 yrs max... Thank you for the help! I've appreciated it greatly! Mike. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] Case Insensitive searches
In the application that we are working on, all data searches must be case insensitive. Select * from test where column1 = 'a' and Select * from test where column1 = 'A' should always be the same and use the index if column1 is indexed. In order to do this am I going to be required to use the lower() on all selects in order to make sure that they are case insensitive? In some db's if you use a lower() or upr() it will always do a table scan instead of using a index Best Regards, Michael Gould, Manager Information Technology All Coast Intermodal Services, Inc. First Coast Intermodal Services, Inc. First Coast Logistical Services, LLC. 904-226-0978
Re: [SQL] order function in aggregate
Richard Huxton wrote:
Michael Toews wrote:
You could accumulate the values in an array and then sort that with
the final-func that create aggregate supports.
Thanks for the help. Here was my final functions to aggregate things
into a comma serpented text element (if anyone is interested):
CREATE OR REPLACE FUNCTION commacat_fn(anyarray)
RETURNS text AS
$BODY$select array_to_string(sort($1),', ');$BODY$
LANGUAGE 'sql' IMMUTABLE STRICT
COST 100;
ALTER FUNCTION commacat_fn(anyarray) OWNER TO postgres;
CREATE AGGREGATE commacat(anyelement) (
SFUNC=array_append,
STYPE=anyarray,
FINALFUNC=commacat_fn,
INITCOND='{}'
);
---
Lastly a random quick example:
select attrelid, commacat(attname) as attnames from pg_attribute group
by attrelid order by attrelid;
Certainly there are far better examples that can be used to distill
information in a table to a comma-separated list.
In some specific applications, a "sort(myarraytype[])" function will
need to be created if the data type in the aggregate column does not
have a sort function (fortunately, most data types already have this
function defined).
Regards,
+mt
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Multi-line text fields
Hi all, I have some records that have some multiple lines within a single text field. (On top of that, I think I have mixed DOS/UNIX line endings too). I'm looking for two functions which can operate on a single field: 1. number of lines 2. a way to select the first line or to trim to the first line only (the normal trim function doesn't appear to do this) Thanks in advance. -Mike -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] sequence number in a result
> Is there a function or special system label I can use that would
generate a sequence number in the returning result set?
Would something like this work for you?
CREATE TEMP SEQUENCE foo;
SELECT a, b, c, nextval('foo') AS order FROM t1 ORDER BY a;
Mike Relyea
Product Development Engineer
Xerox Corporation
Building 218 Room 107
800 Phillips Rd
Webster, NY 14580
p 585.265.7321
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Re: [GENERAL] TOP SESSIONS?
mikeo wrote: > > hi, > in oracle you would use these two cursors to determine who was connected and > what they were doing. > > select distinct s.sid sid, s.serial# serial, s.status status, osuser, spid , > count(o.sid) counter, s.username username, s.program program, sql_address > from v$session s, v$open_cursor o, v$process p > where s.sid = o.sid(+) > and paddr = addr > group by s.sid,s.serial#, s.status , osuser, spid ,s.username, s.program ,sql_address > order by 1,3 > > select distinct name > from sys.audit_actions, v$sqltext > where address = ? > and action = command_type > > does anyone know what tables in postgres would give me the same or similar >information? > > TIA, > mikeo PostgreSQL attempts to communicate what queries are being performed by setting the process information in the connected backend when processing a SQL statement, much like sendmail. You should be able to determine who's connected and what they're doing with something like: ps axf You'll see who's connected to what database from what machine and the type of query being executed. I don't know of any tables in PostgreSQL which would provide similar information. Hope that helps, Mike Mascari
