[SQL] Re: enumerating rows
Try this
CREATE SEQUENCE just_a_seq;
Select nextval('just_a_seq') as row_no, * from pg_tables ;
drop SEQUENCE just_a_seq;
>
> row_no | column1 | column2 | ...
> ---+-+-+ ...
> 1 | datum11 | datum12 | ...
> 2 | datum21 | datum22 | ...
>... | ... | ... | ...
>
> I didn't find anything in the docs.
>
> TIA, Zoltan
>
---(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] Rule won't let me NOTIFY, no matter how hard I try
El Mar 13 Ene 2004 18:07, Jeff Boes escribió:
>JB: Here's the setup: I wanted to write a rule that would fire on an update
>JB: to one table, and do an update to another table, followed by a notify.
>JB: My first attempt wasn't acceptable to PG (7.3.4):
>JB:
>JB: create rule "my_rule" as
>JB: on update to table_A
>JB: where new.col_A != old.col_A
>JB: do
>JB:(update table_B ...;
>JB: notify "my_signal";
>JB:);
>JB:
>JB: ... because you can't have a "notify" statement in a rule that fires on
>JB: update (only select, update, and delete, I guess).
>JB:
hi,
you have to write to rules
first one (update):
create rule "my_rule" as
on update to table_A
where new.col_A != old.col_A
do
update table_B ...;
second one (notify):
create rule "my_rule2" as
on update to table_A
notify my_rule;
>JB: Second attempt was to "hide" the notify in a function:
>JB:
>JB: create function fn_notify(TEXT) returns VOID as '
>JB: execute ''notify " || $1 || "'';
>JB: ' language 'plpgsql';
The correct syntax...
create function fn_notify ( text ) RETURNS void as '
declare
v_signal alias for $1;
begin
execute '' notify "'' || v_signal || ''"'';
return;
end;
' language 'plpgsql';
...and re-write the first rule
create rule "my_rule" as
on update to table_A
where new.col_A != old.col_A
do
( update table_B ...;
SELECT fn_notify('my_signal'); );
CAVEAT: This rule always returns a tuple:
fn_notify
---
(1 row)
--
---
Luis Carlos Ferreira [EMAIL PROTECTED]
Centro de CómputosJunin 2957 - Santa Fe - Argentina
Sindicato de Luz y Fuerza Tel.: (54)(342) 4520-075
---
Estas loco? come vaca!!
---(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) the type 'list' and the function 'comma_cat' (I cannot remember where I took it, but are very useful)... CREATE FUNCTION comma_cat (text, text) RETURNS text AS 'select case WHEN $2 is null or $2 = THEN $1 WHEN $1 is null or $1 = THEN $2 ELSE $1 || '', '' || $2 END' LANGUAGE sql; CREATE AGGREGATE list ( BASETYPE = text, SFUNC = comma_cat, STYPE = text, INITCOND = '' ); -- 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 ---(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] 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
