RE: [SQL] large going giving errors.
What happens when you don't join quite so much? That is, take off the AND blah blocks, one by one? -Robby -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of [EMAIL PROTECTED] Sent: Wednesday, June 13, 2001 3:38 PM To: [EMAIL PROTECTED] Subject: [SQL] large going giving errors. Hi I have a largeish join that is giving me an error.. here's the query SELECT o.name,o,contactname,o.contactphone,o.fax,o.emailaddy,o.freetelephone,o.addr ess1,o.address2,uw.id,m.manufacturer,i.type,uw.model,uw.color,uw.size,uw.pri ce FROM user_wantads AS uw,organisations AS o,users AS u ,itemtypes AS i,itemmanufacturers AS m,provinces AS p,cities AS ct,countries AS cy WHERE uw.owner = u.loginid AND u.belongsto = o.organisationid AND m.id=uw.manufacturer AND i.id=uw.itemtype AND o.provinceid=p.provinceid AND o.cityid=ct.cityid AND o.countryid=cy.countryid AND uw.id=9 and the error ERROR: copyObject: don't know how to copy 704 can anyone shed a little light please ? jeff. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] pl/pgSQL and escaping LIKE clauses
I'm having trouble getting LIKE clauses to work correctly inside a plpgSQL
function.
Here's my table:
id | val
+-
1 | hello
2 | there
3 | everyone
Here's my function:
CREATE FUNCTION intable(char)
RETURNS INTEGER
AS
'
DECLARE
input ALIAS FOR $1;
temp INTEGER;
BEGIN
SELECT INTO temp id FROM test WHERE val LIKE ''input%'';
RAISE NOTICE ''Value of temp is %'',temp;
RETURN temp;
END;
'
LANGUAGE 'plpgsql';
I should be able to SELECT('hello') and get back 1, correct?
No matter what I put in as a parameter, it always returns null.
If I change the LIKE clause to read "...LIKE ''hello%''" it does
in fact work. Or if I scrap the LIKE clause and have it
read something such as " id = input" (if input is an integer)
it also works fine.
Any thoughts?
Thanks,
Robby
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
RE: [SQL] "Display of specified number of records."
You want to just display records WHERE the field is BETWEEN 10 and 50? SELECT * FROM test WHERE testID BETWEEN 10 AND 50; If you want them to be ordered by the testID, just include a ORDER BY testID; See, isn't SQL a friendly language? :-) -Robby Hi, I have got following simple SQL. Select TestID from test where testname = ' ' order by testdate. Suppose for argument sake there are 100 records and testID's are 1 to 100. Is it possible to modify this SQL so that it will display records from 10 to 50 and not any other records. Rajesh. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(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] SQL question
Deepali, Bruce Momjian's book on SQL provides a great intro to SQL, including joins. http://www.ca.postgresql.org/docs/aw_pgsql_book/ If you have a specific question, please post that to a list Good luck! -Robby -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Deepali Agarwal Sent: Friday, July 06, 2001 12:53 PM To: '[EMAIL PROTECTED]' Subject: [SQL] SQL question hello, I have a simple question about nested SQL statements. I remember having learnt of another way of writing nested SQL statements, using Joins I guess. I'm buildingh an ASP/ADO application or which using nested select becomes too complicated. Could you please throw some light ( preferably with an example) on how i can use a join or any other method instead of suing nested SQL statements. Also, would you be able to tell me how to use a nested DQL quesry/join query with an ADO command object, i.e the commandtext property? Thank you for your help. -Deepali Deepali Agarwal Intern Project Performance Corporation 7600, Colshire Drive McLean, VA 22101 (703)748-7089 www.ppc.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl ---(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] Hey! ORDER BY in VIEWS?
I think PostgreSQL allows you to do an ORDER BY in a view, but the real message is that it just doesn't make any sense. Remember that a view is just a "virtual table", not a query. If you "order by" as part of it's definition, there's no guarantee that the data will be orded when you SELECT FROM later on. Always, always, always include an ORDER BY clause in every select you do. (I personally think SQL ought to REQUIRE it!) -Robby -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Josh Berkus Sent: Sunday, July 15, 2001 12:22 PM To: [EMAIL PROTECTED] Subject: [SQL] Hey! ORDER BY in VIEWS? Tom, Stephan, Hey! I thought you couldn't do ORDER BY in views ... yet I just did. Is this a new thing, or am I just getting my Trasact-SQL and my PostgreSQL mixed up again? -Josh __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
RE: [SQL] Hey! ORDER BY in VIEWS?
Josh: You wondered: >What happens if I put an ORDER BY in a view, then call an ORDER BY in a >query, e.g.: > >CREATE VIEW test_view AS >SELECT client_name, city, zip FROM clients >WHERE zip IS NOT NULL >ORDER BY zip; > >SELECT * FROM test_view ORDER BY city; > >Does the second ORDER BY override or suppliment the view ORDER BY, or is >it ignored? I think this question falls into the "Don't ask, don't tell" category of computer related questions. I can't think of a reason to design this way, the behavior isn't specified or sensible, so just don't do it! Sorry if I'm over-admonishing. Curiosity killed the cat. -Robby ---(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] Unknown values in int8 fields?
I've not been following this too closely but it sounds like you are
trying to COPY records from table A to table B, where the table B
also includes a serial value.
Here's an easy trick which I'm pretty sure will work: instead of
using COPY use SELECT INTO. It's much slower but I think it will
do the trick.
Good luck! HTH.
-Robby
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of Chris Ruprecht
Sent: Sunday, July 15, 2001 9:06 PM
To: [EMAIL PROTECTED]
Subject: [SQL] Unknown values in int8 fields?
Hi Chris,
When I load records with copy from ... And one of the fields (last
one) is left empty, I want the default of nextval('sequence_table') to kick
in - but it doesn't.
Now, the field with a unique index on it stays blank. No big deal if I could
go and say 'update rate set recno = nextval('sequence_rate') where recno = 0
- but it's not that easy as the recno is not 0 but - hmm what? What can I
check for? I tried '?' and ? And 0 and ... And ... And ... But nothing works
... Now what?
Best regards
Chris
_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
---(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 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] First steps in plpgsql - language not recognized?
Chris, sounds like you haven't called 'createlang plpgsql database-name' at the comand prompt. Try executing this and see if it works. -Robby -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Chris Ruprecht Sent: Tuesday, July 17, 2001 8:49 AM To: p-sql Subject: [SQL] First steps in plpgsql - language not recognized? Hi all, I have tried (and failed) my first steps in Pl/PgSQL. This must be real simple (like a 'create language' or something) but I don't (yet) know how to do this and maybe someone has a quick answer for me here. I get this (I'm sure it's a clear case of RT(F)M, but there are so many Ms to R, I don't know where to begin): [postgres@chill-025 postgres]$ psql -f x.sql phones psql:x.sql:1: ERROR: RemoveFunction: function 'testfunc(int4)' does not exist psql:x.sql:9: ERROR: Unrecognized language specified in a CREATE FUNCTION: 'plpgsql'. Recognized languages are sql, C, internal, and created procedural languages. The first error is ok - the error in line 9 is what I'm worried about. The code is what's in the tutorial, no biggy ;). Best regards and thanks for the help, Chris _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(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] PL/PGSQL and external (flat ASCII) files - Urgent ... :)
The hack and slash Perl programmer in me says--- if you only plan to do this once, (like importing data), then just write a Perl script that *generates* SQL code that does your inserts, updates, or whatever. You can then execute thsi with psql -f filename. But if you're trying to use flat files programmtically...(aren't you using a database to avoid flatfiles altogether? :-) hope that helps, robby -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Chris Ruprecht Sent: Tuesday, July 17, 2001 3:29 PM To: p-sql Subject: [SQL] PL/PGSQL and external (flat ASCII) files - Urgent ... :) Importance: High Hi all, I need to know how I can access a flat file from within a PL/PGSQL script. I have an Oracle PL/SQL book here and they refer to FOPEN(...), FCLOSE(...) but these functions don't seem to exist in PL/PGSQL.. What can I do instead? I checked the Programmer's Guide to Procedural Languages (PostGreSQL 7.1.2 and 7.2) but there is no info on it. Best regards, Chris _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
RE: [SQL] Fuzzy matching?
Here's an off the cuff reply:
It sounds like fuzzy_match(str1,str2,num) is
really just a tokenizer-type operation. The number is exactly
one less than the potential number of string segments
that you are interested in. For example:
fuzzy_match('Thornton','Tornton',1) = TRUE
Because the two segements are 'T' and 'ornton'
And also:
fuzzy_match('Thornton','Torntin',2) = TRUE
Becuse the three segments are 'T', "ornt', and 'n'
So, it seems like you could try to build the tokens,
which would be probably more efficient than just trying
all permutations.
HTH
-Robby
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of Josh Berkus
Sent: Tuesday, July 31, 2001 11:05 AM
To: [EMAIL PROTECTED]
Subject: [SQL] Fuzzy matching?
Folks,
For many of my programs, it would be extremely useful to have some form
of "fuzzy matching" for VARCHAR fields. There are two kinds of fuzzy
matching for words that I know of:
1. Phonetic matching, which would be nice but will have to wait for
someone's $100,000 project;
2. Textual mathcing, which I will outline below.
The way textual fuzzy matching should work is as follows:
The developer supplies two VARCHARs to match and a number/percent of
character mis-match that is acceptable:
Fuzzy_match('Thornton','Tornton',1)
And the fuzzy_match should return True if the two phrases are no more
than that number of characters different. Thus, we should get:
fuzzy_match('Thornton','Tornton',1) = TRUE
fuzzy_match('Thornton','Torntin',1) = FALSE
fuzzy_match('Thornton','Torntin',2) = TRUE
Unfortunately, I cannot think of a way to make this happen in a function
without cycling through all the possible permutations of characters for
both words or doing some character-by-character comparison with
elaborate logic for placement. Either of these approaches would be very
slow, and completely unsuitable for column comparisons on large tables.
Can anyone suggest some shortcuts here? Perhaps using pl/perl or
something similar?
Grazie!
-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 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
RE: [SQL] Simple Insert Problem
Gonzo: You need to make sure that you delimit your values correctly. To insert text fields (which may contain spaces) use 'single quotes'. You'll also want to enter date fields the same way. So, you should try INSERT INTO OP (op_num,op_name,start_time) VALUES (5400,'Welding','06:00:00'); Of course, you're probably using the "time" data type, which means that 6:00:00 really means 6:00 in the morning! Hope that helps! -Robby -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Gonzo Rock Sent: Monday, August 06, 2001 7:18 PM To: [EMAIL PROTECTED] Subject: [SQL] Simple Insert Problem Sorry but this is making me crazy... yes... I'm way new to SQL Why would this error out ?? This is the Query... INSERT INTO OP (op_num,op_name,start_time) Values (5400,Welding,06:00:00); And this is the pgSQL error... ERROR: parser: parse error at or near ":" The table has reasonable values in it already... I'm just adding a few more rows by hand. Thanks All, ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
RE: [SQL] Delete coloumn
Unfortunately, there's no easy way to delete a column in a table in PostgreSQL. The standard SQL syntax is: ALTER TABLE tablename DROP COLUMN columnname; But I repeat, this is NOT supported in postgresql. If you really need to delete a column you can always just create a new table with an identical definition but WITHOUT the offending column, and then SELECT INTO it. Example: CREATE TABLE sample ( id INTEGER, data TEXT, badcolumn DATE ); Now to delete the bad column table: CREATE TABLE sample_copy ( id INTEGER, data TEXT); and then copy it all over: SELECT id,data INTO sample_copy FROM sample; and then you can DROP TABLE sample; If you need the original table name, repeat the process of creating a new table now and copying the data over. Hope that helps! -Robby Slaughter -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Suhadi Sent: Monday, August 06, 2001 11:16 PM To: SQL Subject: [SQL] Delete coloumn Please send to me how to delete coloumn in SQL. Thank's ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
RE: [SQL] Delete Trigger Issue
Let me make sure I get this right: CREATE TABLE table1 ( field1 varchar(64), ... ); CREATE TABLE table2 ( field2 varchar(64), ... ); and you want that whenever a row is deleted from table1 you want the SAME row to be deleted from table2? here's what you want. First, a trigger: CREATE TRIGGER update_table2 BEFORE DELETE ON table1 FOR EACH ROW EXECUTE PROCEDURE update_table2_proc(); That trigger will make sure that each time a row is deleted from table1, the proceudre update_table2_proc will be called. And here is that procedure CREATE FUNCTION update_table2_proc() RETURNS opaque AS 'BEGIN DELETE FROM table2 WHERE field2 = new.field1; RETURN new; END;' LANGUAGE 'plpgsql'; That procedure just DELETEs all the rows in table2 that match up to field1 in the first table. Of course, you might want to do a broader LIKE matching if they are really VARCHAR fields. Hope that helps! -Robby Slaughter -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Sundararajan Sent: Tuesday, August 07, 2001 12:16 AM To: [EMAIL PROTECTED] Subject: [SQL] Delete Trigger Issue I am developing a db application in postgresql and i need to write a delete trigger on one of the tables. the environment is table1 field1 varchar(64) other fields. table 2. field1 varchar(64) other fields I need a delete trigger on the table 1, so that if I delete a row from table 1 , the corresponding rows from table 2 should also be deleted. This is the code I have tried. DROP FUNCTION ApplicationsDeleteFn(); CREATE FUNCTION ApplicationsDeleteFn() RETURNS OPAQUE AS ' BEGIN delete from ports where appName=OLD.appName; RETURN OLD; END; ' LANGUAGE 'plpgsql'; Please help me with this, as my work is time bound.Even if the trigger is written is SQL Thanks sundar ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 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] Sequential select queries...??
>Hello,
>At first I thought what I was trying to do was simple and could be done
>easily - but alas, I've spent way too much time and could not figure out
>how to get the results in question.
I think there's cause for excitement, because unless I'm interpreting you
incorrectly, it IS easy to do what you want to do:
Use the UNION functionality. If your table T is this
id name description
-
1 abc def
2 bcd abc
3 def ghi
4 jkl bcd
5 hij hij
And you do this:
SELECT id FROM T WHERE name = 'abc'
UNION
SELECT id FROM T WHERE desc = 'abc';
You'll get:
id
1
2
That ought to do it for you!
Hope this helps,
Robby Slaughter
---(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] Simple SQL-syntax
Fredrik, Just for future reference, it's generally a good idea to include a complete table schema and some sample data for your tables when asking this kind of question. But I think I know what you mean and will try to help you. >Suppose I have Table A looking something like this: >IndexTextNrA >And Table B like this: >NrANrB >Then I want to change all occurences of NrA in Table A to NrB... First of all, your syntax isn't quite right. It's UPDATE tablename SET value = value WHERE [conditions]; There's no FROM clause in an update statement. And second, you need to be careful to make sure all your tokens make sense. You had an extra "B" in the middle of your statement. Based on this: UPDATE tableA,tableB SET tableA.NrA = tableB.NrB WHERE tableA.NrA = tableB.NrB should achieve the desired result. Hope this helps! -Robby Slaughter ---(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
