Re: [SQL] my pgsql error?
Christopher Sawtell <[EMAIL PROTECTED]> said: > On Wed, 14 Mar 2001 01:38, Richard Huxton wrote: > > From: "Christopher Sawtell" <[EMAIL PROTECTED]> > > > > > Please could a kind soul help me with this. > > [ ... ] > > > Note to readers: this is not a general service, I'm in a good mood ;-) > In that case, thank you very much indeed, and may the blessings of > the Deities be on you. > > > For a moment I thought you could do to_char(now(),'Day DD Month ' > > but you're quite right you need to rtrim() the various pieces. > > Personally I think the fact that you have to do all that rtrim() stuff is > very close to being a bug. What do you think? > It certainly comes under the headings of "unexpected" and "making more work for me" but I seem to remember it's supposed to be compatible with Oracle so it might be something Oracle does. - Richard Huxton ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] copy a record from one table to another (archive)
Thus spake Richard Huxton > begin; > insert into archive_foo (select * from foo where foo_id=1); > delete from foo where foo_id=1; > commit; > > Is probably the closest you could get. > > Alternatively, you could wrap the above up in a function and just go: > > select do_archive_foo(1); Or even use a trigger if suitable. You can have the delete automatically trigger an insert into another table. -- D'Arcy J.M. Cain| Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re(2): [SQL] Permissons on database
you can also do it by a shell script
grantall.sh:
## start of grantall.sh ###
#!/bin/ash
SQL="SELECT relname FROM pg_class WHERE (relkind = 'r' OR relkind = 'S')
AND relname !~ '^pg_' ORDER BY relname"
OBJ=`psql -t -c "${SQL}" $1`
# OBJ=`echo ${OBJ} | sed 's/EOF//g'`
OBJ=`echo ${OBJ} | sed 's/ /, /g'`
# SQL="REVOKE ALL ON ${OBJ} FROM PUBLIC"
SQL="GRANT ALL ON ${OBJ} TO PUBLIC"
echo ${SQL}
psql -c "${SQL}" $1
## end of grantall.sh ###
syntax: grantall.sh name_of_database
sherwin
[EMAIL PROTECTED] writes:
>> "bk" == Boulat Khakimov <[EMAIL PROTECTED]> writes:
>
>bk> How do I grant permissions on everything in the selected
>bk> databes?
>
>bk> GRANT doesnt take as on object database name nor does it
>bk> accept wild chars
>
>Attached is some Perl code I wrote long ago to do this. This
>particular code was done for Keystone, a problem tracking database and
>it would do a "GRANT ALL". Modify it as needed. Last I checked it
>worked with both PostgreSQL 6.5.x and 7.0.x
>
>
>roland
>--
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] my pgsql error?
> For a moment I thought you could do to_char(now(),'Day DD Month ' > but you're quite right you need to rtrim() the various pieces. regression=# select to_char(now()-10,'Day DD Month '); to_char - Sunday04 March 2001 (1 row) regression=# select to_char(now()-10,'fmDay fmDD fmMonth '); to_char - Sunday 4 March 2001 (1 row) The 'fm' prefix is not very well explained in the docs at http://www.postgresql.org/devel-corner/docs/postgres/functions-formatting.html If anyone wants to suggest better wording, I'll be glad to put it in. 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]
Hello, I have created a table test1(fromuri varchar(350), tovalue(varchar(3000)) and I have created a btree index on the fromuri attribute. The size of the table is 630kb while the size of the index is 417kb. I execute the following query select * from test1 where fromuri like 'http://ww%'; I get the following message NOTICE: PortalHeapMemoryFree: 0x16563a8 not in alloc set! ERROR: AllocSetFree: cannot find block containing chunk This is a bit strange taking into account that the total size of database buffers I have defined is 80Mb Thanks in advance for your help Sofia Alexaki ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] PL/pgSQL "compilation error"
Hello all - I apologize for the newbie-esque question, but the debug output from postgres when you have a bug in your PL/pgSQL procedure is none to detailed. I've created the following procedure and am getting an error when I try to update the table. The error is something like "parse error near ; on line 50". Line 50 is the last line. There's probably something glaring wrong in here that I'm not seeing, but any help would be appreciated. I don't know if the \ at the end of the line is a problem, but those were added late in the game and didn't change the error message ;-) Tim CREATE FUNCTION artifactgroup_update_agg () RETURNS OPAQUE AS ' BEGIN -- -- see if they are moving to a new artifacttype -- if so, its a more complex operation -- IF NEW.group_artifact_id <> OLD.group_artifact_id THEN -- -- transferred artifacts always have a status of 1 -- so we will increment the new artifacttypes sums -- UPDATE artifact_counts_agg SET count=count+1, open_count=open_count+1 \ WHERE group_artifact_id=NEW.group_artifact_id; -- -- now see how to increment/decrement the old types sums -- IF NEW.status_id <> OLD.status_id THEN IF OLD.status_id = 2 THEN UPDATE artifact_counts_agg SET count=count-1 \ WHERE group_artifact_id=OLD.group_artifact_id; -- -- no need to do anything if it was in deleted status -- END IF; ELSE -- -- Was already in open status before -- UPDATE artifact_counts_agg SET count=count-1, open_count=open_count-1 \ WHERE group_artifact_id=OLD.group_artifact_id; END IF; ELSE -- -- just need to evaluate the status flag and -- increment/decrement the counter as necessary -- IF NEW.status_id <> OLD.status_id THEN IF new.status_id = 1 THEN UPDATE artifact_counts_agg SET open_count=open_count+1 \ WHERE group_artifact_id=new.group_artifact_id; ELSE IF new.status_id = 2 THEN UPDATE artifact_counts_agg SET open_count=open_count-1 \ WHERE group_artifact_id=new.group_artifact_id; ELSE IF new.status_id = 3 THEN UPDATE artifact_counts_agg SET open_count=open_count-1,count=count-1 \ WHERE group_artifact_id=new.group_artifact_id; END IF; END IF; END IF; RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER artifactgroup_update_trig AFTER UPDATE ON artifact FOR EACH ROW EXECUTE PROCEDURE artifactgroup_update_agg(); -- Founder - PHPBuilder.com / Geocrawler.com Lead Developer - SourceForge VA Linux Systems ---(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] PL/pgSQL "compilation error"
> On Wed, Mar 14, 2001 at 10:33:49AM -0500, Bruce Momjian wrote: > > It is hard to test this without the table schema, but I think you are > > missing some END IF's in the code above. Those are not elif's, but > > actual new IF's that need their own END IF, I think. > > Oh wow - this is almost like going back to my COBOL days. > > IF NEW.status_id <> OLD.status_id THEN > IF new.status_id = 1 THEN > UPDATE artifact_counts_agg SET open_count=open_count+1 \ > WHERE group_artifact_id=new.group_artifact_id; > ELSE > IF new.status_id = 2 THEN > UPDATE artifact_counts_agg SET open_count=open_count-1 \ > WHERE group_artifact_id=new.group_artifact_id; > ELSE > IF new.status_id = 3 THEN > UPDATE artifact_counts_agg SET open_count=open_count-1,count=count-1 \ > WHERE group_artifact_id=new.group_artifact_id; > END IF; > END IF; > END IF; > END IF; > > Yes - nesting those IFs did the trick. Are there more examples of PL/pgSQL > anywhere beyond those on your website? > > Thanks, Bruce! > There is a PL/PgSQL HowTo that we are trying to integrate into the docs for 7.1. Anyone have a URL or status on that? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] PL/pgSQL "compilation error"
> Don't you have one END IF too many? No you don't ... I can't count this morning :-( Weird. I don't see what's wrong either. Anyone? 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] PL/pgSQL "compilation error"
Don't you have one END IF too many? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] PL/pgSQL "compilation error"
Tim, > Hello all - I apologize for the newbie-esque question, but the debug output > from postgres when you have a bug in your PL/pgSQL procedure is none to > detailed. It's an improvement over the query parser errors -- at least in PL/pgSQL you get a line number! Although in your case, the line number is deceptive. This brings up an important point. We have a medium-large user base for PL/pgSQL out there, but it appears that Jan Wieck no longer has time to develop the language ... nor should he be the sole developer. Howe do we find more developers to expand & extend PL/pgSQL? I'd be willing to contribute financially, but can't afford to actuall hire somebody on my own (and don't have the moxie to doe the programming!). > I've created the following procedure and am getting an error when I try to > update the table. The error is something like "parse error near ; on line 50". > Line 50 is the last line. Actually, your question is not newbie-esque. That's quite a sophisticated procedure! THe problem is simple. PL/pgSQL at this time does not support the "ELSE IF" structure. Thus, the compiler is looking for more "END IF"s that it doesn't find, and errors out when it gets to the end of the procedure without seeing them. Thus, your final program section needs to be structured like this: -- -- just need to evaluate the status flag and -- increment/decrement the counter as necessary -- IF NEW.status_id <> OLD.status_id THEN IF new.status_id = 1 THEN UPDATE artifact_counts_agg SET open_count=open_count+1 \ WHERE group_artifact_id=new.group_artifact_id; ELSE IF new.status_id = 2 THEN UPDATE artifact_counts_agg SET open_count=open_count-1 \ WHERE group_artifact_id=new.group_artifact_id; ELSE IF new.status_id = 3 THEN UPDATE artifact_counts_agg SET open_count=open_count-1,count=count-1 \ WHERE group_artifact_id=new.group_artifact_id; END IF; END IF; END IF; END IF; END IF; RETURN NEW; END; -Josh -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. 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])
Re: [SQL] PL/pgSQL "compilation error"
On Wed, Mar 14, 2001 at 10:33:49AM -0500, Bruce Momjian wrote: > It is hard to test this without the table schema, but I think you are > missing some END IF's in the code above. Those are not elif's, but > actual new IF's that need their own END IF, I think. Oh wow - this is almost like going back to my COBOL days. IF NEW.status_id <> OLD.status_id THEN IF new.status_id = 1 THEN UPDATE artifact_counts_agg SET open_count=open_count+1 \ WHERE group_artifact_id=new.group_artifact_id; ELSE IF new.status_id = 2 THEN UPDATE artifact_counts_agg SET open_count=open_count-1 \ WHERE group_artifact_id=new.group_artifact_id; ELSE IF new.status_id = 3 THEN UPDATE artifact_counts_agg SET open_count=open_count-1,count=count-1 \ WHERE group_artifact_id=new.group_artifact_id; END IF; END IF; END IF; END IF; Yes - nesting those IFs did the trick. Are there more examples of PL/pgSQL anywhere beyond those on your website? Thanks, Bruce! Tim -- Founder - PHPBuilder.com / Geocrawler.com Lead Developer - SourceForge VA Linux Systems ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] PL/pgSQL "compilation error"
> IF NEW.status_id <> OLD.status_id THEN > IF new.status_id = 1 THEN > UPDATE artifact_counts_agg SET open_count=open_count+1 \ >WHERE group_artifact_id=new.group_artifact_id; > ELSE IF new.status_id = 2 THEN > UPDATE artifact_counts_agg SET open_count=open_count-1 \ >WHERE group_artifact_id=new.group_artifact_id; > ELSE IF new.status_id = 3 THEN > UPDATE artifact_counts_agg SET open_count=open_count-1,count=count-1 \ >WHERE group_artifact_id=new.group_artifact_id; > END IF; > END IF; >END IF; >RETURN NEW; It is hard to test this without the table schema, but I think you are missing some END IF's in the code above. Those are not elif's, but actual new IF's that need their own END IF, I think. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL]
From: "Alexaki Sofia" <[EMAIL PROTECTED]> > I execute the following query > select * from test1 where fromuri like 'http://ww%'; > > I get the following message > NOTICE: PortalHeapMemoryFree: 0x16563a8 not in alloc set! > ERROR: AllocSetFree: cannot find block containing chunk It's complaining that memory it tries to free hasn't been allocated. Something has got mangled here. I'd take a backup of the table drop the index and recreate it, see if that helps. Also - what version of Postgres is this? One of the developers might recognise a known bug if it isn't current. - Richard Huxton > This is a bit strange taking into account that the total size > of database buffers I have defined is 80Mb > > Thanks in advance for your help > Sofia Alexaki > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] PL/pgSQL "compilation error"
From: "Tim Perdue" <[EMAIL PROTECTED]> > Hello all - I apologize for the newbie-esque question, but the debug output > from postgres when you have a bug in your PL/pgSQL procedure is none to > detailed. > > I've created the following procedure and am getting an error when I try to > update the table. The error is something like "parse error near ; on line 50". > Line 50 is the last line. You're writing 50 line functions and you're calling yourself a newbie? Hate to think what that makes me. > There's probably something glaring wrong in here that I'm not seeing, but any > help would be appreciated. Nothing huge leaps out at me except for the \ and the occasional lower-case 'new' - don't know if 'new' is the same as 'NEW' - not sure I've tried it. I have used statements on multiple lines without the \ though, so it might be worth snipping them and seeing what happens. > I don't know if the \ at the end of the line is a problem, but those were > added late in the game and didn't change the error message ;-) [snip] > UPDATE artifact_counts_agg SET open_count=open_count-1 \ >WHERE group_artifact_id=new.group_artifact_id; Failing that, email me the definition of artifacts_count_agg and artifact and I'll play with it here. - Richard Huxton ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] PL/pgSQL "compilation error"
> Bruce Momjian writes: > > > There is a PL/PgSQL HowTo that we are trying to integrate into the docs > > for 7.1. Anyone have a URL or status on that? > > It's not a PL/pgSQL Howto, it's just a documentation how to port from > Oracle's PL/SQL. The status is "done". The author expressed interest in > more work on the PL/pgSQL documentation; we'll see what comes of it. Oh, that's great. Thanks. > http://www.postgresql.org/devel-corner/docs/postgres/plpgsql-porting.html -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] pl/Perl
Tom, 1.Where or how I can get pltcl.so? I have not find this file anywhere in my source except a pltcl.c. 2.Dose installation same as plpgsql? i.e. CREATE FUNCTION pltcl_call_handler () RETURNS OPAQUE AS '/usr/local/pgsql/lib/pltcl.so' LANGUAGE 'C'; CREATE TRUSTED PROCEDURAL LANGUAGE 'pltcl' HANDLER pltcl_call_handler LANCOMPILER 'PL/pgtcl'; 3.Where I can find more doc about pltcl? Thanks for your time. Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Wed, 21 Feb 2001, Tom Lane wrote: > Jie Liang <[EMAIL PROTECTED]> writes: > > My choice: > > if involving a lot of regular expressions, pl/Perl is better; > > if involving a lot of SQLs or other functions(or store procedures), > > then pl/pgsql is better. > > Also consider pltcl, which has pretty nearly perl-equivalent regexp > support, and can do queries too. Besides which it's easier to build/ > install than plperl. > > It's a shame that plperl doesn't yet have support for making queries. > It hasn't really progressed much past the proof-of-concept stage IMHO, > but no one is working on it :-( > > regards, tom lane > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] PL/pgSQL "compilation error"
Bruce Momjian writes: > There is a PL/PgSQL HowTo that we are trying to integrate into the docs > for 7.1. Anyone have a URL or status on that? It's not a PL/pgSQL Howto, it's just a documentation how to port from Oracle's PL/SQL. The status is "done". The author expressed interest in more work on the PL/pgSQL documentation; we'll see what comes of it. http://www.postgresql.org/devel-corner/docs/postgres/plpgsql-porting.html -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(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]
you can also do it by a shell script
grantall.sh:
## start of grantall.sh ###
#!/bin/ash
SQL="SELECT relname FROM pg_class WHERE (relkind = 'r' OR relkind = 'S')
AND relname !~ '^pg_' ORDER BY relname"
OBJ=`psql -t -c "${SQL}" $1`
# OBJ=`echo ${OBJ} | sed 's/EOF//g'`
OBJ=`echo ${OBJ} | sed 's/ /, /g'`
# SQL="REVOKE ALL ON ${OBJ} FROM PUBLIC"
SQL="GRANT ALL ON ${OBJ} TO PUBLIC"
echo ${SQL}
psql -c "${SQL}" $1
## end of grantall.sh ###
syntax: grantall.sh name_of_database
sherwin
[EMAIL PROTECTED] writes:
>> "bk" == Boulat Khakimov <[EMAIL PROTECTED]> writes:
>
>bk> How do I grant permissions on everything in the selected
>bk> databes?
>
>bk> GRANT doesnt take as on object database name nor does it
>bk> accept wild chars
>
>Attached is some Perl code I wrote long ago to do this. This
>particular code was done for Keystone, a problem tracking database and
>it would do a "GRANT ALL". Modify it as needed. Last I checked it
>worked with both PostgreSQL 6.5.x and 7.0.x
>
>
>roland
>--
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Rule/currval() issue
This is related to the plpgsql project I was working on this morning. I'm
trying to create a rule, so that when a row is inserted into a certain table,
we also create a row over in a "counter table". The problem lies in getting
the primary key value (from the sequence) so it can be inserted in that
related table.
I tried a couple different approaches. Neither works.
artifact_group_list is a table where the primary key, group_artifact_id is
SERIAL type. When I insert a row, I want to get that new value in my rule.
--
-- Define a rule so when you create a new ArtifactType
-- You automatically create a related row over in the counters table
--
CREATE RULE artifactgroup_insert_agg AS
ON INSERT TO artifact_group_list
DO INSERT INTO
artifact_counts_agg (group_artifact_id,count,open_count)
VALUES (currval('artifact_grou_group_artifac_seq'),0,0);
I get this:
ERROR: artifact_grou_group_artifac_seq.currval is not yet defined in this session
If I write the rule this way:
CREATE RULE artifactgroup_insert_agg AS
ON INSERT TO artifact_group_list
DO INSERT INTO
artifact_counts_agg (group_artifact_id,count,open_count)
VALUES (new.group_artifact_id,0,0);
...it doesn't fail with an error, but the sequence increments twice.
Tim
--
Founder - PHPBuilder.com / Geocrawler.com
Lead Developer - SourceForge
VA Linux Systems
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] my pgsql error?
> "Tom" == Tom Lane <[EMAIL PROTECTED]> writes: >> For a moment I thought you could do to_char(now(),'Day DD Month ' >> but you're quite right you need to rtrim() the various pieces. Tom> regression=# select to_char(now()-10,'Day DD Month '); Tom>to_char Tom> - Tom> Sunday04 March 2001 Tom> (1 row) Tom> regression=# select to_char(now()-10,'fmDay fmDD fmMonth '); Tom>to_char Tom> - Tom> Sunday 4 March 2001 Tom> (1 row) Tom> The 'fm' prefix is not very well explained in the docs at It doesn't seem to be explained at all, just listed in the table. I'd suggest adding the following (and perhaps include your example selects from above) to the usage notes below table 4-11: o to_char() does not remove trailing blanks from fields which are blank padded, e.g., `Day' and `Month', unless the `FM' prefix is used. roland -- PGP Key ID: 66 BC 3B CD Roland B. Roberts, PhD RL Enterprises [EMAIL PROTECTED] 76-15 113th Street, Apt 3B [EMAIL PROTECTED] Forest Hills, NY 11375 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] need to join successive log entries into one
I have a table like: run | seq | start| done 1415|261| 2001-01-29 12:36:55| 2001-02-07 13:02:38 1415|263| 2001-02-14 07:40:04| 2001-02-15 16:05:04 1415|264| 2001-02-16 16:05:05| 2001-03-08 16:34:03 1415|265| 2001-03-08 16:34:04| 1747|257| 2001-02-15 09:14:39| 2001-03-01 08:58:03 1747|258| 2001-03-01 08:58:04| 2001-03-01 09:01:27 1747|260| 2001-03-01 09:01:39| 2001-03-01 09:02:39 1747|262| 2001-03-05 13:13:58| 1954|218| 2001-02-02 20:55:39| 2001-02-08 12:44:48 1954|219| 2001-02-08 12:44:49| 2001-02-08 12:47:36 1954|220| 2001-02-08 12:47:36| 2001-02-08 16:50:33 1954|221| 2001-02-08 16:50:33| 2001-02-08 16:50:45 1954|222| 2001-02-08 16:50:46| 2001-02-12 14:36:41 1954|223| 2001-02-12 14:36:41| 2001-03-02 10:17:15 This is a log of some operations done on some runs. 'seq' is the step within the run. I need to produce a new table that coalesces immediately successive operations on a run into one, e.g.: run | start | done 1415| 2001-01-29 12:36:55| 2001-02-07 13:02:38 1415| 2001-02-14 07:40:04| 1747| 2001-02-15 09:14:39| 2001-03-01 09:02:39 1747| 2001-03-05 13:13:58| 1954| 2001-02-02 20:55:39| 2001-03-02 10:17:15 i.e. where a run has one or more steps with succesive seq values, or equivalently, with abutting start/end values, then I want the new table to have only one entry representing the full span of time. Null 'done' just means it's not done yet. Unfortunately, the start time of a 'succesive' op is sometimes 1 second later that the 'done' time of the previous one, so maybe using the seq field is simpler. Can anyone think of a way I can do this in postgres? -- George Young, Rm. L-204[EMAIL PROTECTED] MIT Lincoln Laboratory 244 Wood St. Lexington, Massachusetts 02420-9108(781) 981-2756 ---(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] Rule/currval() issue
Entirely untested, but how about replacing currval() in your first try
with nextval()? My theory is that the compilation of the function is
happening before the INSERT happens; therefore the sequence hasn't been
incremented yet; therefore there's no currval() for this backend
instance. If you use nextval(), you'll get the next increment, which
should be appropriate.
As I think about it, this could have record-locking implications in a
heavy use environment, since the possibility exists of another INSERT
between the nextval() and the INSERT in this situation - I don't know if
that's actually an issue, or if there would be a way around it.
Andy Perrin
Tim Perdue wrote:
>
> This is related to the plpgsql project I was working on this morning. I'm
> trying to create a rule, so that when a row is inserted into a certain table,
> we also create a row over in a "counter table". The problem lies in getting
> the primary key value (from the sequence) so it can be inserted in that
> related table.
>
> I tried a couple different approaches. Neither works.
>
> artifact_group_list is a table where the primary key, group_artifact_id is
> SERIAL type. When I insert a row, I want to get that new value in my rule.
>
> --
> -- Define a rule so when you create a new ArtifactType
> -- You automatically create a related row over in the counters table
> --
> CREATE RULE artifactgroup_insert_agg AS
> ON INSERT TO artifact_group_list
> DO INSERT INTO
> artifact_counts_agg (group_artifact_id,count,open_count)
> VALUES (currval('artifact_grou_group_artifac_seq'),0,0);
>
> I get this:
>
> ERROR: artifact_grou_group_artifac_seq.currval is not yet defined in this session
>
> If I write the rule this way:
>
> CREATE RULE artifactgroup_insert_agg AS
> ON INSERT TO artifact_group_list
> DO INSERT INTO
> artifact_counts_agg (group_artifact_id,count,open_count)
> VALUES (new.group_artifact_id,0,0);
>
> ...it doesn't fail with an error, but the sequence increments twice.
>
> Tim
>
> --
> Founder - PHPBuilder.com / Geocrawler.com
> Lead Developer - SourceForge
> VA Linux Systems
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
--
--
Andrew J. Perrin - Programmer/Analyst, Desktop Support
Children's Primary Care Research Group, UNC - Chapel Hill
(919)966-9394 * [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] Rule/currval() issue
Tim Perdue <[EMAIL PROTECTED]> writes: > This is related to the plpgsql project I was working on this morning. I'm > trying to create a rule, so that when a row is inserted into a certain table, > we also create a row over in a "counter table". The problem lies in getting > the primary key value (from the sequence) so it can be inserted in that > related table. You probably should be using a trigger, not a rule at all. 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] Rule/currval() issue
On Wed, Mar 14, 2001 at 01:09:18PM -0500, Tom Lane wrote: > Tim Perdue <[EMAIL PROTECTED]> writes: > > This is related to the plpgsql project I was working on this morning. I'm > > trying to create a rule, so that when a row is inserted into a certain table, > > we also create a row over in a "counter table". The problem lies in getting > > the primary key value (from the sequence) so it can be inserted in that > > related table. > > You probably should be using a trigger, not a rule at all. OK - so another rule like this one, is probably ill-advised as well? It seems a lot easier than going into the triggers: CREATE RULE forum_delete_agg AS ON DELETE TO forum DO UPDATE forum_agg_msg_count SET count=count-1 WHERE group_forum_id=old.group_forum_id; Tim -- Founder - PHPBuilder.com / Geocrawler.com Lead Developer - SourceForge VA Linux Systems ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Rule/currval() issue
Tim Perdue <[EMAIL PROTECTED]> writes: >> You probably should be using a trigger, not a rule at all. > OK - so another rule like this one, is probably ill-advised as well? It seems > a lot easier than going into the triggers: > CREATE RULE forum_delete_agg AS > ON DELETE TO forum > DO UPDATE forum_agg_msg_count SET count=count-1 > WHERE group_forum_id=old.group_forum_id; That seems OK offhand, although it's hard to guess whether it will be more or less efficient than a trigger. 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] Rule/currval() issue
Tom, You indicate trigger, rather than rule. Going by Momjian's book, he indicates that rules are "...ideal for when the action affects other tables." Can you clarify why you would use a trigger for this? I'm asking because I have no clue how to use rules or triggers, but need one or the other to modify a second table on inserts/deletes to the first table. I'd like to make the best choice first if possible. Thanks, Rob > -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, March 14, 2001 11:09 AM > To: Tim Perdue > Cc: [EMAIL PROTECTED] > Subject: Re: [SQL] Rule/currval() issue > > > Tim Perdue <[EMAIL PROTECTED]> writes: > > This is related to the plpgsql project I was working on > this morning. I'm > > trying to create a rule, so that when a row is inserted > into a certain table, > > we also create a row over in a "counter table". The problem > lies in getting > > the primary key value (from the sequence) so it can be > inserted in that > > related table. > > You probably should be using a trigger, not a rule at all. > > 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]) > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Rule/currval() issue
"Creager, Robert S" <[EMAIL PROTECTED]> writes: > You indicate trigger, rather than rule. Going by Momjian's book, he > indicates that rules are "...ideal for when the action affects other > tables." Can you clarify why you would use a trigger for this? Primarily because there's a need to get at the default values that will be computed for the inserted tuple (ie, the serial number it will be assigned). An ON INSERT trigger has access to those values because it's handed the fully-constructed tuple. A rule does not. QED. My take on the rule vs. trigger issue is a little different from Bruce's. To me, a trigger is ideal for actions that you want to drive off insertion/deletion/update of individual tuples --- ie, all that you need to look at to know what to do is the single tuple being processed. However, that's also a trigger's weak spot: it will be fired again, separately, for every inserted/deleted/updated tuple. In contrast, a rule specifies a transformation of the original query, which makes it good for bulk operations. For example: suppose I'm deleting a whole ton of tuples in table A, say delete from a where a.date < '2000-01-01' and my application logic dictates that associated tuples in table B also go away. If I make that happen with a trigger then I'll be executing something like delete from b where b.id = old.id separately for each deleted A tuple. That amounts to a nested-loop join between A and B, since B is scanned separately (hopefully with an indexscan!) for each A tuple. On the other hand I could write a rule on delete to a do delete from b where b.id = old.id This will expand my above query into delete from b where b.id = a.id and a.date < '2000-01-01'; delete from a where a.date < '2000-01-01'; Now the planner can turn the B delete into a merge or hash join between A and B. For large numbers of tuples that could make for a huge speedup. So basically, rules are good for specifying bulk operations between related tables, whereas a trigger is good for more "retail" kinds of things. Also, I think a trigger is a lot easier to understand, even if there's a little more learning curve involved to write one (because you also have to know some plpgsql). The transformational nature of rules is harder to get a handle on; they seem trivial but they're really not. 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] FETCH ... INTO in PL/pgSQL
Tom, Bruce, Is there any way to make use of the contents of a cursor in a PL/pgSQL function? FETCH ... INTO isn't supported, according to the docs. Can I use the dot notation, or something similar? -Josh -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] need to join successive log entries into one
George Young wrote: ... >I need to produce a new table that coalesces immediately successive >operations on a run into one, e.g.: > >run | start | done > 1415| 2001-01-29 12:36:55| 2001-02-07 13:02:38 > 1415| 2001-02-14 07:40:04| > 1747| 2001-02-15 09:14:39| 2001-03-01 09:02:39 > 1747| 2001-03-05 13:13:58| > 1954| 2001-02-02 20:55:39| 2001-03-02 10:17:15 > >i.e. where a run has one or more steps with succesive seq values, >or equivalently, with abutting start/end values, then I want >the new table to have only one entry representing the full span of >time. Null 'done' just means it's not done yet. ... >Can anyone think of a way I can do this in postgres? I think you would be better off feeding the log out of the database into a perl script. SQL is a set-oriented language that can't do this sort of thing without some appalling contortions, if at all. (I can't think of a way to do it.) So, store the data in PostgreSQL, but do procedural processing in a more appropriate language. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Let your light so shine before men, that they may see your good works, and glorify your Father which is in heaven." Matthew 5:16 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] PL/pgSQL "compilation error"
On 3/14/01, 4:28:03 PM, Josh Berkus <[EMAIL PROTECTED]> wrote regarding Re: [SQL] PL/pgSQL "compilation error": > Tim, > > Hello all - I apologize for the newbie-esque question, but the debug output > > from postgres when you have a bug in your PL/pgSQL procedure is none to > > detailed. > It's an improvement over the query parser errors -- at least in > PL/pgSQL you get a line number! Although in your case, the line number > is deceptive. Line numbers always are deceptive when it comes to bracketing/quotes/structure. And you can get line-numbers on a query if you stick it in a text-file and use psql -f. > This brings up an important point. We have a medium-large user base for > PL/pgSQL out there, but it appears that Jan Wieck no longer has time to > develop the language ... nor should he be the sole developer. Howe do > we find more developers to expand & extend PL/pgSQL? I'd be willing to > contribute financially, but can't afford to actuall hire somebody on my > own (and don't have the moxie to doe the programming!). There are four issues with plpgsql: 1. features (not too bad, fine for most trigger functions, wouldn't want to write an OS in it). 2. error messages (could be better, although it might be that I'm just not used to them yet) 3. documentation (someone was doing a plpgsql cookbook - is (s)he still about?) 4. quoting literal strings (drives me up the *~!%&ing wall - have you tried putting a literal string in an execute?) This is partly growing pains with PG I'd guess, look at the changes since 6.early - PG is moving into the bigtime (I can remember Ingres from University days :-) I'd be loath for plpgsql to get carried away with functionality. I like Perl a lot, but I'm not sure I want to kick off 4M of interpreter every time someone inserts on a view. I'm happy to do my bit on the documentation side, but I don't want to end up duplicating the cookbook - does anyone know the status of this? I'd guess 90% of plpgsql functions will be on the same "theme" as something in a cookbook so it's the perfect format for learning/cut & paste. > > I've created the following procedure and am getting an error when I try to > > update the table. The error is something like "parse error near ; on line 50". > > Line 50 is the last line. > Actually, your question is not newbie-esque. That's quite a > sophisticated procedure! To be honest Josh, that was my opinion. > THe problem is simple. PL/pgSQL at this time does not support the "ELSE > IF" structure. Thus, the compiler is looking for more "END IF"s that it > doesn't find, and errors out when it gets to the end of the procedure > without seeing them. Missed that completely - reading my expectations, not the code. - Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] need to join successive log entries into one
>> Original Message << On 3/14/01, 5:24:12 PM, George Young <[EMAIL PROTECTED]> wrote regarding [SQL] need to join successive log entries into one: > I have a table like: > run | seq | start| done > 1415|261| 2001-01-29 12:36:55| 2001-02-07 13:02:38 > 1415|263| 2001-02-14 07:40:04| 2001-02-15 16:05:04 > 1415|264| 2001-02-16 16:05:05| 2001-03-08 16:34:03 > 1415|265| 2001-03-08 16:34:04| > This is a log of some operations done on some runs. 'seq' is the step > within the run. > I need to produce a new table that coalesces immediately successive > operations on a run into one, e.g.: > run | start | done > 1415| 2001-01-29 12:36:55| 2001-02-07 13:02:38 > 1415| 2001-02-14 07:40:04| > 1747| 2001-02-15 09:14:39| 2001-03-01 09:02:39 > 1747| 2001-03-05 13:13:58| > 1954| 2001-02-02 20:55:39| 2001-03-02 10:17:15 Try: select run,min(start),max(done) from mytable group by run; - Richard Huxton ---(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] pl/Perl
On 3/14/01, 5:04:49 PM, Jie Liang <[EMAIL PROTECTED]> wrote regarding Re: [SQL] pl/Perl : > Tom, I'm not the organ grinder, but maybe the monkey can help ;-) > 1.Where or how I can get pltcl.so? I have not find this file anywhere in > my > source except a pltcl.c. Did you set the relevant parameter with ./configure? Something like --with-tcl probably --help will give a list of options. > 2.Dose installation same as plpgsql? > i.e. > CREATE FUNCTION pltcl_call_handler () RETURNS OPAQUE AS > '/usr/local/pgsql/lib/pltcl.so' LANGUAGE 'C'; I just use the "createlang" command-line tool. > CREATE TRUSTED PROCEDURAL LANGUAGE 'pltcl' > HANDLER pltcl_call_handler > LANCOMPILER 'PL/pgtcl'; > 3.Where I can find more doc about pltcl? There's a bit in the user-manual (developer's version - 7.1 - anyway) on the website. Actually - there seems to be a break in the "Next" links in my local copy of the docs. Is that just me? Try a google search for tcl/tk - should turn up plenty of general stuff on the language. I don't use it myself, but it seems great for simple projects. - Richard Huxton > Thanks for your time. > Jie LIANG ---(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] PIVOT of data
Hi, I have a table like this: location| numbrochures | marketing ---+--+--- 101 Asheville, NC |4 | NEWS 101 Asheville, NC |1 | TV 101 Asheville, NC |3 | RADIO 101 Asheville, NC |2 | OTHER 101 Asheville, NC |3 | null 101 Asheville, NC |1 | TV 102 'Charlotte', 'NC' |1 | SIGN 104 'Colfax', 'NC'|5 | SIGN 109 'Moyock', 'NC'|1 | BROCHURE (9 rows) I want the headings to be like: location | NEWS | TV | RADIO | OTHER | How to get this done using sql for postgresql backend? TIA, Sri. __ Do You Yahoo!? Yahoo! Auctions - Buy the things you want at great prices. http://auctions.yahoo.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] need to join successive log entries into one
On Wed, 14 Mar 2001, you wrote: > On 3/14/01, 5:24:12 PM, George Young <[EMAIL PROTECTED]> wrote regarding [SQL] > I need to join successive log entries into one: > > I have a table like: > > > run | seq | start| done > > 1415|261| 2001-01-29 12:36:55| 2001-02-07 13:02:38 > > 1415|263| 2001-02-14 07:40:04| 2001-02-15 16:05:04 > > 1415|264| 2001-02-16 16:05:05| 2001-03-08 16:34:03 > > 1415|265| 2001-03-08 16:34:04| > > > This is a log of some operations done on some runs. 'seq' is the step > > within the run. > > > I need to produce a new table that coalesces immediately successive > > operations on a run into one, e.g.: > > > run | start | done > > 1415| 2001-01-29 12:36:55| 2001-02-07 13:02:38 > > 1415| 2001-02-14 07:40:04| > > 1747| 2001-02-15 09:14:39| 2001-03-01 09:02:39 > > 1747| 2001-03-05 13:13:58| > > 1954| 2001-02-02 20:55:39| 2001-03-02 10:17:15 > > Try: > > select run,min(start),max(done) from mytable group by run; Alas, this combines *all* entries for a given run, not just those that are imediately adjacent (in time, or by 'seq' number)... -- George Young, Rm. L-204[EMAIL PROTECTED] MIT Lincoln Laboratory 244 Wood St. Lexington, Massachusetts 02420-9108(781) 981-2756 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] need to join successive log entries into one
On Wed, Mar 14, 2001 at 09:17:33PM +, Richard H wrote: > > > I need to produce a new table that coalesces immediately successive > > operations on a run into one, e.g.: > > > run | start | done > > 1415| 2001-01-29 12:36:55| 2001-02-07 13:02:38 > > 1415| 2001-02-14 07:40:04| > > 1747| 2001-02-15 09:14:39| 2001-03-01 09:02:39 > > 1747| 2001-03-05 13:13:58| > > 1954| 2001-02-02 20:55:39| 2001-03-02 10:17:15 > > Try: > > select run,min(start),max(done) from mytable group by run; select run,min(start),max(done) from mytable group by run UNION ALL select run,start,NULL from mytable where done is null; Assuming that any given run has only one open step. Ross ---(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] PIVOT of data
Srikanth Rao wrote: >Hi, >I have a table like this: > location| numbrochures | marketing >---+--+--- > 101 Asheville, NC |4 | NEWS > 101 Asheville, NC |1 | TV > 101 Asheville, NC |3 | RADIO > 101 Asheville, NC |2 | OTHER > 101 Asheville, NC |3 | null > 101 Asheville, NC |1 | TV > 102 'Charlotte', 'NC' |1 | SIGN > 104 'Colfax', 'NC'|5 | SIGN > 109 'Moyock', 'NC'|1 | BROCHURE >(9 rows) > > >I want the headings to be like: > >location | NEWS | TV | RADIO | OTHER | > >How to get this done using sql for postgresql backend? SELECT location, CASE WHEN marketing = 'NEWS' THEN numbrochures ELSE NULL END AS "NEWS", CASE WHEN marketing = 'TV' THEN numbrochures ELSE NULL END AS "TV", ... but it's a clumsy hack and won't work if you don't know the contents of "marketing" in advance. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Let your light so shine before men, that they may see your good works, and glorify your Father which is in heaven." Matthew 5:16 ---(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] Re: psql win32 version
The URL shown below can not get through. Would you please check again. Thank You JACK - Original Message - From: "Marc Cromme" <[EMAIL PROTECTED]> To: "'datactrl'" <[EMAIL PROTECTED]> Sent: Wednesday, March 14, 2001 6:58 PM Subject: RE: psql win32 version > Yes - check > > http://www.zeos.dn.ua/download/ipgsql-latest.zip > > > your's Marc Cromme > > > -Original Message- > From: datactrl [mailto:[EMAIL PROTECTED]] > Sent: 12. marts 2001 02:19 > To: [EMAIL PROTECTED] > Subject: psql win32 version > > > Is there a win32 version for psql? > > JACK > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] PL/pgSQL "compilation error"
> I'm happy to do my bit on the documentation side, but I don't want to end > up duplicating the cookbook - does anyone know the status of this? I'd > guess 90% of plpgsql functions will be on the same "theme" as something > in a cookbook so it's the perfect format for learning/cut & paste. I am told that the cookbook has been merged into the 7.1beta docs. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Re: psql win32 version
This one worked a minute ago:- http://www.zeos.dn.ua/download/ipgsql-1.6.2.zip On Thu, 15 Mar 2001 14:14, datactrl wrote: > The URL shown below can not get through. Would you please check again. -- Sincerely etc., NAME Christopher Sawtell CELL PHONE 021 257 4451 ICQ UIN45863470 EMAIL csawtell @ xtra . co . nz CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz -->> Please refrain from using HTML or WORD attachments in e-mails to me <<-- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] PIVOT of data
Probably worth noting that this could be normalized into at least 3 tables from what I can tell. Tim On Wed, Mar 14, 2001 at 11:03:01PM +, Oliver Elphick wrote: > Srikanth Rao wrote: > >Hi, > >I have a table like this: > > location| numbrochures | marketing > >---+--+--- > > 101 Asheville, NC |4 | NEWS > > 101 Asheville, NC |1 | TV > > 101 Asheville, NC |3 | RADIO > > 101 Asheville, NC |2 | OTHER > > 101 Asheville, NC |3 | null > > 101 Asheville, NC |1 | TV > > 102 'Charlotte', 'NC' |1 | SIGN > > 104 'Colfax', 'NC'|5 | SIGN > > 109 'Moyock', 'NC'|1 | BROCHURE > >(9 rows) > > > > > >I want the headings to be like: > > > >location | NEWS | TV | RADIO | OTHER | > > > >How to get this done using sql for postgresql backend? > > SELECT location, >CASE WHEN marketing = 'NEWS' > THEN numbrochures > ELSE NULL >END AS "NEWS", >CASE WHEN marketing = 'TV' > THEN numbrochures > ELSE NULL >END AS "TV", >... > > but it's a clumsy hack and won't work if you don't know the > contents of "marketing" in advance. > > -- > Oliver Elphick[EMAIL PROTECTED] > Isle of Wight http://www.lfix.co.uk/oliver > PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 > GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C > > "Let your light so shine before men, that they may see > your good works, and glorify your Father which is in > heaven." Matthew 5:16 > > > > ---(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 -- Founder - PHPBuilder.com / Geocrawler.com Lead Developer - SourceForge VA Linux Systems ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] PIVOT of data
Srikanth, Aside from seconding Tim (your database is *not* normalized), the PIVOT function is only available one of 2 ways: SQL-ONLY, FIXED COLUMN: If you know your categories in advance, you can write a complex view using outer joins, sub-selects, and aggregates. Imagine that we know your types of marketing to be limited to TV, NEWS and POSTER: SELECT client_address, tv_totals.totaltv, news_totals.totalnews, poster_totals.totalposter FROM clients LEFT OUTER JOIN ( SELECT client_address, sum(tv_spots) as totaltv FROM clients GROUP BY client_address ) AS tv_totals ON tv_totals.client_address = clients.client_address LEFT OUTER JOIN ( SELECT client_address, sum(news_ads) as totalnews FROM clients GROUP BY client_address ) AS news_totals ON news_totals.client_address = clients.client_address LEFT OUTER JOIN ( SELECT client_address, sum(posters) as totalposters FROM clients GROUP BY client_address ) AS poster_totals ON poster_totals.client_address = clients.client_address; DYNAMIC METHOD: This requires an external programming language, and I'm not going to write it for you! However, pick you favorite procedural language (tcl, perl, or C) and write the following: 1. Do an select distinct to obtain each value in the column you want to use in the headers. Load this series into an array. 2. Dynamically generate query text, adding a column and a LEFT OUTER JOIN as above into the query for each value in your array. This method is a lot of work for both you and the server. Why do you think that MS Access often crashes on a large "crosstab" query? -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
