Re: [SQL] Need your help
Jie Liang wrote: > e.g. > Try: > > CREATE TABLE emp ( > id int4 primary key, > empname text, > salary int4, > last_date datetime, > last_user name); > > CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS > BEGIN >update emp set last_date=''now''::timestamp where id=NEW.id; > RETURN NEW; > END; > ' LANGUAGE 'plpgsql'; > > CREATE TRIGGER emp_stamp BEFORE UPDATE ON emp > FOR EACH ROW EXECUTE PROCEDURE emp_stamp(); Not sure if it works this way at all, but the update on emp table is definitely a wasted scan. And the 'now'::timestamp will be evaluated at the first function call - never again; not sure if he wanted that behaviour either. CREATE FUNCTION emp_stamp () RETURNS opaque AS ' BEGIN new.last_date := now(); RETURN new; END;' LANGUAGE 'plpgsql'; Is the correct trigger for this purpose. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
[SQL] SELECT DISTINCT problems
Hi there,
I'm having a lot of trouble with one sql statement and I wonder can you
help.
I My problem is the following...there are two field name in the the table
named LecturerName and Projectcode. Each ProjectCode vulue is unique within
the table but there can be many entries in the table with the same
LecturerName.
I would like to pull each distinct value of LecturerName with any one
corresponding ProjectCode value, it does not matter what the ProjectCode
value is, but all attemps at this have failed so far.
I have tried this but it does not work:
Set rsLecturers = Server.CreateObject("ADODB.Recordset")
sqlLect = "Select LecturerName, MIN(ProjectCode) from tblSuggestions WHERE
LecturerName IN ( SELECT DISTINCT LecturerName FROM tblSuggestions)"
rsLecturers.Open sqlLect, Conn, 3, 3
I get this error when I try to run this:
[Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that
does not include the specified expression 'LecturerName' as part of an
aggregate function.
I am trying to put the results of this query into a recordset and I am using
an accessdatabase
Thanks in advance,
Kevin.
Re: [SQL] SELECT DISTINCT problems
SCAHILL KEVIN <[EMAIL PROTECTED]> writes:
> I have tried this but it does not work:
> Set rsLecturers = Server.CreateObject("ADODB.Recordset")
> sqlLect = "Select LecturerName, MIN(ProjectCode) from tblSuggestions WHERE
> LecturerName IN ( SELECT DISTINCT LecturerName FROM tblSuggestions)"
> rsLecturers.Open sqlLect, Conn, 3, 3
You seem to be trying to re-invent the notion of GROUP BY. The correct
way to write this sort of query in SQL is
Select LecturerName, MIN(ProjectCode) from tblSuggestions GROUP BY LecturerName
This gives you one output row for each distinct value of LecturerName,
and within that row the MIN() aggregates over all the original rows that
have that LecturerName. See
http://www.postgresql.org/devel-corner/docs/postgres/query-agg.html
http://www.postgresql.org/devel-corner/docs/postgres/queries.html#QUERIES-GROUP
regards, tom lane
Re: [SQL] SELECT DISTINCT problems
From: "SCAHILL KEVIN" <[EMAIL PROTECTED]>
> I would like to pull each distinct value of LecturerName with any one
> corresponding ProjectCode value, it does not matter what the ProjectCode
> value is, but all attemps at this have failed so far.
>
> I have tried this but it does not work:
>
> Set rsLecturers = Server.CreateObject("ADODB.Recordset")
> sqlLect = "Select LecturerName, MIN(ProjectCode) from tblSuggestions WHERE
> LecturerName IN ( SELECT DISTINCT LecturerName FROM tblSuggestions)"
> rsLecturers.Open sqlLect, Conn, 3, 3
>
> I get this error when I try to run this:
> [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query
that
> does not include the specified expression 'LecturerName' as part of an
> aggregate function.
The "min()" function means this is what access refers to as a "totals
query". Try something like:
SELECT LecturerName,min(ProjectCode) FROM tblSuggestions GROUP BY
LecturerName
Basically, anything that is not min() or max()ed should be mentioned in the
GROUP BY.
You might need to quote "LecturerName" (like that) etc since they are
mixed-case. On the other hand the ODBC might deal with all that for you.
- Richard Huxton
[SQL] Updatable Views
Tom, A while back you posted a rather intimidating e-mail regarding updatable views. Thanks to some reading (Fabian Pascal) I understand what you were talking about. My question is, are updatable views implemented in 7.1, or is this still vaporware? And, for that matter, when is 7.1 expected to be out of beta? -Josh Berkus P.S. Let me suggest again that you let us application developer-types field the newbie questions so that you can focus on the hard issues and developing the database engine. -- __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
[SQL] Re: [GENERAL] Debug messages in beta5
I am adding the DEBUG messages to the open items list. > Maybe this will help in evaluating beta5. Here is some debug output > from serverlog while the system was doing a restore to 7.1b5 from a > pg_dump file created on a 7.0.3 box: > > DEBUG: MoveOfflineLogs: remove 003D > DEBUG: MoveOfflineLogs: remove 003E > DEBUG: MoveOfflineLogs: remove 003F > DEBUG: MoveOfflineLogs: remove 0040 > DEBUG: MoveOfflineLogs: remove 0041 > DEBUG: MoveOfflineLogs: remove 0036 > DEBUG: MoveOfflineLogs: remove 0037 > DEBUG: MoveOfflineLogs: remove 0038 > DEBUG: MoveOfflineLogs: remove 0039 > DEBUG: MoveOfflineLogs: remove 003A > DEBUG: MoveOfflineLogs: remove 003B > DEBUG: MoveOfflineLogs: remove 003C > DEBUG: copy: line 25300, XLogWrite: new log file created - try to > increase WAL_FILES > DEBUG: copy: line 57362, XLogWrite: new log file created - try to > increase WAL_FILES > DEBUG: copy: line 27109, XLogWrite: new log file created - try to > increase WAL_FILES > DEBUG: copy: line 2978, XLogWrite: new log file created - try to > increase WAL_FILES > DEBUG: MoveOfflineLogs: remove 0044 > DEBUG: MoveOfflineLogs: remove 0045 > DEBUG: MoveOfflineLogs: remove 0046 > DEBUG: MoveOfflineLogs: remove 0042 > DEBUG: MoveOfflineLogs: remove 0043 > > I'm not sure where/how to change WAL_FILES. Is that a compiled in > default or a configuration setting? Is the MoveOfflineLogs anything to > worry about? > > These were interesting too: I'm not sure what to make of them: > > NOTICE: Adding missing FROM-clause entry for table "cont_group_link" > ERROR: Rule WHERE condition may not contain references to other > relations > NOTICE: Adding missing FROM-clause entry for table "cont_group_link" > > Here's what the original schema definition for cont_group_link looks > like: > > create table cont_group_link ( > - > -- Primary Keys:org_id, cont_id, group_owner, group_name > -- Foreign Keys:org_id, cont_id into cont_cont > >org_id int, >cont_id int, >crt_by int4, >group_name varchar, >unique (org_id, cont_id, crt_by, group_name), > >foreign key (org_id) > references cont_org > on update cascade > on delete cascade, > >foreign key (org_id, cont_id) > references cont_cont > on update cascade > on delete cascade, > >foreign key (crt_by, group_name) > references cont_group > on update cascade > on delete cascade > ); > > And here's a view definition that the messages are probably related to: > > drop view cont_group_v; > drop view cont_group_link_v; > --CV: > create view cont_group_v as select *, oid as _oid from cont_group where > not (access = 'none' and crt_by != getpguid()); > > create rule cont_group_v_insert as on insert to cont_group_v > do instead > insert into cont_group >(crt_by, group_name, group_type, descr, access) > values >(getpguid(), new.group_name, new.group_type, new.descr, > new.access); > > create rule cont_group_v_delete as on delete to cont_group_v > do instead > delete from cont_group > where crt_by = old.crt_by > and group_name = old.group_name; > > create rule cont_group_v_update as on update to cont_group_v > do instead > update cont_group > set group_name = new.group_name, group_type = new.group_type, > descr = new.descr, access = new.access > where crt_by = old.crt_by > and group_name = old.group_name; > > create view cont_group_link_v as select *, oid as _oid from > cont_group_link where cont_group_priv(crt_by,group_name,'r'); > > create rule cont_group_link_v_innull as on insert to cont_group_link_v > do instead nothing; > create rule cont_group_link_v_insert as on insert to cont_group_link_v > where cont_group_priv(crt_by,group_name,'w') > do instead > insert into cont_group_link > (org_id, cont_id, crt_by, group_name) > values (new.org_id, new.cont_id, getpguid(), new.group_name); > > create rule cont_group_v_link_denull as on delete to cont_group_link_v > do instead nothing; > create rule cont_group_v_link_delete as on delete to cont_group_link_v > where cont_group_priv(crt_by,group_name,'w') > do instead > delete from cont_group_link > where org_id = old.org_id > and cont_id = old.cont_id > and crt_by = old.crt_by > and group_name = old.group_name; > > > > This seems to lack sufficient context to find exactly where they came > from: > ERROR: Rule WHERE condition may not contain references to other > relations > > ERROR: select rule's target entry 27 has different type from attribute > lquant > > However, based on the field name, I can tell the last one is coming from > this view:
[SQL] plpgsql notify trigger
[postgres 7.0.2, x86 linux] I am trying to use a trigger to perform an sql 'notify' command. I do something like: CREATE FUNCTION run_changed() RETURNS opaque AS ' declare nm text; begin nm := NEW.run_name; notify nm return null; end; ' LANGUAGE 'plpgsql'; create trigger run_changed_tr after update on runs for each row execute procedure run_changed(); BUT, when I update the table, I get: ERROR: parser: parse error at or near "$1" It looks like the *name* (or it's alias here: $1.run_name), not the *value* of the variable nm, is passwd to the notify command. Since notify only takes a name, not a string, I don't see how to proceed. Is there some way in plsql to construct a string and have it executed in sql? disappointed in plsql, George -- George Young, Rm. L-204[EMAIL PROTECTED] MIT Lincoln Laboratory 244 Wood St. Lexington, Massachusetts 02420-9108(781) 981-2756
Re: [SQL] mysql's "replace into..."
Jeff, > I'm in the process of migrating a production database and related > applicatoins from mysql to postgresql. I've just not been very impressed > with mysql's stability, that is I'm trying to make my phone stop ringing. Surprising. I have a number of gripes with MySQL (of which REPLACE INTO is one), but stability isn't one of them. > However, when I wrote the applications, I took free advantage of mysql's > replace into feature which behaves like a smart insert that inserts new > records or updates old records. I'm using perl DBI and need to figure out a > good way to migrate this to postgresql. > > Any ideas? Well, you have two choices. One is to get used to standard SQL and use the SQL I will expound below. The other is to actually hire a C programmer to modify the PostgreSQL source to support Replace Into. Great Bridge might be willing to help. Regardless, your SQL functionality requires the following steps: 1. All tables involved *must* have primary keys. I reccommend against using the OID for this purpose for several reasons; see the archives for a discussion. 2. Build an if-then stucture for your update calls, that goes like this (this assumes that your primary key is SERIAL): IF primarykey_variable = 0 THEN INSERT INTO table1 (field1, field2, field3) VALUES (variable1, variable2, variable3); ELSE UPDATE table1 SET field1 = variable1, field2 = variable2, field3 = variable3 WHERE primarykey = primarykey_variable; END IF; -Josh Berkus -- __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
[SQL] Re: Daily Digest V1 #282
> > create trigger run_changed_tr after update on runs for each row >execute procedure run_changed(); > > BUT, when I update the table, I get: >ERROR: parser: parse error at or near "$1" > > It looks like the *name* (or it's alias here: $1.run_name), not the *value* of the >variable nm, > is passwd to the notify command. Since notify only takes a name, not a string, > I don't see how to proceed. > > Is there some way in plsql to construct a string and have it executed in sql? > > disappointed in plsql, > George > I don't know much about notify, but I know you can build arbitrary SQL strings in PL/TCL. Have you tried that? I use a combination of plpgsql and pltcl in my implementation because each one has it strengths/weaknesses. begin:vcard n:Bateman;Kyle x-mozilla-html:FALSE org:Action Target Inc. adr:;; version:2.1 email;internet:[EMAIL PROTECTED] title:President x-mozilla-cpt:;0 fn:Kyle Bateman end:vcard
[SQL] int2+float8 problems
select trn_qty,amount2,trn_qty*amount2 from invo trn_qty INT2 amount2 FLOAT8 +-+-+--+ | trn_qty | amount2 | ?column? | +-+-+--+ | -1 |7678 | 7678 | what to get -7678 thanks
Re: [SQL] int2+float8 problems
"guard" <[EMAIL PROTECTED]> writes: > select trn_qty,amount2,trn_qty*amount2 from invo > trn_qty INT2 > amount2 FLOAT8 > +-+-+--+ > | trn_qty | amount2 | ?column? | > +-+-+--+ > | -1 |7678 | 7678 | What platform are you on, and how did you compile Postgres? Also, what Postgres version? Did you run the regression tests? regards, tom lane
Re: [SQL] plpgsql notify trigger
Hello George, Once, Thursday, March 01, 2001, 12:30:07 AM, you wrote: GY> [postgres 7.0.2, x86 linux] GY> I am trying to use a trigger to perform an sql 'notify' command. GY> I do something like: GY> CREATE FUNCTION run_changed() RETURNS opaque AS ' GY>declare GY> nm text; GY>begin GY>nm := NEW.run_name; GY>notify nm GY>return null; GY>end; GY> ' LANGUAGE 'plpgsql'; GY> create trigger run_changed_tr after update on runs for each row GY>execute procedure run_changed(); GY> BUT, when I update the table, I get: GY>ERROR: parser: parse error at or near "$1" GY> It looks like the *name* (or it's alias here: $1.run_name), not the *value* of the variable nm, GY> is passwd to the notify command. Since notify only takes a name, not a string, GY> I don't see how to proceed. GY> Is there some way in plsql to construct a string and have it executed in sql? GY> disappointed in plsql, GY> George Trigger function must return record, try "return NEW" instead of "return null" -- Best regards, Yury
[SQL] Re: Help needed -> ERROR: record arow has no field description
Hi all, Solved my own problem. I was just misreading the error message. It was actually TELLING me the problem (I was referencing a table with no field called "description" in the select, but trying to use it in the loop. Sorry for disturbing people. As an aside, this has motivated me to add a section on error messages into techdocs.postgresql.org (very messy at the moment, I'll fix it tonight). Regards and best wishes, + Justin Clift Database Administrator Justin Clift wrote: > > Hi all, > > I'm getting this error, which to me makes no sense. Running PostgreSQL > 7.0.3 on Mandrake 7.2 (compiled from source, not the rpms). > > The code is in a pl/pgsql function I am writing and I can't see why it's > complaining. > > This is the appropriate part of the code : > > > > arowrecord; > > > > BEGIN > > > > FOR arow IN select transaction_payments.cashback from > transaction_payments, payment_types where > transaction_payments.payment_type = payment_types.idnum LOOP > textbuf := text(arow.cashback); > textbuf := textcat(textbuf, '' ''); > insert into receipts (receipt_num, data) values (trans_num, > textbuf); > END LOOP; > > > > >From the PostgreSQL log file (debug is set to 2), I am getting : > > query: SELECT transaction_payments.cashback from transaction_payments, > payment_types where transaction_payments.payment_type = > payment_types.idnum > ERROR: record arow has no field description > DEBUG: Last error occured while executing PL/pgSQL function > create_receiptp3 > DEBUG: line 105 at assignment > AbortCurrentTransaction > > The "arow" record type variable is used quite a lot in previous FOR > loops in this function. This is the first FOR loop in the function that > uses more than one table though. I suspect this may have something to > do with it. > > As far as I can tell, this SQL statement is valid. Does anyone have any > ideas how to get this to work. I have tried several variations, and the > mailing lists don't even have a reference for this error message. > > Regards and best wishes, > > Justin Clift > Database Administrator
[SQL] Re: [PHP] novice question
Andy, I'm forwarding your e-mail to the PGSQL-SQL list, where I think you'll find more help on CONSTRAINTS. As I don't use them a lot, myself, I don't have a good answer for you. > i'm running postgresql on apache with php4. i am working with a > postgresql > table which was set up by an employee of mine who no longer works for > the > company. this employee set up constraints to the table, some of which > won't > work properly, ie. rejected due to CHECK constraint. > > how do i find out what the check constraints are currently? > how do i change the check constraints? > > i've located in the book postgresql by bruce momjian how to set up > the > constraints, but can't find how to find answers to the questions > above. any > help would be most appreciated. > > how can i count how many users are logged in at the current time? > does anyone know of a good novice user group for linux or php4? > andy arledge > www.neighborhoodsingles.com Hmmm ... Andy, I think you may have bitten off more than you can chew. While I bootstrapped my own knowledge of Linux and PostgreSQL, it took me over a year before I was comfortable administrating professionally ... and I haven't learned PHP yet! Unfortunately, I haven't found any one book that helped me with everything. For example, for Linux administration, I bought and read "Linux Administration for Beginners"(Osborne), "Linux Administration"(O'Reilly) and "Linux in a Nutshell" (O'Reilly). And I still hire specialists for complex networking. Good Luck! -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
Re: [SQL] int2+float8 problems
compile ./configure --prefix=/home/pgsql --enable-multibyte run Redhat 6.1 & 6.2 postgresql7.0.3 not run regression tests "Tom Lane" <[EMAIL PROTECTED]> ¼¶¼g©ó¶l¥ó [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > "guard" <[EMAIL PROTECTED]> writes: > > select trn_qty,amount2,trn_qty*amount2 from invo > > trn_qty INT2 > > amount2 FLOAT8 > > > +-+-+--+ > > | trn_qty | amount2 | ?column? | > > +-+-+--+ > > | -1 |7678 | 7678 | > > What platform are you on, and how did you compile Postgres? > Also, what Postgres version? Did you run the regression tests? > > regards, tom lane
Re: [SQL] int2+float8 problems
"guard" wrote: >select trn_qty,amount2,trn_qty*amount2 from invo > >trn_qty INT2 >amount2 FLOAT8 > >+-+-+--+ >| trn_qty | amount2 | ?column? | >+-+-+--+ >| -1 |7678 | 7678 | > >what to get -7678 This works in 7.1beta4. Perhaps you need to upgrade. -- 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 "The LORD is my shepherd; I shall not want. He maketh me to lie down in green pastures: he leadeth me beside the still waters, he restoreth my soul...Surely goodness and mercy shall follow me all the days of my life; and I will dwell in the house of the LORD for ever."Psalms 23:1,2,6
Re: [SQL] mysql's "replace into..."
Jeff, > Thanx for your help. I found replace into to be a nice convience since my > primary keys are often expressions build from several fields which I want to > keep unique. Replace into enforces this uniqueness. I was learning SQL > when I started this project so I didn't know this was non-standard. Bummer. No, what you're describing are called "composite keys". They are a standard part of SQL, but most developers end up using numerical surrogate keys because they are easier to handle. > But since my primary keys are expressions, I can't use the technique you > suggested. I'm thinking of simply trying the insert and checking to see if > I get an error. If I get an error, I do an update... Not sure how well > this will work, tho. Better to do it the other way. Search for that key; if it's returned, do an update; if the results are NULL, do an insert. If your table has few fields (<15) you can even do this through a function, passing the field values as parameters of the function. -Josh Berkus -- __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
