[SQL] Object based Query Requirement.
Title: Object based Query Requirement. Hi, I use Postgres-7.2.4. and my client is in C++ I need to know the following: As I user libpq++ library, I have to pass the query in the string format only. i.e.’const char *’ Does Postgres support object based query? i.e. User should make the object of some query class (supported by Posstgres) and just sets predicates, fields, table names by invoking proper member functions of the same object. Then calls the member function like ‘executeQuery’ or so of the same object. Is there any way to do this? Thx., Anagha
[SQL] Join or Where?
Title: Urgent Help : Use of return from function/procedure. Hi,could anybody tell me what can be the difference between these queries?There are a big difference but I can't understand the reasons. For somereasons the firs query calculates getup column in a wrong way. It allwaysdevides with 10. (???) While the getupquantity column comes well.With the second query everything is ok.Thank you,-- Csaba---select (select getupquantity from t_products where id=productid) asgetupquantity, quantity, (select quantity/getupquantity) as getupfrom t_stockchanges, t_productswhere (getupquantity<>0)limit 30;RESULT: getupquantity | quantity | getup---+--+--- 10 | 100 | 10 10 | 10600 | 1060 15 | 15150 | 1515 13 | 13650 | 1365 17.5 | 17500 | 1750 5.75 | 5750 | 575 2.5 | 2500 | 250 1.5 | 1500 | 150 16 | 16000 | 1600 2 | 2000 | 200 5.5 | 5500 | 550 4.5 | 4500 | 450 2 | 2000 | 200---select t_products.getupquantity as getupquantity, quantity, (select t_stockchanges.quantity/t_products.getupquantity) as getupfrom t_stockchangesjoin t_products on (t_products.id=productid)where (getupquantity<>0)limit 30;RESULT:getupquantity | quantity | getup---+--+--- 10 | 100 | 10 10 | 10600 | 1060 15 | 15150 | 1010 13 | 13650 | 1050 17.5 | 17500 | 1000 5.75 | 5750 | 1000 2.5 | 2500 | 1000 1.5 | 1500 | 1000 16 | 16000 | 1000 2 | 2000 | 1000 5.5 | 5500 | 1000 4.5 | 4500 | 1000 2 | 2000 | 1000 Hi,could anybody tell me what can be the difference between these queries?There are a big difference but I can't understand the reasons. For somereasons the firs query calculates getup column in a wrong way. It allwaysdevides with 10. (???) While the getupquantity column comes well.With the second query everything is ok.Thank you,-- Csaba---select (select getupquantity from t_products where id=productid) asgetupquantity, quantity, (select quantity/getupquantity) as getupfrom t_stockchanges, t_productswhere (getupquantity<>0)limit 30;RESULT: getupquantity | quantity | getup---+--+--- 10 | 100 | 10 10 | 10600 | 1060 15 | 15150 | 1515 13 | 13650 | 1365 17.5 | 17500 | 1750 5.75 | 5750 | 575 2.5 | 2500 | 250 1.5 | 1500 | 150 16 | 16000 | 1600 2 | 2000 | 200 5.5 | 5500 | 550 4.5 | 4500 | 450 2 | 2000 | 200---select t_products.getupquantity as getupquantity, quantity, (select t_stockchanges.quantity/t_products.getupquantity) as getupfrom t_stockchangesjoin t_products on (t_products.id=productid)where (getupquantity<>0)limit 30;RESULT:getupquantity | quantity | getup---+--+--- 10 | 100 | 10 10 | 10600 | 1060 15 | 15150 | 1010 13 | 13650 | 1050 17.5 | 17500 | 1000 5.75 | 5750 | 1000 2.5 | 2500 | 1000 1.5 | 1500 | 1000 16 | 16000 | 1000 2 | 2000 | 1000 5.5 | 5500 | 1000 4.5 | 4500 | 1000 2 | 2000 | 1000
[SQL] Change the behaviour of the SERIAL "Type"
Hi all, I am struggling hard with a badly written piece of code. It has such statements all over the place: INSERT INTO TABLE A (NULL, Value1, Value2...). It was written for MySQL, which can take NULL and then assign an auto_increment. However, in PostgreSQL I am getting problems, because it would not let me insert NULL into a NOT NULL column (which is perfectly sensible from my point of view). But as the author has also left out the column list in the insert, its really tedious to change the code. Therefore I was thinking whether it was possible to change the meaning of SERIAL in my DB, so that it would actually generate a Trigger on the table, which would then take care of the SERIAL value. (Getting nextval from the sequence and write all into the table) This way, there would be no possibility to circumvent the Value which comes from the Sequence. To me, this would make more sense anyway than just setting the DEFAULT of a coulmn (and as far as I understood, this is what SERIAL does, right?) Is there a way to change SERIAL this way? Cheers, Dani ---(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] Change the behaviour of the SERIAL "Type"
On Thu, Jun 26, 2003 at 14:31:34 +0200, Dani Oderbolz <[EMAIL PROTECTED]> wrote: > > It was written for MySQL, which can take NULL and then assign an > auto_increment. > However, in PostgreSQL I am getting problems, because it would not let > me insert NULL > into a NOT NULL column (which is perfectly sensible from my point of view). > But as the author has also left out the column list in the insert, its > really tedious to change > the code. You can use the keyword DEFAULT instead of NULL and it will do what you want. > This way, there would be no possibility to circumvent the Value which > comes from the Sequence. You can use a unique constraint to enforce uniqueness. > Is there a way to change SERIAL this way? Well you can certainly write your own trigger to do this. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Join or Where?
On Thursday 26 Jun 2003 12:21 pm, Együd Csaba wrote: > Urgent Help : Use of return from function/procedure.Hi, > could anybody tell me what can be the difference between these queries? > There are a big difference but I can't understand the reasons. For some > reasons the firs query calculates getup column in a wrong way. It allways > devides with 10. (???) While the getupquantity column comes well. > > With the second query everything is ok. 1 > select (select getupquantity from t_products where id=productid) as 2 > getupquantity, 3 >quantity, 4 >(select quantity/getupquantity) as getup 5 > from t_stockchanges, t_products 6 > where (getupquantity<>0) 7 > limit 30; I don't think the t_products in the first line is the same as that in the fifth line - that'd surely mess up your values. -- Richard Huxton ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] contrib/tsearch - chopped words?
Hello list, I have followed the examples in README.tsearch about converting text columns to txtidx type using the function txt2txidx() . Problem is.. some words become chopped. "Database for dummies" becomes 'dummi' 'databas' , for example. I also created a trigger to update the txtidx column and it does the same thing. Thanks - bruce __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Change the behaviour of the SERIAL "Type"
Wow, I had never actually faced this problem (yet) but I spied it as a possible stumbling block for porting MySQL apps, for which the standard practice is inserting a NULL. As I have made a fairly thorough reading of the docs (but may have not cross-correlated every piece of data yet, obviously), I was surprised to find I hadn't figured this out myself. It /seems/ obvious in retrospect, but it really baked my noodle when I first looked at some ugly MySQL queries. Respectfully, then, I move that a sentence outlining this functionality be added to User Manual section 5.1.4, "The Serial Types." Furthermore, anyone who has written or is writing a MySQL porting guide should include this, if he hasn't. Best, Randall On Thursday, June 26, 2003, at 08:49 AM, Bruno Wolff III wrote: On Thu, Jun 26, 2003 at 14:31:34 +0200, Dani Oderbolz <[EMAIL PROTECTED]> wrote: It was written for MySQL, which can take NULL and then assign an auto_increment. However, in PostgreSQL I am getting problems, because it would not let me insert NULL into a NOT NULL column (which is perfectly sensible from my point of view). But as the author has also left out the column list in the insert, its really tedious to change the code. You can use the keyword DEFAULT instead of NULL and it will do what you want. This way, there would be no possibility to circumvent the Value which comes from the Sequence. You can use a unique constraint to enforce uniqueness. Is there a way to change SERIAL this way? Well you can certainly write your own trigger to do this. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Temporary table performance?
Hello everyone, In the Intranet application I've been building over the past 9 months, I have developed quite a beast of a search. I've asked for help here in the past on performance tuning it, and have come to the conclusion that I've gotten it as tweaked as I can without seeking the counsel of a shamen. What I'm looking at doing is improving in-page performance. In a nutshell, how expensive is it to create a temporary table? I'd like to do something like: CREATE TEMP TABLE SearchResults AS [..]; I could then do some queries against the temporary table without having to regenerate the results every time I want to show a "Page 299 of 500" toolbar. Would I be better off just sucking this data into an in-memory data structure, or can I use a temp table as an easy-to-use alternative? -- /* Michael A. Nachbaur <[EMAIL PROTECTED]> * http://nachbaur.com/pgpkey.asc */ ...[Arthur] leapt to his feet like an author hearing the phone ring... ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Temporary table performance?
> I could then do some queries against the temporary table without having to > regenerate the results every time I want to show a "Page 299 of 500" toolbar. Performance wise temp tables are the same as a regular table but without WAL on the table contents. > Would I be better off just sucking this data into an in-memory data structure, > or can I use a temp table as an easy-to-use alternative? You are probably better off using a cursor. -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
[SQL] Rules: passing new/old to functions
How to pass the special variables new / old to functions? e.g. I'd like to to something like this (v is a view): create funtion f1(v, v) returns void as '...' create rule vupt as on update to v do instead select f1(new, old) While accepting this definitions, pg says it can not handle "whole-tuple references" at runtime. using: select f(new) from newdoesn't work either: pg says relation new doesn't exist. Since I need to handle tables with more than 20 columns, it would be uggly if I had to pass every single value to the function as follows: select f1(new.1, ..., new.n, old.1, ..., old.n) create function f1(typeof 1, ..., typeof n, typeof 1, ..., typeofn) I'd be glad for any kind of suggestion. regards, Gunter -- +++ GMX - Mail, Messaging & more http://www.gmx.net +++ Bitte lächeln! Fotogalerie online mit GMX ohne eigene Homepage! ---(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] Informing end-user of check constraint rules
> Am Sonntag, 22. Juni 2003 14:45 schrieb [EMAIL PROTECTED]:
>> I have not used column check constraints before, but I'd like to
>> start using then and so would I'll like to know if there is a direct
>> way to provide feedback to the end user about data validation rules
>> expressed in column check constraints?
>>
> you can use a trigger on insert and write your own error handling
> function like below. then you have everything at one place. I think
> postgres should have better ways to report errors but i am not a
> database guru and dont know how other databases do their error
> handling.
>
I came up with what I think is a pretty cool implementation idea, as
follows.
-- 1) Define a table as
CREATE TABLE regular_expression (
description character varying(48) NOT NULL,
regular_expression character varying(128),
user_message text
) WITHOUT OIDS;
-- with sample data:
INSERT INTO regular_expression VALUES ('Social Security Number',
'^\\d{3}-\\d{2}-\\d{4}$', 'Social Security Number must consist of the
pattern: nnn-nn-, where "n" is a digit.');
INSERT INTO regular_expression VALUES ('US Telephone Number',
'^[2-9]\\d{2}-\\d{3}-\\d{4}', 'US Telephone numbers must consist of the
pattern aaa-eee-, optionally followed by extra extension number,
where aaa is the three-digit area code, eee is the three digit exchange
code, and is the four digit number.');
INSERT INTO regular_expression VALUES ('Internet E-Mail Address',
'[a-z0-9_]+([-.][a-z0-9_]+)[EMAIL PROTECTED]([-.][a-z0-9_]+)+',
'Internet E-Mail Addresses are typically of the form [EMAIL PROTECTED],
where, "n" is the user name, "dd" is the Internet domain name,
and "ttt" is the three character top-level domain name.');
INSERT INTO regular_expression VALUES ('US ZIP Code',
'^\\d{5}-\\d{4}|\\d{5}$', 'US Postal ZIP Codes are of the form n or
n-, where "n" is any digit.');
COMMENT ON TABLE regular_expression IS 'This table defines regular
expressions used in the application.';
-- 2) Define the following function to be used as a generic CHECK
constraint:
CREATE FUNCTION public.check_pattern(varchar, varchar) RETURNS bool AS '
DECLARE
l_value ALIAS FOR $1;
l_pattern ALIAS FOR $2;
l_row RECORD;
BEGIN
IF l_value IS NOT NULL THEN
IF EXISTS(SELECT 1 FROM public.regular_expression WHERE
UPPER(description) = UPPER(l_pattern)) THEN
SELECT INTO l_row regular_expression, user_message FROM
public.regular_expression WHERE UPPER(description) =
UPPER(l_pattern);
IF NOT (l_value ~ l_row.regular_expression) THEN
RAISE EXCEPTION ''Invalid %. %'', l_pattern, l_row.user_message;
END IF;
END IF;
END IF;
RETURN TRUE;
END;
' LANGUAGE 'plpgsql' VOLATILE;
-- 3) Define any check constraint you want similar to:
CREATE TABLE person (
e_mail_address varchar(128),
social_security_no varchar(11),
--[...other column defs...]
CONSTRAINT person_e_mail_address CHECK (check_pattern(e_mail_address,
'Internet E-Mail Address')),
CONSTRAINT person_social_security_no CHECK
(check_pattern(social_security_no, 'Social Security Number'))
) WITHOUT OIDS;
The only hitch I've run into so far is that when I want to do an ALTER
TABLE to ADD a CHECK CONSTRAINT this way when there is existing data, I
need to temporarily CREATE OR REPLACE the check_pattern function with the
RAISE EXCEPTION line commented out because for some reason that
particular exception gets raised in the process of trying to add the
constraint, and so the ADD CONSTRAINT command fails. After the constraint
is successfully added with the function's RAISE EXCEPTION call commented
out, then I re-CREATE OR REPLACE the check_pattern function with the
RAISE EXCEPTION line restored.
Seems to work great in preliminary testing.
~Berend Tober
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] UPDATE table SET col = (SELECT ...)
Hi, I found follwing email sent to you in internet. I am interested in same issue myself. Did you finally found out any more information about how to Update a column of a table by selecting value from another table? Hello,If I understand the SQL references I have read, UPDATEs are allowed topull data from SELECT statements, something like UPDATE table SET col1 = (SELECT val1 FROM table WHERE id = 34), SET col2 = (SELECT val2 FROM table WHERE id = 34) WHERE id = 35;However, the PostgreSQL parser chokes on the 'SELECT'. Thedocumentation says UPDATE table SET column = _expression_ [, ...] [ FROM fromlist ] [ WHERE condition ] _expression_ = A valid _expression_ or value to assign to column.I don't know exactly how to interpret `valid _expression_'. -- Eric Marsdenemarsden @ mail.dotcom.frIt's elephants all the way down
[SQL] Postgres - Delphi Application
Hi I' would like to connect my postgres database on a linux server with Delphi 5 Applications,which are located on Win95/98 Clients, very helpful would be an example on how to realize a connection of these things Thanks Murali
Re: [SQL] UPDATE table SET col = (SELECT ...)
On Wednesday 25 June 2003 11:00, Vangelis-Maria Tougia wrote: > Hi, > I found follwing email sent to you in internet. > I am interested in same issue myself. > Did you finally found out any more information about how to Update a column > of a table by selecting value from another table? Not sure if this has been answered previously, but here goes: > Hello, > > If I understand the SQL references I have read, UPDATEs are allowed to > pull data from SELECT statements, something like > > UPDATE table SET col1 = (SELECT val1 FROM table WHERE id = 34), >SET col2 = (SELECT val2 FROM table WHERE id = 34) ^^^ > WHERE id = 35; The second "SET" is not necessary or allowed and is causing the update to fail. Ian Barwick [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] Rules: passing new/old to functions
Gunter Diehl <[EMAIL PROTECTED]> writes: > create funtion f1(v, v) returns void as '...' > create rule vupt as on update to v do instead select f1(new, old) > While accepting this definitions, pg says it can not handle "whole-tuple > references" at runtime. FWIW, the "old" case works fine. "new" doesn't work so well because the new row hasn't been formed into a tuple at the point where the rule runs; it only exists as a list of variables. (This is a rather handwavy explanation, but I think it captures the key point.) While this could probably be fixed with some effort, I doubt it's going to happen soon. Is there any chance of doing what you want with a trigger instead of a rule? 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] TR: Like and =
In fact I'm trying to migrate a database from Informix IDS to Postgres. This IDS database uses CHAR so I just let the same. Now I'm conviced that I sould convert CHAR to VARCHAR. Thanks. > -Message d'origine- > De : [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] la part de Dani Oderbolz > Envoye : mercredi 25 juin 2003 14:19 > A : [EMAIL PROTECTED] > Objet : Re: [SQL] TR: Like and = > > > Nicolas JOUANIN wrote: > > >Hi, > > > > Thanks for your help. In fact that means 2 solutions for this: > > > >1) select * from pdi where rtrim(pdi) = '100058' > > > >or > > > >2) Use VARCHAR instead of CHAR > > > >I don't which is the best , but both are working. > > > >Nicolas. > > > > > > Do you have a specific reason why to use CHAR? > I use CHAR only for certain one-byte flags, and even there its use is > debatable. > I would use VARCHAR; if I was you. > > Cheers, > Dani > > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
