[GENERAL] can function arguments have the type tablename.columnname%TYPE?
Hi people, I'm writing a plpgsql function in pgadminIII and want to know if the arguments can have the following type: tablename.columnname%TYPE If so, how do I accomplish this in pgadminIII as it doesn't allow me to change the arguments to this type. The function I'm writing looks like this: CREATE OR REPLACE FUNCTION totnrchange(a table1.resnr%TYPE, b table1.fnr%TYPE, c table1.fdate%TYPE, d table1.dep%TYPE, e table1.arr%TYPE, f table1.nrdays%TYPE) RETURNS integer AS $BODY$DECLARE tot integer; BEGIN select count(resnr) into tot from table1 x where x.resnr=a and x.fnr=b and x.fdate=c and x.dep=d and x.arr=e and x.nrdays>=f group by x.resnr,x.fnr,x.fdate,x.dep,x.arr; return tot; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100; Brgds Dino
[GENERAL] last and/or first in a by group
Dear postgresql experts, I want to know if postgresql has facilities for getting the first and or the last in a by group. Suppose I have the following table: resnr,dep,arr,cls,dbd meaning reservationsnumber, departure station, arrival station, the class of the reservation and the daysbeforedeparture and records like: xxx,NYC,BRA,C,80 xxx,NYC,BRA,M,75 xxx,NYC,BRA,Q,50 yyy,WAS,LIS,T,55 zzz,NYC,LIS,Z,40 zzz,NYC,LIS,J,39 I want to select only the most recent records being: xxx,NYC,BRA,Q,50 yyy,WAS,LIS,T,55 zzz,NYC,LIS,J,39 How would you accomplish this? I googled and found this: http://www.postgresonline.com/journal/index.php?/archives/68-More-Aggregate-Fun-Whos-on-First-and-Whos-on-Last.html I hope there are alternatives because I don't have to program this myself. The other option is that I load this data in SAS and do it there but I only have SAS at work and want to do this at home. Brgds Dino
Re: [GENERAL] last and/or first in a by group
From: "Thomas Kellerer" To: "" Dino Vliet wrote on 16.05.2010 18:07: > Dear postgresql experts, > > I want to know if postgresql has facilities for getting the first and or > the last in a by group. > > Suppose I have the following table: > > resnr,dep,arr,cls,dbd meaning reservationsnumber, departure station, > arrival station, the class of the reservation and the > daysbeforedeparture and records like: > xxx,NYC,BRA,C,80 > xxx,NYC,BRA,M,75 > xxx,NYC,BRA,Q,50 > yyy,WAS,LIS,T,55 > zzz,NYC,LIS,Z,40 > zzz,NYC,LIS,J,39 > > I want to select only the most recent records being: > xxx,NYC,BRA,Q,50 > yyy,WAS,LIS,T,55 > zzz,NYC,LIS,J,39 > Something like this? SELECT * FROM your_table t1 WHERE dbd = (SELECT min(dbd) FROM your_table t2 WHERE t2.dep = t1.dep AND t2.arr = t1.arr AND t2.resnr = t1.resnr) Regards Thomas Thanks for your answer and if I look at it from a functionality point of view, this does the trick. However, my table t1 (and hence t2) contains 6 million records AND I'm planning to do this repeatedly (with a scripting language for various moments in time) so this will end up being a very slow solution. How can I speed these kind of queries up? By using indices, but on what columns would that be the best way then? Or by trying to do this one time by constructing a table with the relevant information which can be used in such a way that I join thing in stead of using this subquery construction. Thanks Dino
[GENERAL] How to improve: performance of query on postgresql 8.3 takes days
Dear postgresql list, I have some troubles generating data for a analysis task at hand. I have a table (table A) containing 5 million records and 28 number of attributes. This table is 461MB big if I copy it to a csv file. I want to create another table (table B) based on the contents of table A plus some 15 extra attributes (in pl/pgsql written functions which produce those extra attributes) So my statement looks like this: create tableB as ( select some attributes, function1(A.attribute1)as attributeX+1, function2(A.attribute1,A.Attribute2,A.attribute3,A.attribute4,A.attribute5) as attribute X+2..function15(A.attribute1,A.attribute9) as attributeX+15 from tableA as A) This takes almost 60 hours to finish on my database server running debian 5.0 with XFS as filesystem containing 4GB RAM. I'm using postgresql server version 8.3 (but am seeing the same phenomena on my FreeBSD 8.0 database server running postgresql 8.4 as well) I arrived at 15 functions because I had 7 or 8 joins in the past and saw that my disk was getting hid and I had heard someplace that RAM is faster so I rewrote those 7 or 8 joins as functions in pl/pgsql. They were just simple lookups, although some of the functions are looking stuff up in tables containing 78000 records. However, I thought this wouldn't be a problem because they are simple functions which look up the value of one variable based on a parameter. 3 of the more special functions are shown here: CREATE OR REPLACE FUNCTION agenttype1(a character) RETURNS integer AS $BODY$ DECLARE i integer; t1_rij canxagents%ROWTYPE; BEGIN select * into t1_rij from canxagents where agent = a; if NOT FOUND THEN i := 0; ELSE if t1_rij.aantal >= 0 and t1_rij.aantal <=499 THEN i := 1; ELSE if t1_rij.aantal > 500 and t1_rij.aantal <=1999 THEN i := 2; ELSE if t1_rij.aantal >= 2000 THEN i := 3; END IF; END IF; END IF; END IF; return i ; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100; CREATE OR REPLACE FUNCTION agenttype2(a character) RETURNS integer AS $BODY$ DECLARE i integer; t1_rij showagents%ROWTYPE; BEGIN select * into t1_rij from showagents where agent = a; if NOT FOUND THEN i := 0; ELSE if t1_rij.aantal >= 0 and t1_rij.aantal <=499 THEN i := 1; ELSE if t1_rij.aantal > 500 and t1_rij.aantal <=999 THEN i := 2; ELSE if t1_rij.aantal >= 1000 THEN i := 3; END IF; END IF; END IF; END IF; return i ; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100; CREATE OR REPLACE FUNCTION agenttype3(a character) RETURNS integer AS $BODY$ DECLARE i integer; t1_rij noagents%ROWTYPE; BEGIN select * into t1_rij from noagents where agent = a; if NOT FOUND THEN i := 0; ELSE if t1_rij.aantal >= 0 and t1_rij.aantal <=299 THEN i := 1; ELSE if t1_rij.aantal > 300 and t1_rij.aantal <=899 THEN i := 2; ELSE if t1_rij.aantal >= 900 THEN i := 3; END IF; END IF; END IF; END IF; return i ; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100; The interesting parts of my postgresql.conf file look like this: #-- # RESOURCE USAGE (except WAL) #-- # - Memory - shared_buffers = 512MB # min 128kB or max_connections*16kB # (change requires restart) temp_buffers = 8MB # min 800kB #max_prepared_transactions = 5 # can be 0 or more # (change requires restart) # Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory # per transaction slot, plus lock space (see max_locks_per_transaction). work_mem = 50MB # min 64kB maintenance_work_mem = 256MB# min 1MB #max_stack_depth = 2MB # min 100kB # - Free Space Map - max_fsm_pages = 153600 # min max_fsm_relations*16, 6 bytes each # (change requires restart) #max_fsm_relations = 1000 # min 100, ~70 bytes each # (change requires restart) # - Kernel Resource Usage - #max_files_per_process = 1000 # min 25 # (change requires restart) #shared_preload_libraries = '' # (change requires restart) # - Cost-Based Vacuum Delay - #vacuum_cost_delay = 0 # 0-1000 milliseconds #vacuum_cost_page_hit = 1 # 0-1 credits #vacuum_cost_page_miss = 10 # 0-1 credits #vacuum_cost_page_dirty =
[GENERAL] general questions postgresql performance config
Dear postgresql people, Introduction Today I've been given the task to proceed with my plan to use postgresql and other open source techniques to demonstrate to the management of my department the usefullness and the "cost savings" potential that lies ahead. You can guess how excited I am right now. However, I should plan and execute at the highest level because I really want to show results. I'm employed in the financial services. Context of the problem Given 25 million input data, transform and load 10 million records to a single table DB2 database containing already 120 million records (the whole history). The current process is done on the MVS mainframe while the SAS system is used to process the records (ETL like operations). The records of the two last months (so 20 million records) are also stored in a single SAS dataset, where users can access them through SAS running on their Windows PC's. With SAS PC's they can also analyse the historical records in the DB2 table on the mainframe. These users are not tech savvy so this access method is not very productive for them but because the data is highly valued, they use it without complaining too much. Currently it takes 5 to 6 hours before everything is finished. Proof of concept I want to showcase that a solution process like: input-->Talend/Pentaho Kettle for ETL-->postgresql-->pentaho report designer, is feasible while staying in the 5~6 hours processing and loading time. Input: flat files, position based ETL: Pentaho Kettle or Talend to process these files DBMS: postgresql 8 (on debian, opensuse, or freebsd) Reporting: Pentaho report wizard Hardware AMD AM2 singlecore CPU with 4GB RAM Two mirrored SATA II disks (raid-0) Now that I have introduced my situation, I hope this list can give me some tips, advice, examples, pitfalls regarding the requirements I have. Questions 1) Although this is not exactly rocket science, the sheer volume of the data makes it a hard task. Do you think my "solution" is viable/achievable? 2) What kind of OS would you choose for the setup I have proposed? I prefer FreeBSD with UFS2 as a filesystem, but I guess Debian with ext3 filesystems or openSUSE with ext3 or Ubuntu server with ext3 would all be very good candidates too?? 3) Would you opt for the ETL tools mentioned by me (pentaho and talend) or just rely on the unix/linux apps like gawk, sed, perl? I'm familiar with gawk. The ETL tools require java, so I would have to configure postgresql to not use all the available RAM otherwise risking the java out of memory error message. With that said, it would be best if I first configure my server to do the ETL processing and then afterwards configure it for database usage. 4) what values would you advice for the various postgresql.conf values which can impact performance like shared buffers, temp_buffers, sort_mem, etc etc? Or is this more of like an "art" where I change and restart the db server, analyze the queries and iterate until I find optimal values? 5) Other considerations? Thanks in advanced, Dino
[GENERAL] join two tables without a key
Hi postgresql list, If I have two tables with the same number of rows but different columns and I want to create one table out of them what would be the way to do that in postgresql? Table A has N number of rows and columns X,Y,Z and Table B has N number of rows and P,Q,R as columns. None of the tables have a column which can be used as a key. The resulting table should have N number of rows and X,Y,Z,P,Q,R as columns. 1) How to accomplish this is plain SQL? Join on rowid? 2) What would a PL-pgsql program look like to accomplish ths? 3) If N=1000 and the two tables have 45 columns with mixed integer and character values, what would be the most efficient approach (fastest) and why? Thanks in advanced
Re: [GENERAL] join two tables without a key
--- On Sat, 4/3/10, Raymond O'Donnell wrote: From: Raymond O'Donnell Subject: Re: [GENERAL] join two tables without a key To: "Dino Vliet" Cc: pgsql-general@postgresql.org Date: Saturday, April 3, 2010, 1:01 PM On 03/04/2010 11:16, Dino Vliet wrote: > Hi postgresql list, If I have two tables with the same number of rows > but different columns and I want to create one table out of them what > would be the way to do that in postgresql? > > Table A has N number of rows and columns X,Y,Z and Table B has N > number of rows and P,Q,R as columns. None of the tables have a > column which can be used as a key. > > The resulting table should have N number of rows and X,Y,Z,P,Q,R as > columns. How do the rows in the tables relate to each other? You need to decide first how you match the rows in A and B. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie Hi Ray, they don' t. It' s pure randomly generated data. Brgds
[GENERAL] managing kernel
My sysadmin has changed the values for shmall to 256MB (268435456) and shmmax to 1GB (1073741824) and I wonder if these values seem ok to you. We are working on a linux system (2.4 kernel) and have 2 GB RAM at our disposal. I have this large database running (15 million records). Are there any other parameters I should/could tweak for better performance? Best Regards Dino __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] managing kernel
OK, I have RTFM:-) SHMALL Total amount of shared memory available (bytes or pages) if bytes, same as SHMMAX; if pages, ceil(SHMMAX/PAGE_SIZE) These values should be the same. I've changed that now. Still my second question is valid though. Are there any other values I should consider changing in order to boost performance. I'm using postgresql 7.4. Brgds Dino --- Dino Vliet <[EMAIL PROTECTED]> wrote: > My sysadmin has changed the values for shmall to > 256MB > (268435456) and shmmax to 1GB (1073741824) and I > wonder if these values seem ok to you. We are > working > on a linux system (2.4 kernel) and have 2 GB RAM at > our disposal. I have this large database running (15 > million records). > > Are there any other parameters I should/could tweak > for better performance? > > Best Regards > Dino > > > > __ > Do you Yahoo!? > Yahoo! Mail - Helps protect you from nasty viruses. > http://promotions.yahoo.com/new_mail > > ---(end of > broadcast)--- > TIP 8: explain analyze is your friend > __ Do you Yahoo!? Plan great trips with Yahoo! Travel: Now over 17,000 guides! http://travel.yahoo.com/p-travelguide ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] reporting solution for postgresql
Hi folks, Maybe a little bit off topic, but I want to convince my managers of the value of opensource products and decided that I will reverse engineer the datawarehouse at my company. We are a small player and our datawarehouse solution is based on an ETL tool from Informatica, Oracle and Business Objects. I have already written the most ETL defenitions in Ruby. So I will manage to get the data in the postgresql database, in a star schema. However, to convince management I need a slick, opensource reporting tool to show the reports that already have been defined in Business Objects. Or I should try to use Business Objects through ODBC. Can anyone tell me about his/her findings in a similar approach? What decent but opensource Query tools exist today which can support this architecture (web based OR a client-server), where the clients MUST be windows 2000 because that's what they use at the office. Thanks in advance and hope I will get some good feedback (as I always do from this excellent list) Brgds Dino __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] /libexec/ld-elf.so.1: Shared object "libpq.so.4" not found
Hi folks, when I start pgadmin3 on my system I get the following error: /libexec/ld-elf.so.1: Shared object "libpq.so.4" not found, required by "pgadmin3" The only thing I have done lately is upgrade the database to 8.2 in the freebsd ports system. Pgadmin did work in the past, so what could be the problem? I have a amd64 system running freebsd version 6.1. I haven't had the time to check out the postgresql database I had backed up. Will I experience problems because of this? Hope somebody can help me out, brgds Yahoo! Music Unlimited Access over 1 million songs. http://music.yahoo.com/unlimited
[GENERAL] why can't I increase shared buffers to higher value?
I have the following config but increasing the shared buffers to a value greater then 32 doesn't let the database server start (I want a value of 256MB there because I will have a giant table of 12 million rows which will be qeuried extremely). I have a 3GB RAM amd64 system running freebsd 6.1 with: Maintenance_work_mem is 32MB Max_stack_depth is 3MB Shared_buffers is 32MB Temp_buffers is 8MB Work_mem is 32MB Max_fsm_pages is 204800 Max_connections is 3 And I am doing this: sysctl -w kern.ipc.shmmax=1954311424 sysctl -w kern.ipc.shmall=16384 Whay is my shared buffer value not increasing? Hope you can help me out or give me a few tips. O yeah, I'm using version 8.2.3. Thanks Have a burning question? Go to www.Answers.yahoo.com and get answers from real people who know.
[GENERAL] relations betwee subclasses in postgersql
Hi all, I'm using postgresql 7.4.2 and could not create relationships between subclases. I hava a EER model where the superclass Person has subclasses like Customers. When I try to create a relationship between Customers and other relations the database won't let me. Is that correct and if so, when will this be fixed? Brgds Dino Vliet __ Do you Yahoo!? Check out the new Yahoo! Front Page. www.yahoo.com ---(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
[GENERAL] frustrated by plpgsql procedure
Hi guys, I trying for days to get this simple plpgsql procedure to run but I keep getting this error: psql:pgsql_procedure.txt:15: ERROR: syntax error at or near at character 17 QUERY: copy cancel TO $1 with delimiter as ',' null as '.' CONTEXT: SQL statement in PL/PgSQL function doedit near line 12 psql:pgsql_procedure.txt:15: LINE 1: copy cancel TO $1 with delimiter as ',' null as '.' psql:pgsql_procedure.txt:15: The source code of this plpgsql procedure is: create or replace function doedit() returns void AS $$ /* Procedure to create textfile from database table. */ DECLARE i integer := 340; start date :='2004-08-06'; eind date :='2004-08-12'; location varchar(30) :='/usr/Data/plpgtrainin'; BEGIN create table cancel as (SOME QUERY); location := location || i || '.txt' ::varchar(30); raise notice 'location is here %', location; copy cancel TO location with delimiter as ',' null as '.' ; END $$ Language plpgsql; Can somebody tell me why my location variable is NOT working as expected? I would like to use it in a loop to create multiple text files which names would be different because of the way I concatenate it with the looping variable. Hope somebody can help me because it's a big frustration. Thanks in advanced. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] frustrated by plpgsql procedure
Nope:-( I added it just now and still the same error message!! --- Bricklen Anderson <[EMAIL PROTECTED]> wrote: > Dino Vliet wrote: > > Hi guys, > > > > I trying for days to get this simple plpgsql > procedure > > to run but I keep getting this error: > > > > psql:pgsql_procedure.txt:15: ERROR: syntax error > at > > or near at character 17 > > QUERY: copy cancel TO $1 with delimiter as ',' > null > > as '.' > > CONTEXT: SQL statement in PL/PgSQL function > doedit > > near line 12 > > psql:pgsql_procedure.txt:15: LINE 1: copy cancel > TO $1 > > with delimiter as ',' null as '.' > > psql:pgsql_procedure.txt:15: > > > > The source code of this plpgsql procedure is: > > > > create or replace function doedit() returns void > AS $$ > > /* Procedure to create textfile from database > table. > > */ > > > > DECLARE > > i integer := 340; > > start date :='2004-08-06'; > > eind date :='2004-08-12'; > > location varchar(30) :='/usr/Data/plpgtrainin'; > > > > BEGIN > > create table cancel as (SOME QUERY); > > location := location || i || '.txt' ::varchar(30); > > raise notice 'location is here %', location; > > copy cancel TO location with delimiter as ',' null > as > > '.' ; > > END > > $$ Language plpgsql; > > > Missing semi-colon after END? > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] frustrated by plpgsql procedure
Ok thanks for now. I understood the problem and what I should do to fix it. Will try that later. Thanks for all the tips and the REALLY FAST answers!! --- John DeSoi <[EMAIL PROTECTED]> wrote: > > On Mar 27, 2006, at 2:10 PM, Dino Vliet wrote: > > > Can somebody tell me why my location variable is > NOT > > working as expected? I would like to use it in a > loop > > to create multiple text files which names would > be > > different because of the way I concatenate it with > the > > looping variable. > > You can't just stick an arbitrary string in the > middle of a SQL > statement. You can build a SQL statement and then > run it with EXECUTE. > > Try something like this: > > create or replace function doedit() returns varchar > AS $$ > /* Procedure to create textfile from database table. > */ > > DECLARE > i integer := 340; > start date :='2004-08-06'; > eind date :='2004-08-12'; > location varchar(30) :='/usr/Data/plpgtrainin'; > > BEGIN > create table cancel as (SOME QUERY); > location := location || i || '.txt' ::varchar(30); > raise notice 'location is here %', location; > execute 'copy cancel to ' || location || ' with > delimiter as \',\' > null as \'.\''; > return location; > END; > $$ Language plpgsql; > > > Also note you must have super user access to use > COPY, so it still > might fail if you don't have the right privileges. > > > > > John DeSoi, Ph.D. > http://pgedit.com/ > Power Tools for PostgreSQL > > > ---(end of > broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] execute in pl/pgsql
Hi, In my pl/pgsql procedure I have the following line: execute 'copy cancella to ' || location || ' with delimiter as \',\''; Location is a string that gives the absolute pathname. It's obvious what I'm aiming for, but this still gives an error because of the two '' I'm missing around the location var. That's why I'm getting this error message: NOTICE: location is hier /usr/local/pgsql/data/plpgtrainin10.txt ERROR: syntax error at or near "/" at character 18 QUERY: copy cancella to /usr/local/pgsql/data/plpgtrainin10.txt with delimiter as ',' CONTEXT: PL/pgSQL function "perform" line 16 at execute statement How can I solve it? I've tried a few things but keep getting this error. Thanks __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] execute in pl/pgsql
Solved:-) Thanks for your aanswer, now I can go further with exploring pl/pgsql. Looks nice. Brgds --- Tom Lane <[EMAIL PROTECTED]> wrote: > Dino Vliet <[EMAIL PROTECTED]> writes: > > In my pl/pgsql procedure I have the following > line: > > > execute 'copy cancella to ' || location || ' with > > delimiter as \',\''; > > quote_literal(location) would work much better and > more safely. > > regards, tom lane > > ---(end of > broadcast)--- > TIP 9: In versions below 8.0, the planner will > ignore your desire to >choose an index scan if your joining column's > datatypes do not >match > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] can postgresql handle these large tables
Hi folks, I'm busy with analyzing some data and therefore will have to store 2 big tables containing 50 million obs & 25 million obs. Selecting some interesting obs can decrease these tables with maybe a factor 10 but eventually I will have to combine them using a join. These tables contain almost 20 colums and after selecting the appropriate ones I will have maybe 10 colums. Off course I will have to make use of indexes and try to optimize the settings in postgresql.conf file like: geqo=true geqo_treshold=11 geqo_effort= 1 geqo_generations=5 But my main question is whether postgresql will be able to handle these large volumes of data, or should I ask whether my hardware will be capable of working with these large tables. Answers, Ideas, remarks...everything is welcome folks, Many thanks in advance, it is MUCH appreciated what is done in this list!! __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] help with trigger
Hi folks, I'm new to PostgreSQL and am busy tring to work with it. Of pl/pgsql I know even less and that's the part I have a question on right now. I have this nice example to get me started with pl/pgsql... I have a table with the schedule of a service my sport team wants to offer: Table Schedule name length startdate enddateday soccer 4 01-sep-2004 30-sep-2004 Mon tennis 4 01-sep-2004 30-sep-2004 Wed This means, that I want to offer soccer weekly, for a total of 4 weeks as from 1st sep till 30 sep on Mondays! Now, what I do want is a second table that creates all the dates on which there will be soccer training if I finish entering this record in table Schedule. So, then the other table, called ScheduledDates has the follwing records: Table ScheduleDates nameoccurrence date soccer 1 06-sep-2004 soccer 2 13-sep-2004 soccer 3 20-sep-2004 soccer 4 27-sep-2004 Can someone help me with writing this as a trigger in pl/pgsql? Another variant is that the trigger starts when the table Schedule is populated, but it not just automatically creates the 4 records as described above, but because the date 20-sep-2004 is a national holiday and is in table Exceptions, the sheme just shifts one up and the occurrence 4 becomes occurrence 3 and the last schedule date becomes monday the 4th of october! I hope you can help me with this one, will put me way up the learning curve of pl/pgsql __ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] job for sql, pl/pgsql,gawk,perl or ??
Dear all, I have this problem with getting information out of my database and I don't know if it is my knowledge of sql or that this is something that can't be done in sql. I have the following table: id fdateprod pricestat nr_items sdate x1 23-11-2003123 456 yes 7 01-11-03 x1 23-11-2003123 456 may 4 07-11-03 x1 23-11-2003123 400 yes 7 14-11-03 x2 29-11-2003201 711 no 6 01-11-03 x2 29-11-2003133 700 no 6 08-11-03 Here, you can see that id x1 is interested in product 123 that we offer for 456 euro's and that he's sure and wants 7 items. Then almost 6 days afterwards he mails us to change his order to 4 items and after we arrange a price reduction. For various reasons I sometimes want only these customers and reason as follows: "Give me the id's of persons wo start with a status="yes" and end with a status="yes". Then I can track so called "doubters". How to do this in postgresql? So basically I want to be able to scan a file, order it by id,sdate and the want to look at the field stat. If I see that the contents of the field changes from yes to something else and then back to yes, I select the id. Other positives would be stat started from no and ending in yes. The total number of changes an id can have varies. Can this be done in sql? Or is this a pl/pgsql task? In that case, what should I think of? Another option for me is to output a textfile and try to do it in gawk or perl or something else. __ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] help with trigger
Maybe I've missed it but can someone plese help me with this? Brgds and thanks in advance, --- Dino Vliet <[EMAIL PROTECTED]> wrote: > Hi folks, > I'm new to PostgreSQL and am busy tring to work with > it. Of pl/pgsql I know even less and that's the part > I > have a question on right now. I have this nice > example > to get me started with > pl/pgsql... > > I have a table with the schedule of a service my > sport > team wants to offer: > > Table Schedule > name length startdate enddateday > soccer 4 01-sep-2004 30-sep-2004 > Mon > tennis 4 01-sep-2004 30-sep-2004 > Wed > > This means, that I want to offer soccer weekly, for > a > total of 4 weeks as from 1st sep till 30 sep on > Mondays! > > Now, what I do want is a second table that creates > all > the dates on which there will be soccer training if > I > finish entering this record in table Schedule. So, > then the other table, called ScheduledDates has the > follwing records: > > Table ScheduleDates > nameoccurrence date > soccer 1 06-sep-2004 > soccer 2 13-sep-2004 > soccer 3 20-sep-2004 > soccer 4 27-sep-2004 > > Can someone help me with writing this as a trigger > in > pl/pgsql? > > Another variant is that the trigger starts when the > table Schedule is populated, but it not just > automatically creates the 4 records as described > above, but because the date 20-sep-2004 is a > national > holiday and is in table Exceptions, the sheme just > shifts one up and the occurrence 4 becomes > occurrence > 3 and the last schedule date becomes monday the 4th > of > october! > > I hope you can help me with this one, will put me > way > up the learning curve of pl/pgsql > > > > > __ > Do you Yahoo!? > New and Improved Yahoo! Mail - 100MB free storage! > http://promotions.yahoo.com/new_mail > __ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] error: insert has more expressions than target column
MUCH better nowI did manage to get an insert into the table lessons with these adjustments...BUT now it seems the FOR LOOP didn't work because I only get 1 record and expected that I would get 8 records due to the i variabele. What could be wrong? My code is now: CREATE FUNCTION vulalles() RETURNS trigger AS ' BEGIN FOR i in 0..7 LOOP INSERT INTO lessons (..) SELECT dayofweek,startdate + (i*7), enddate + (i*7),...; RETURN NEW; END LOOP; END; ' LANGUAGE plpgsql; --- Richard Huxton <[EMAIL PROTECTED]> wrote: > Dino Vliet wrote: > > I'm getting the same error without brackets. > > Check the columns in table "lessons" matches the > columns in your select. > > > The EXECUTE statement was because I read something > > about executing dynamic content. > > > > I want to add 7 days to the date value of > startdate > > and want to repeat it every week. Because there > are 8 > > weeks I choose to do that with the for loop going > from > > 0 to 7. > > Looking closer, I can see the problem. You're > treating the column from > the select as a variable (which it isn't). > > Try something like: > > INSERT INTO lessons (col_name1, col_name2, ...) > SELECT dayofweek, startdate + (i*7), endate + (i*7), > startime, ... > > -- >Richard Huxton >Archonet Ltd > __ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail ---(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
