Re: [SQL] Is it possible in PostgreSQL?
Moving thread over to SQL list as it belongs
there.
Bronx: This certainly is possible, but IMO,
not in one query. Actually doing it will be relatively complex. For
purposes of maintenance, I am thinking that doing this would be better handled
by wrapping at least one view.
CREATE VIEW sales_pre_proc AS
SELECT name, quantity, to_char("date", '') AS
year, to_char("date", 'MM') FROM sales;
This is needed for the group by statement
below to function properly:
CREATE VIEW sales_month_summary AS
SELECT name, sum(quantity) AS quantity, year, month
from sales_pre_proc
GROUP BY name, year, month;
This will give you a view that will have the
sum information. Now we just have to create the statement which will
create the pivot effect. I understand that there is something under
contrib/tablefunc for this, but I do not have it on my system (cygwin), at
the moment. Perhaps someone else can help.
Failing that, you can write your own function to
return each row. I was working on a quick proof of concept but it was not
working properly.
Best Wishes,
Chris Travers
- Original Message -
From:
Bronx
To: [EMAIL PROTECTED]
Sent: Tuesday, January 13, 2004 6:58
AM
Subject: [ADMIN] Is it possible in
PostgreSQL?
Hi,
I've got problem with one specific query.
I've got the table
with many of rekords like
these:
name
| quantity | date
---
aaa
2
2003-04-01
bbb
4
2003-04-12
ccc
5
2003-05-12
aaa
3
2003-01-14
aaa
1
2003-12-09
bbb
9
2003-08-08
and so on ...
Does
anybody know how make query which return grouped
records by month of year and name
(also sum of quantity).
It is possible to make a query whitch return
something like that:
name | 01 | 02 | 03 | 04 | ... | 12
(months)
aaa
x x x
x ... x
bbb
x x x
x ... x
ccc
x x x
x ... x
where x means sum of quantity in month.
It is possible to make it in one query?
I know that in Access is construction : PIVOT.
Thanks
Adam
[SQL] Problem with LEFT JOIN
Hello, whats wrong with this SQL?: SELECT L.*, A."lastname" AS firma_value, T."string_val1" AS type_value FROM "lists" L, "typecode" T LEFT JOIN "adressen" A ON A."id_adressen"=L."firma" WHERE T."id_typecode"=L."lists_type" ORDER BY L."id_lists" I get this: ERROR: relation "l" does not exist This version work, but i need a LEFT JOIN (L."firma" can have NULL): SELECT L.*, A."lastname" AS firma_value, T."string_val1" AS type_value FROM "lists" L, "typecode" T, "adressen" A WHERE T."id_typecode"=L."lists_type" AND A."id_adressen"=L."firma" ORDER BY L."id_lists" -- Thomas Wegner ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Left joins with multiple tables
Hi, all. I've got a bit of a problem here. I have 4 tables - people, a, b, c (not the original names). For each person in the people table, they may or may not have a record in a, may or may not have a record in b, and may or may not have a record in c. Handling the first table (a) is easy: select id, name from people p left outer join a on a.person_id = p id; But I'd like to be able to do something like: select id, name, a.field1, b.field2, c.field3 from people p left outer join a on a.person_id = p id, people p left outer join b on b.person_id = p.id, people p left outer join c on c.person_id = p.id; Naturally you can't repeat the 'people p' clause 3 times, but is there some other syntax that would let me do this? Thanks! cf ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Triggers
I try pgmail and that is well running ... fo security, do not use attachment files with your mail ... try pgmail, you need to use pl/tclu ... Ben Sai Hertz And Control Systems wrote: Dear Uzo , Hi, does postgresql support the ability to email as in SQL Server? I want to create a trigger which on input of a record will send out an email. Is this possible? http://pgmail.sourceforge.net/ is what you need. Regards, Vishal Kashyap ---(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 8: explain analyze is your friend
Re: [SQL] Transpose rows to columns
El Lun 12 Ene 2004 22:12, David Witham escribió: >DW: Hi, >DW: >DW: I have a query that returns data like this: >DW: >DW: cust_idcust_name month costrevenue margin >DW: 991234 ABC 2003-07-01 10 15 5 >DW: 991234 ABC 2003-08-01 11 17 6 >DW: 991234 ABC 2003-09-01 12 19 7 >DW: 991235 XYZ 2003-07-01 13 21 8 >DW: 991235 XYZ 2003-08-01 12 19 7 >DW: 991235 XYZ 2003-09-01 11 17 6 >DW: >DW: I want to turn it around so it displays like this: >DW: >DW: 991234,ABC,2003-07-01,10,15,5,2003-08-01,11,17,6,2003-09-01,12,19,7 >DW: 991235,XYZ,2003-07-01,13,21,8,2003-08-01,12,19,7,2003-09-01,11,17,6 Hi, the following query select cust_id || ', ' || cust_name || ', ' || list(month::text || ', ' || cost || ', ' || revenue || ', ' || margin) as result from tmp122 group by cust_id, cust_name; *DISPLAYS* data like this: result -- 991234, ABC, 2003-07-01, 10, 15, 5, 2003-08-01, 11, 17, 6, 2003-09-01, 12, 19, 7 991235, XYZ, 2003-07-01, 13, 21, 8, 2003-08-01, 12, 19, 7, 2003-09-01, 11, 17, 6 (2 rows) -- Original data for test -- drop table tmp122; create temp table tmp122 ( cust_id integer, cust_name varchar, month date, costinteger, revenue integer, margin integer ); copy tmp122 from stdin; 991234 ABC 2003-07-01 10 15 5 991234 ABC 2003-08-01 11 17 6 991234 ABC 2003-09-01 12 19 7 991235 XYZ 2003-07-01 13 21 8 991235 XYZ 2003-08-01 12 19 7 991235 XYZ 2003-09-01 11 17 6 \. -- Chau, Luis Carlos Ferreira ---(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
[SQL] Unique field key or several fks ?
Hi, I would like to know opinions about which approach is better: Having a table with a field that works as a unique key, or having several fks that work as a combined key ( all the fks fields )? Thanks in advance, K. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] problem with function trigger
Hi I'm trying to update a table column with a pl/pgsql function and a trigger. But I didn't managed to make it work so far. Here's my function code : CREATE FUNCTION public.calcul_impact() RETURNS opaque AS ' DECLARE id_line integer; quantity integer; single_price real; total_cost real; amort integer; month integer; impact real; BEGIN SELECT INTO id_line id_line_table FROM table WHERE id_line_table = NEW.id_line; SELECT INTO single_price single_price_previ FROM table WHERE id_line_table = NEW.id_line; SELECT INTO total_cost total_cost_previ FROM table WHERE id_line_table = NEW.id_line; SELECT INTO quantity quantity_previ FROM table WHERE id_line_table = NEW.id_line; SELECT INTO amort amortis FROM table WHERE id_line_table = NEW.id_line; SELECT INTO month month_previ FROM table WHERE id_line_table = NEW.id_line; SELECT INTO impact impact_previ FROM table WHERE id_line_table = NEW.id_line; IF(quantity IS NULL OR single_price IS NULL) THEN impact:= 0; ELSE IF(quantity >= 12) THEN impact:= (total_cost / amort); ELSE IF(quantity < 12 AND single_price <= 500) THEN impact:= total_cost; ELSE IF(quantity < 12 AND single_price > 500) THEN impact:= ((12 - month)*(total_cost/(amort*12))); END IF; END IF; END IF; END IF; IF (TG_OP =''INSERT'' OR TG_OP=''UPDATE'') THEN UPDATE table SET impact_previ = impact WHERE id_line_table = NEW.id_line; END IF; RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER add_impact_previ BEFORE INSERT OR UPDATE ON public.budget FOR EACH ROW EXECUTE PROCEDURE calcul_impact(); I always get the error : Error SQL : ERROR: record "new" has no field named "id_ligne" Has anyone an idea about what's wrong ? thanks for answering me
[SQL] help with limiting query results
Hi, I have the following table in postgres: hostname | username| logontime --+---+ ws1 | rautaonn | 2004-01-13 21:25:01.100336 ws1 | administrator | 2004-01-13 21:25:07.706546 ws1 | testuser | 2004-01-13 21:25:16.084844 ws2 | testuser | 2004-01-13 21:25:18.683653 ws2 | testuser2 | 2004-01-13 21:25:20.862199 ws2 | administrator | 2004-01-13 21:25:25.932736 ws2 | oizone| 2004-01-13 21:25:30.107574 and I would need to create a query that selects each hostname only once with username that has the latest timestamp in the logontime column. The real table has about 5000 rows with ¨500 different hostnames, and I would need this query for reporting. Any help would be appreciated. Thank in advance. -Onni Rautanen ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Trigger to identify which column(s) updated
Does anyone know how to write a trigger that would identify which columns have actually changed in an update (and then log them to an archive). I suspect that the function would look something like; CREATE FUNCTION FIND_CHANGED_COLUMNS() RETURNS OPAQUE AS ' BEGIN -- FOR EACH COLUMN IN THE RECORD: -- IF ( NEW COLUMNx <> OLD COLUMNx) -- LOG THE RECORD PRIMARY KEY, COLUMN NAME, OLD VALUE RETURN NEW; END; ' LANGUAGE 'plpgsql'; In other words - How might you parse, in general, old and new records to compare like columns? - How can you find out the primary key of a record? Thanks, Jack ---(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] DROP TRIGGER
hi all again i have little complex database was used in tests of a program in ordinal way (no manual expirements with pg_catalog have done) now the database seems damaged in strange manner: Postgres cannot drop nor create some triggers (he was thinking about an hour on the query then i have cancelled) The undroppable and uncreateable triggers are all on the one table of the DB ANY OTHER triggers looks fine (drop and create) VACUUM FULL VERBOSE ANALYZE; drove the Postgres to nirvana too with output: bla-bla-bla some table pf pg_catalog INFO: --Relation pg_catalog.pg_type-- INFO: Pages 7: Changed 0, reaped 1, Empty 0, New 0; Tup 375: Vac 0, Keep/VTL 0/0, UnUsed 10, MinLen 144, MaxLen 144; Re-using: Free/Avail. Space 1664/1472; EndEmpty/Avail. Pages 0/1. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: Index pg_type_oid_index: Pages 2; Tuples 375: Deleted 0. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: Index pg_type_typname_nsp_index: Pages 8; Tuples 375: Deleted 0. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: Rel pg_type: Pages: 7 --> 7; Tuple(s) moved: 0. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: Analyzing pg_catalog.pg_type that's all. I will not drop this DB and waiting for your reply. Dear developers if you suspect a bug, i would gladly provide any info on your request even dump. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Initially Deffered - FK
Hi all, I am using : PostgreSQL 7.2.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 20020903 (Red Hat Linux 8.0 3.2-7) I am facing strange problem.. I have created two tables: create table contact (id int constraint contact_pk primary key, name text ); create table address (id int constraint address_fk references contact(id) on delete cascade initially deferred, city text, pin text); Lets.. insert few data in it.. insert into contact values (1, 'Denis'); insert into contact values (2, 'Anand'); insert into contact values (3, 'Debatosh'); insert into contact values (4, 'Pradeep'); insert into address values (1,'Howrah','711102'); insert into address values (2,'Kolkata','71'); insert into address values (3,'Jadavpur','75'); insert into address values (4,'Mumbai','42'); Now, below gives me the correct result. select * from contact; select * from address; acedg=> select * from contact; select * from address; id | name +-- 1 | Denis 2 | Anand 3 | Debatosh 4 | Pradeep (4 rows) id | city | pin +--+ 1 | Howrah | 711102 2 | Kolkata | 71 3 | Jadavpur | 75 4 | Mumbai | 42 (4 rows) BUT, the problem starts when i issue the following set of DMLs in transaction: begin; delete from contact where id=1; insert into contact values (1, 'Denis'); delete from address where id=1;/* this is not required.. but my app.fires. Should not have any impact */ insert into address values (1,'Howrah','711102'); end; It gives me the result: acedg=> select * from contact; select * from address; id | name +-- 2 | Anand 3 | Debatosh 4 | Pradeep 1 | Denis (4 rows) id | city | pin +--+ 2 | Kolkata | 71 3 | Jadavpur | 75 4 | Mumbai | 42 (3 rows) Where is my lastly inserted row ?? i.e. insert into address values (1,'Howrah','711102'); I have tested the same in ORACLE, and it works fine (i.e. both table has 4 records). It is BUG or !!! Pl. help. Thanx Denis ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Initially Deffered - FK
Hi Stephan, Thanks for your reply. But, you will agree that result should be same JUST BEFORE and JUST AFTER commit ( assuming no one is working on the database and i am the only user connected.) Till, the commit ( or end ) is issued, if you query ADDRESS, you will get 4 rows. This is expected result. But, just issue commit and see, the result gets changed !! Is this behaviour rectified / changed in later release of PG (say 7.3 or 7.4) ? Any help will be appreciated. Thanx Denis - Original Message - From: "Stephan Szabo" <[EMAIL PROTECTED]> To: "Denis" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, January 16, 2004 10:48 AM Subject: Re: [SQL] Initially Deffered - FK > > On Fri, 16 Jan 2004, Denis wrote: > > create table contact (id int constraint contact_pk primary key, name > > text ); > > create table address (id int constraint address_fk references contact(id) on > > delete cascade initially deferred, > >city text, > >pin text); > > > > Lets.. insert few data in it.. > > > > insert into contact values (1, 'Denis'); > > insert into contact values (2, 'Anand'); > > insert into contact values (3, 'Debatosh'); > > insert into contact values (4, 'Pradeep'); > > > > insert into address values (1,'Howrah','711102'); > > insert into address values (2,'Kolkata','71'); > > insert into address values (3,'Jadavpur','75'); > > insert into address values (4,'Mumbai','42'); > > > > Now, below gives me the correct result. > > > > select * from contact; select * from address; > > > > acedg=> select * from contact; select * from address; > > id | name > > +-- > > 1 | Denis > > 2 | Anand > > 3 | Debatosh > > 4 | Pradeep > > (4 rows) > > > > id | city | pin > > +--+ > >1 | Howrah | 711102 > >2 | Kolkata | 71 > >3 | Jadavpur | 75 > >4 | Mumbai | 42 > > (4 rows) > > > > BUT, the problem starts when i issue the following set of DMLs in > > transaction: > > > > begin; > > delete from contact where id=1; > > insert into contact values (1, 'Denis'); > > delete from address where id=1;/* this is not required.. but my > > app.fires. Should not have any impact */ > > insert into address values (1,'Howrah','711102'); > > end; > > > > It gives me the result: > > > > acedg=> select * from contact; select * from address; > > id | name > > +-- > >2 | Anand > >3 | Debatosh > >4 | Pradeep > >1 | Denis > > (4 rows) > > > > id | city | pin > > +--+ > >2 | Kolkata | 71 > >3 | Jadavpur | 75 > >4 | Mumbai | 42 > > (3 rows) > > > > Where is my lastly inserted row ?? i.e. > > insert into address values (1,'Howrah','711102'); > > Definitional difference. We currently treat a > request to defer the constraint to mean defer > referential actions as well, thus the inserted > address is removed when the on delete cascade > occurs after it at transaction end. Noone's > been entirely sure whether this is correct > or not per spec as I remember. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Trigger to identify which column(s) updated
Dear Jack , I suspect that the function would look something like; CREATE FUNCTION FIND_CHANGED_COLUMNS() RETURNS OPAQUE AS ' BEGIN -- FOR EACH COLUMN IN THE RECORD: -- IF ( NEW COLUMNx <> OLD COLUMNx) -- LOG THE RECORD PRIMARY KEY, COLUMN NAME, OLD VALUE RETURN NEW; END; ' LANGUAGE 'plpgsql'; Instead of using a trigger use a rule as CREATE RULE log_allthat AS ON UPDATE TO table_to_scan WHERE ((NEW.coloum_1 != old.NEW.coloum_1) OR (NEW.coloum_2 != old.NEW.coloum_2)) OR (MORE COLUMNS DO INSERT INTO log_changes_table ( PRIMARY_KEY,COLUMN_NAME,OLD_VALUE ) VALUES ( OLD.PRIMARY_KEY,OLD.COLUMN_NAME,OLD.OLD_VALUE ); Yes you will have to create a table as log_changes_table or any name you fancy such that data could be loged Kindly shoot back if this helps. -- Regards, Vishal Kashyap ~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~* I Know you believe my words so logon to Jabber.org and add [EMAIL PROTECTED] to your roster. ~*~*~*~*~*~*~*~* I am usually called by the name Vishal Kashyap but my Girl friend believes my name should be Vishal CASH UP.This is because others love my nature and my Girl friend loves my CASH. ~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~* ---(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] Left joins with multiple tables
On Sat, Jan 17, 2004 at 02:30:01AM +, Colin Fox wrote: > For each person in the people table, they may or may not have a record in > a, may or may not have a record in b, and may or may not have a record in > c. ... > But I'd like to be able to do something like: > > select > id, name, a.field1, b.field2, c.field3 > from > people p left outer join a on a.person_id = p id, > people p left outer join b on b.person_id = p.id, > people p left outer join c on c.person_id = p.id; You can just chain the joins and the Right Thing will happen: SELECT id, name, a.field1, b.field2, c.field3 FROM people p LEFT OUTER JOIN a ON (p.id = a.person_id) LEFT OUTER JOIN a ON (p.id = b.person_id) LEFT OUTER JOIN a ON (p.id = c.person_id) I'm not sure that this behaviour is mandated by the SQL standard; a certain other popular open source database-like product interprets the same construction differently. But it does do what you want in postgres. Richard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Is it possible in PostgreSQL?
This is what I use to flatten a table, the syntax may not be postgresql
correct but you will get idea.
SELECT
a.name
,SUM (CASE
WHEN EXTRACT(month from a.date) = 1 THEN a.quantity
ELSE 0
END) AS '01'
,SUM(CASE
WHEN EXTRACT(month from a.date) = 2 THEN a.quantity
ELSE 0
END) AS '02'
etc,etc.
FROM
(SELECT DISTINCT name
FROM "whatever") as a
JOIN
"whatever" as b on a.name = b.name
WHERE
-- put in year range
GROUP BY
a.name
The "a" table could b a temp table with the know values to speed up execution.
The main thing is to only scan the table once.
Let me know how it works out for you.
On Sunday 18 January 2004 07:02, you wrote:
> Moving thread over to SQL list as it belongs there.
>
> Bronx: This certainly is possible, but IMO, not in one query. Actually
> doing it will be relatively complex. For purposes of maintenance, I am
> thinking that doing this would be better handled by wrapping at least one
> view.
>
> CREATE VIEW sales_pre_proc AS
> SELECT name, quantity, to_char("date", '') AS year, to_char("date",
> 'MM') FROM sales;
>
> This is needed for the group by statement below to function properly:
> CREATE VIEW sales_month_summary AS
> SELECT name, sum(quantity) AS quantity, year, month from sales_pre_proc
> GROUP BY name, year, month;
>
> This will give you a view that will have the sum information. Now we just
> have to create the statement which will create the pivot effect. I
> understand that there is something under contrib/tablefunc for this, but I
> do not have it on my system (cygwin), at the moment. Perhaps someone else
> can help.
>
> Failing that, you can write your own function to return each row. I was
> working on a quick proof of concept but it was not working properly.
>
> Best Wishes,
> Chris Travers
>
> - Original Message -
> From: Bronx
> To: [EMAIL PROTECTED]
> Sent: Tuesday, January 13, 2004 6:58 AM
> Subject: [ADMIN] Is it possible in PostgreSQL?
>
>
> Hi,
> I've got problem with one specific query. I've got the table
> with many of rekords like these:
>
> name | quantity| date
> ---
> aaa22003-04-01
> bbb42003-04-12
> ccc52003-05-12
> aaa32003-01-14
> aaa12003-12-09
> bbb92003-08-08
>
> and so on ...
>
> Does anybody know how make query which return grouped
> records by month of year and name (also sum of quantity).
> It is possible to make a query whitch return something like that:
>
> name | 01 | 02 | 03 | 04 | ... | 12 (months)
>
> aaa x x xx... x
> bbb x x xx... x
> ccc x x xx... x
>
> where x means sum of quantity in month.
> It is possible to make it in one query?
> I know that in Access is construction : PIVOT.
>
> Thanks
> Adam
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] Execute permissions for stored functions
Hi all, From what I've seen in the archives, questions like this have kind of been answered in the past, but I was wondering if there have been any changes in this area, or if anyone has good ideas on how to do what I'm about to ask :) In RDBMSs such as Oracle, stored PL/SQL functions run with the permissions of the user that creates the function. Users who are given EXECUTE privileges then call the function with the permissions of the creator of the function. Is this how things work with PL/pgSQL in PostgreSQL? From my understanding, the answer is 'no.' If the answer really is 'no,' then how do I achieve the same thing? The main benefit for this is in security - I have a dynamic web application that requires (a lot of) access to a PostgreSQL database. I want to make sure that the user doesn't have direct access to change the content of tables, but rather to alter their contents, in predetermined ways, through a set of functions. It's another layer that protects against hacking, and because my project involves a lot of monetary transactions (and database transactions), I want to reduce my potential for malicious abuse. Many thanks in advance for you help, Paul smime.p7s Description: S/MIME cryptographic signature
Re: [SQL] Problem with LEFT JOIN
"Thomas Wegner" <[EMAIL PROTECTED]> writes:
> Hello, whats wrong with this SQL?:
> SELECT L.*, A."lastname" AS firma_value, T."string_val1" AS type_value
> FROM "lists" L, "typecode" T
> LEFT JOIN "adressen" A ON A."id_adressen"=L."firma"
> WHERE T."id_typecode"=L."lists_type"
> ORDER BY L."id_lists"
> I get this:
> ERROR: relation "l" does not exist
The problem is in your ON clause: the above is equivalent to
SELECT ...
FROM "lists" L CROSS JOIN
("typecode" T LEFT JOIN "adressen" A ON A."id_adressen"=L."firma")
WHERE ...
so the ON clause is illegal because it controls the join of T and A,
in which L does not appear.
I have a sneaking suspicion that you are trying to port some MySQL code.
Last I heard, MySQL interprets the above FROM syntax as
FROM ("lists" L CROSS JOIN "typecode" T) LEFT JOIN "adressen" A ON
A."id_adressen"=L."firma"
which makes the ON condition legal. Unfortunately for MySQL, their
parser is directly in violation of the SQL standard on this point.
JOIN is supposed to bind more tightly than comma.
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[SQL] How can I get the last element out of GROUP BY sets?
I'm trying to produce summary data from a table (using PGSQL 7.4.1): CREATE TABLE readings( "when" timestamp, value integer ); The summary will be based on various time periods. I've been using date_trunc( 'hour', "when" ) and GROUP BY for the min/max/average readings with no problems. But, one piece of data I need is the last value for each GROUP BY period. Alas, I cannot figure out how to do this. If I wanted to loop from a script, I could, for instance, execute the following for each GROUP BY period (filling in ? appropriately): SELECT date_trunc( 'hour', "when" ), value FROM readings WHERE date_trunc( 'hour', "when" )::timestamp = ? ORDER BY "when" DESC LIMIT 1 But, I figure there's probably some what to do this in SQL. Any help? Thanks, Rob -- 21:12:24 up 21 days, 11:00, 4 users, load average: 2.23, 1.69, 1.28 pgp0.pgp Description: PGP signature
Re: [SQL] help with limiting query results
"OizOne" <[EMAIL PROTECTED]> writes: > I would need to create a query that selects each hostname only once with > username that has the latest timestamp in the logontime column. SELECT DISTINCT ON is a convenient way to do this. See the "weather reports" example in the SELECT reference page for a similar case. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Left joins with multiple tables
Hi Colin, Try select id, name, a.field1, b.field2, c.field3 from people p left outer join a on (a.person_id = p id) left outer join b on (b.person_id = p.id) left outer join c on (c.person_id = p.id); HTH Denis - Original Message - From: "Colin Fox" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, January 17, 2004 8:00 AM Subject: [SQL] Left joins with multiple tables > Hi, all. > > I've got a bit of a problem here. I have 4 tables - people, a, b, c (not > the original names). > > For each person in the people table, they may or may not have a record in > a, may or may not have a record in b, and may or may not have a record in > c. > > Handling the first table (a) is easy: > > select id, name > from people p left outer join a on a.person_id = p id; > > But I'd like to be able to do something like: > > select > id, name, a.field1, b.field2, c.field3 > from > people p left outer join a on a.person_id = p id, > people p left outer join b on b.person_id = p.id, > people p left outer join c on c.person_id = p.id; > > Naturally you can't repeat the 'people p' clause 3 times, but is there > some other syntax that would let me do this? > > Thanks! > cf > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Execute permissions for stored functions
Paul Hart <[EMAIL PROTECTED]> writes: > In RDBMSs such as Oracle, stored PL/SQL functions run with the > permissions of the user that creates the function. Users who are given > EXECUTE privileges then call the function with the permissions of the > creator of the function. Use "SECURITY DEFINER" to get this behavior in Postgres. The SQL99 spec punts as to whether SECURITY DEFINER should be the default or not, so unfortunately neither we nor Oracle can be said to be wrong on this point... regards, tom lane ---(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 can I get the last element out of GROUP BY sets?
Robert Creager <[EMAIL PROTECTED]> writes: > ... one piece of data I need is the last value for each GROUP BY > period. Alas, I cannot figure out how to do this. SELECT DISTINCT ON (rather than GROUP BY) could get this done for you. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] DROP TRIGGER
sad <[EMAIL PROTECTED]> writes: > ... now the database seems damaged in strange manner: > Postgres cannot drop nor create some triggers > (he was thinking about an hour on the query then i have cancelled) Could you try it again and get a stack trace to show exactly where it's hung up? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
