Re: [SQL] combination of function to simple query makes query slow
My Postgres version is 7.3.4 (on a central server, so I can't upgrade if that would be one of the suggestions...) It is indeed completely valid to make such a temporary table, but I need this function to help me automate some standard queries other people can make on the database. In other words: (1) the query has to be done in one go, instead of using 2 different selects, and (2) using the complete definition of the function in the query itself becomes pretty bloating, as this function can be used 3 or more times in a single query... I found out that it has probably something to do with Postgres not able to use an index scan on this function. Even though I ended up defining it as 'stable' or even 'immutable'. (Or I may be completely wrong, of course). I thought that making a function stable or immutable would make it available for an index search. Any additional suggestions? Thanks, jan. > -Original Message- > From: Jaime Casanova [mailto:[EMAIL PROTECTED] > Sent: 03 October 2005 20:22 > To: jan aerts (RI) > Cc: [email protected] > Subject: Re: [SQL] combination of function to simple query > makes query slow > > On 10/3/05, jan aerts (RI) <[EMAIL PROTECTED]> wrote: > > Some more information: > > > > An EXPLAIN of the following query > > my_db=> explain select m1.object1_id, m1.object2_id, > m2.object1_id, > > m2.object2_id my_db-> from c_mappings m1, c_mappings m2 my_db-> > > where m1.object1_id = 16575564 my_db-> and m2.object1_id > in (select > > aliases_of(m1.object2_id)); > > gives: > > QUERY PLAN > > > -- > > -- > > > > Nested Loop (cost=0.00..99746.00 rows=1170281 width=16) > > Join Filter: (subplan) > > -> Index Scan using ind_cmappings_object1_id on c_mappings m1 > > (cost=0.00..6.12 rows=2 width=8) > > Index Cond: (object1_id = 16575564) > > -> Seq Scan on c_mappings m2 (cost=0.00..36052.89 rows=1435589 > > width=8) > > SubPlan > > -> Result (cost=0.00..0.01 rows=1 width=0) > > (7 rows) > > > > All columns of c_mappings, as well as the columns that are accessed > > through the aliases_of function, as indexed. However, > notice how the > > second loop uses a "Seq Scan" instead of an "Index Scan". > > Is there a way to use an index scan on the results of a function? > > > > Thanks, > > jan. > > > > > what version is your postgres? > > what if you make temp table first? something like this: > > select * from c_mappings > where object1_id = 16575564 >into temp m1; > > select m1.object1_id, m1.object2_id, m2.object1_id, m2.object2_id > from m1, c_mappings m2 > where m2.object1_id in (select aliases_of(m1.object2_id)); > > just an idea... > > -- > regards, > Jaime Casanova > (DBA: DataBase Aniquilator ;) > ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] combination of function to simple query makes query slow
"jan aerts (RI)" <[EMAIL PROTECTED]> writes: > My Postgres version is 7.3.4 (on a central server, so I can't upgrade if > that would be one of the suggestions...) 7.3.4 has multiple known data-loss bugs and security issues. If you're dealing with someone who won't upgrade it, find someone else to deal with. At the very least they need to move to 7.3.10 (or as of today, 7.3.11). See http://developer.postgresql.org/docs/postgres/release-7-3-11.html and following pages for reasons why. > I thought that making a function stable or immutable would make it > available for an index search. Your problem isn't the function, it's the IN (SELECT ...) construct. 7.3 is not bright enough to optimize that. (Given that it's a correlated sub-SELECT, I'm afraid later releases aren't either :-(.) You need to find a way of expressing the query without that. My guess is that trying to use a function for this is counterproductive in itself; the table access that's going on inside the function needs to be exposed for optimization in order to get reasonable overall performance. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] using pg_tables and tablename in queries
I have a large number of tables with a common naming convention mytable001, mytable002, mytable003 ... mytable00n I would like to do a query across all of the tables, however I do not know all of the tables before hand, and I do not want to ( cant ) manually generate a query like select * from mytable001, mytable002, mytable003 I have a query that returns the names of the tables I want to query: select tablename from pg_tables where tablename like 'mytable%' I have successfully done this query before. I remember it used a odd syntax; I it was something like select * from ( select tablename from pg_tables where tablename like 'mytable%' ) However the above query returns just a listing of tables, I need to use its output in the FROM and treat the input as a table name. ( I've tried creating a Table Function that returns the above set and tried to use that in a select clause, but I cant get it to work. )
Re: [SQL] using pg_tables and tablename in queries
solarsail <[EMAIL PROTECTED]> writes: > I have a large number of tables with a common naming convention > mytable001, mytable002, mytable003 ... mytable00n > I would like to do a query across all of the tables, however I do not know > all of the tables before hand, and I do not want to ( cant ) manually > generate a query like > select * from mytable001, mytable002, mytable003 > I have a query that returns the names of the tables I want to query: > select tablename from pg_tables where tablename like 'mytable%' This looks to me like a situation in which you should rethink your data design. Those tables should all get merged into one big table, adding one extra column that reflects what you had been using to segregate the data into different tables. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] using pg_tables and tablename in queries
The current behavior is by design.
We use the table as a logging repository. It can get very large
250 000 records. Because of the large number of records that we
have in the table we found it was much faster to perform inserts on a
smaller table. Our current system rolls the tables over
every 12 hours or so, creating a new table with the following behavior:
CREATE TABLE mytable_temp {...}
ALTER TABLE mytable RENAME TO mytable_back_datetime; ALTER TABLE mytable_temp RENAME TO mytable;
I want to join the mytable_back_datetime tables together in order to
perform queries against my huge set of data to generate some
reports. I'm probably going to create a temporary table with a
few indexes to make the reports run faster... however I need to join
the tables all together first.
On 10/4/05, Tom Lane <[EMAIL PROTECTED]> wrote:
solarsail <[EMAIL PROTECTED]> writes:> I have a large number of tables with a common naming convention> mytable001, mytable002, mytable003 ... mytable00n
> I would like to do a query across all of the tables, however I do not know> all of the tables before hand, and I do not want to ( cant ) manually> generate a query like> select * from mytable001, mytable002, mytable003
> I have a query that returns the names of the tables I want to query:> select tablename from pg_tables where tablename like 'mytable%'This looks to me like a situation in which you should rethink your
data design. Those tables should all get merged into one big table,adding one extra column that reflects what you had been using tosegregate the data into different tables.regards,
tom lane
Re: [SQL] using pg_tables and tablename in queries
The current behavior is by design.
We use the table as a logging repository. It can get very large 250 000
records. Because of the large number of records that we have in the table we
found it was much faster to perform inserts on a smaller table. Our current
system rolls the tables over every 12 hours or so, creating a new table with
the following behavior:
CREATE TABLE mytable_temp {...}
ALTER TABLE mytable RENAME TO mytable_back_datetime;
ALTER TABLE mytable_temp RENAME TO mytable;
I want to join the mytable_back_datetime tables together in order to perform
queries against my huge set of data to generate some reports. I'm probably
going to create a temporary table with a few indexes to make the reports run
faster... however I need to join the tables all together first.
I would create a function that creates a string with a query that includes
all the tables you need, and call execute on the string. You would loop
through the all tables from pg_tables and keep on appending the table name
you need.
Regards,
Yasir
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] using pg_tables and tablename in queries
do you mean UNION ALL instead of JOIN, if you mean UNION ALL , I would go with a set returning function passing it the necessary WHERE clause to be applied to all of your tables. You might be able to wrap the whole thing into a view -- Original Message --- From: solarsail <[EMAIL PROTECTED]> To: Tom Lane <[EMAIL PROTECTED]> Cc: [email protected] Sent: Tue, 4 Oct 2005 14:40:54 -0400 Subject: Re: [SQL] using pg_tables and tablename in queries > The current behavior is by design. > > We use the table as a logging repository. It can get very large 250 000 > records. Because of the large number of records that we have in the table we > found it was much faster to perform inserts on a smaller table. Our current > system rolls the tables over every 12 hours or so, creating a new table with > the following behavior: > > CREATE TABLE mytable_temp {...} > > ALTER TABLE mytable RENAME TO mytable_back_datetime; > ALTER TABLE mytable_temp RENAME TO mytable; > > I want to join the mytable_back_datetime tables together in order to perform > queries against my huge set of data to generate some reports. I'm probably > going to create a temporary table with a few indexes to make the reports run > faster... however I need to join the tables all together first. > > On 10/4/05, Tom Lane <[EMAIL PROTECTED]> wrote: > > > > solarsail <[EMAIL PROTECTED]> writes: > > > I have a large number of tables with a common naming convention > > > > > mytable001, mytable002, mytable003 ... mytable00n > > > > > I would like to do a query across all of the tables, however I do not > > know > > > all of the tables before hand, and I do not want to ( cant ) manually > > > generate a query like > > > > > select * from mytable001, mytable002, mytable003 > > > > > I have a query that returns the names of the tables I want to query: > > > > > select tablename from pg_tables where tablename like 'mytable%' > > > > This looks to me like a situation in which you should rethink your > > data design. Those tables should all get merged into one big table, > > adding one extra column that reflects what you had been using to > > segregate the data into different tables. > > > > regards, tom lane > > --- End of Original Message --- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] using pg_tables and tablename in queries
I managed to make this work as sub query before... I wish I had
written it down somewhere...
Regarding the creation of a function. I do have a function that
almost does that. I'm having a hard time getting it to return a set
of records from the EXECUTE command ( more than one row returned by
the select * ...).
If I generate a temporary table instead of returning the results how
long will that table exist for? Excuse the OOP terminology but would
it be correct to create a 'Singleton' to access the temporary table,
where if it exists and is less than 30 minutes old use that one,
otherwise drop the table and recreate it?
Thanks
-- sample function..
CREATE OR REPLACE FUNCTION testfunc_jointables()
RETURNS SETOF record AS
$BODY$
DECLARE
query TEXT;
BEGIN
query := 'auditrecord';
FOR
atablename IN select * from pg_tables where tablename like
'mytable_%'
LOOP
query := query || ', ' || quote_ident(atablename.tablename);
END LOOP;
EXECUTE ' SELECT * from ' || query;
END;
On 10/4/05, Yasir Malik <[EMAIL PROTECTED]> wrote:
> > The current behavior is by design.
> >
> > We use the table as a logging repository. It can get very large 250 000
> > records. Because of the large number of records that we have in the table we
> > found it was much faster to perform inserts on a smaller table. Our current
> > system rolls the tables over every 12 hours or so, creating a new table with
> > the following behavior:
> >
> > CREATE TABLE mytable_temp {...}
> >
> > ALTER TABLE mytable RENAME TO mytable_back_datetime;
> > ALTER TABLE mytable_temp RENAME TO mytable;
> >
> > I want to join the mytable_back_datetime tables together in order to perform
> > queries against my huge set of data to generate some reports. I'm probably
> > going to create a temporary table with a few indexes to make the reports run
> > faster... however I need to join the tables all together first.
> >
>
> I would create a function that creates a string with a query that includes
> all the tables you need, and call execute on the string. You would loop
> through the all tables from pg_tables and keep on appending the table name
> you need.
>
> Regards,
> Yasir
>
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faq
>
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] using pg_tables and tablename in queries
Yasir -- You wrote: >If I generate a temporary table instead of returning the results how >long will that table exist for? Excuse the OOP terminology but would >it be correct to create a 'Singleton' to access the temporary table, >where if it exists and is less than 30 minutes old use that one, >otherwise drop the table and recreate it? In 8.0: "Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction (see ON COMMIT below). Existing permanent tables with the same name are not visible to the current session while the temporary table exists, unless they are referenced with schema-qualified names. Any indexes created on a temporary table are automatically temporary as well." So a temp table would persist as long as the originating session, but only that session could see it. Not sure if this helps or not ... Greg Williamson DBA GlobeXplorer LLC ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Help with multistage query
On Wed, Sep 07, 2005 at 05:37:47PM -0400, Matt Emmerton wrote: > > - Original Message - > From: Russell Simpkins > To: [email protected] > Sent: Wednesday, September 07, 2005 4:05 PM > Subject: Re: [SQL] Help with multistage query > > I have a perl script that issues a series of SQL statements to perform > some queries. The script works, but I believe there must be a more elegant > way to do this. > > > > The simplified queries look like this: > > > > SELECT id FROM t1 WHERE condition1; ;returns about 2k records which are > stored in @idarray > > > > foreach $id (@idarray) { > >SELECT x FROM t2 WHERE id=$id; ; each select returns about 100 > records which are saved in a perl variable > > } > > how about > select t1.id from t1, t2 where t1.id = t2.id and t2.id = x > > or more correctly, based on the OP's example: > > select t2.x from t1, t2 where t1.id = t2.id and t1.id = Actually, I think you want AND t2.x , not t1.id. BTW, I recommend not using id as a bareword field name. Very easy to get confused when you start joining a bunch of stuff together. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Why doesn't the SERIAL data type automatically have a
On Tue, Sep 27, 2005 at 10:33:14AM -0500, Scott Marlowe wrote: > On Mon, 2005-09-26 at 20:03, Tom Lane wrote: > > Ferindo Middleton Jr <[EMAIL PROTECTED]> writes: > > > Is there some reason why the SERIAL data type doesn't automatically have > > > a UNIQUE CONSTRAINT. > > > > It used to, and then we decoupled it. I don't think "I have no use for > > one without the other" translates to an argument that no one has a use > > for it ... > > I have to admit, right after the change was made, I was of the opinion > that no one would ever need that. Then, a few months later, it was > exactly what I needed for some project... :) Arguably it would have been better to make the default case add either UNIQUE or PRIMARY KEY with a way to over-ride. If newbies are getting burned maybe it would be useful to toss a NOTICE or maybe even WARNING when a serial is created without a unique constraint of some kind? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Why doesn't the SERIAL data type automatically have a
Jim C. Nasby wrote: Is there some reason why the SERIAL data type doesn't automatically have a UNIQUE CONSTRAINT. It used to, and then we decoupled it. [snip] Arguably it would have been better to make the default case add either UNIQUE or PRIMARY KEY with a way to over-ride. Arguably SERIAL shouldn't be a type at all since it's nothing to do with defining a set of values. If you were being clean about it you'd have to have something like "mycol INTEGER SERIAL UNIQUE", then wire SERIAL to a generator function for the type in question. If newbies are getting burned maybe it would be useful to toss a NOTICE or maybe even WARNING when a serial is created without a unique constraint of some kind? Don't forget the NOT NULL too. Perhaps simpler to have a PGIDENT pseudo-type that implies "UNIQUE NOT NULL" and then explain the difference in the docs. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Why doesn't the SERIAL data type automatically have a
Jim C. Nasby wrote: On Tue, Sep 27, 2005 at 10:33:14AM -0500, Scott Marlowe wrote: On Mon, 2005-09-26 at 20:03, Tom Lane wrote: Ferindo Middleton Jr <[EMAIL PROTECTED]> writes: Is there some reason why the SERIAL data type doesn't automatically have a UNIQUE CONSTRAINT. It used to, and then we decoupled it. I don't think "I have no use for one without the other" translates to an argument that no one has a use for it ... I have to admit, right after the change was made, I was of the opinion that no one would ever need that. Then, a few months later, it was exactly what I needed for some project... :) Arguably it would have been better to make the default case add either UNIQUE or PRIMARY KEY with a way to over-ride. If newbies are getting burned maybe it would be useful to toss a NOTICE or maybe even WARNING when a serial is created without a unique constraint of some kind? Based on the feedback I received after I made that original post, it seemed most people don't use SERIAL with a unique constraint or primary key and I was blasted for making such a suggestion. I'm sorry... It only seemed logical to me to do so and I thought other's would think the same. After giving it further thought to it and thinking about the broader scope that the developers would need to employ to the overall body of people using this database, it does now make more sense to me to just not include it at all and leave it to the admin to deploy it using what ever schema he/she sees fit... I don't think a NOTICE or a WARNING is necessary. People can read documentation. You should probably just stress more so that they actually read the docs rather than putting warnings and the like in place. When I first wrote the article I was a little falsely alarmed because I had thought that I didn't read the documentation and deployed a bunch of table using a serial without constraining them to some kind of UNIQUE property... but I later realized it was just this one table that I didn't do it with and had accidentally duplicated the fields integer value during manual INSERTS/RESTORES/BACKUPS etc. and the like to my db. Ferindo ---(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
Re: [SQL] Why doesn't the SERIAL data type automatically have a
Richard Huxton wrote: Jim C. Nasby wrote: Is there some reason why the SERIAL data type doesn't automatically have a UNIQUE CONSTRAINT. It used to, and then we decoupled it. [snip] Arguably it would have been better to make the default case add either UNIQUE or PRIMARY KEY with a way to over-ride. Arguably SERIAL shouldn't be a type at all since it's nothing to do with defining a set of values. If you were being clean about it you'd have to have something like "mycol INTEGER SERIAL UNIQUE", then wire SERIAL to a generator function for the type in question. If newbies are getting burned maybe it would be useful to toss a NOTICE or maybe even WARNING when a serial is created without a unique constraint of some kind? Don't forget the NOT NULL too. Perhaps simpler to have a PGIDENT pseudo-type that implies "UNIQUE NOT NULL" and then explain the difference in the docs. -- Richard Huxton Archonet Ltd I like Richard's idea. That seems to be the best way to go. Ferindo Sleekcollar ---(end of broadcast)--- TIP 1: 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] Why doesn't the SERIAL data type automatically have a
On Tue, Oct 04, 2005 at 07:50:28PM -0400, Ferindo Middleton Jr wrote: > Based on the feedback I received after I made that original post, it > seemed most people don't use SERIAL with a unique constraint or primary > key and I was blasted for making such a suggestion. I'm sorry... It I don't think either assertion is true. I'd bet most of the developers actually do normally use an index on a serial, since it's normally used as a PK. And while people can be a bit terse with their replies, I wouldn't say you were blasted. :) -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] using pg_tables and tablename in queries
again, do you really want to join the tables or do a UNION ALL. From one of
your posts you said the table were the
same.
you need to do something like
select * from table_001
union all
select * from table_002
...
select * from table_999
I would do this in a set returning function looping of an EXECUTE. If you need
help, post the schema for a couple of
your tables and I will help with the function
Jim
-- Original Message ---
From: solarsail <[EMAIL PROTECTED]>
To: Yasir Malik <[EMAIL PROTECTED]>
Cc: PostgreSQL
Sent: Tue, 4 Oct 2005 15:50:39 -0400
Subject: Re: [SQL] using pg_tables and tablename in queries
> I managed to make this work as sub query before... I wish I had
> written it down somewhere...
>
> Regarding the creation of a function. I do have a function that
> almost does that. I'm having a hard time getting it to return a set
> of records from the EXECUTE command ( more than one row returned by
> the select * ...).
>
> If I generate a temporary table instead of returning the results how
> long will that table exist for? Excuse the OOP terminology but would
> it be correct to create a 'Singleton' to access the temporary table,
> where if it exists and is less than 30 minutes old use that one,
> otherwise drop the table and recreate it?
>
> Thanks
>
> -- sample function..
>
> CREATE OR REPLACE FUNCTION testfunc_jointables()
> RETURNS SETOF record AS
> $BODY$
> DECLARE
> query TEXT;
> BEGIN
> query := 'auditrecord';
>
> FOR
> atablename IN select * from pg_tables where tablename like
> 'mytable_%'
> LOOP
>
> query := query || ', ' || quote_ident(atablename.tablename);
>
> END LOOP;
>
> EXECUTE ' SELECT * from ' || query;
>
> END;
>
> On 10/4/05, Yasir Malik <[EMAIL PROTECTED]> wrote:
> > > The current behavior is by design.
> > >
> > > We use the table as a logging repository. It can get very large 250 000
> > > records. Because of the large number of records that we have in the table
> > > we
> > > found it was much faster to perform inserts on a smaller table. Our
> > > current
> > > system rolls the tables over every 12 hours or so, creating a new table
> > > with
> > > the following behavior:
> > >
> > > CREATE TABLE mytable_temp {...}
> > >
> > > ALTER TABLE mytable RENAME TO mytable_back_datetime;
> > > ALTER TABLE mytable_temp RENAME TO mytable;
> > >
> > > I want to join the mytable_back_datetime tables together in order to
> > > perform
> > > queries against my huge set of data to generate some reports. I'm probably
> > > going to create a temporary table with a few indexes to make the reports
> > > run
> > > faster... however I need to join the tables all together first.
> > >
> >
> > I would create a function that creates a string with a query that includes
> > all the tables you need, and call execute on the string. You would loop
> > through the all tables from pg_tables and keep on appending the table name
> > you need.
> >
> > Regards,
> > Yasir
> >
> > ---(end of broadcast)---
> > TIP 3: Have you checked our extensive FAQ?
> >
> >http://www.postgresql.org/docs/faq
> >
>
> ---(end of broadcast)---
> TIP 2: Don't 'kill -9' the postmaster
--- End of Original Message ---
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[SQL] regular expression
How do I do regular expression for the problem that I am having I have a string called desc, and say that this string in "TSWUU" -- "" "4 - DSC"-- "4" "6768 - THY" -- "6768" basically string may or may not start with number, I need substring of digits parts "" "4" "6768" - This mail sent through IMP: www.resolution.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] MOVE in SQL vs PLPGSQL
I can't find a good way to skip over a large number of records in PLPGSQL (I want to fast-forward and I don't need the I/O of reading and throwing away hundreds of records.) In SQL, I could just use MOVE. That doesn't appear to be supported in PLPGSQL?! Help? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] creating postgres tables by passing a string to function
I am storing data separated by month. How do I perform Inserts and Selects based on something like this. select * from (select 'shipped' || '0509') AS aShippingTable The table 'shipped' || '0509' exists. I am scanning barcode labels which have the '0509' on them and I want to move the data to appropiate monthly tables. '0510' would represent shipped on 2005 october. Eventually I want to create, insert, select all based on passing a string such as '0509' to a function. I am a newbie. Thanks in advance -Scott ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Getting user created tables from SQL
hi guys,
i m trying to get the user created tables from SQL by using C++ Builder.Here is the code:
TQuery *TableQuery; TableQuery= new TQuery (this);
TQuery *TableCountQuery; TableCountQuery= new TQuery (this);
TableQuery->DatabaseName = "TEMP"; TableCountQuery->DatabaseName = "TEMP";
TableCountQuery->SQL->Add ("SELECT COUNT(TABLE_NAME) AS CTABLE FROM INFORMATION_SCHEMA.TABLES WHERE (TABLE_TYPE = 'BASE TABLE')"); TableCountQuery->Open ();
int counttables = TableCountQuery->FieldByName ("CTABLE")->AsInteger;
TableCountQuery->Close ();
TableCountQuery->Free ();
TableQuery->SQL->Add ("SELECT TABLE_NAME AS TNAME FROM INFORMATION_SCHEMA.TABLES WHERE (TABLE_TYPE = 'BASE TABLE')"); TableQuery->Open ();
DynamicArray CTableArr; CTableArr.Length = counttables;
int f = 0; while (!TableQuery->Eof) { CTableArr[f] = TableQuery->FieldByName ("TNAME")->AsString; TableQuery->Next(); f++;
}
TableQuery->Close (); TableQuery->Free ();
the first Select statement work correct, counttables variable gets the right value but unfortunately, after the second select statement, in C++ Builders SQL Explorer window, it brings the correct number of rows but without datas in them!!! In Enterprise Manager the second select statement also works correct but how come it aint work in C++ builder?
i would be grateful if you can help me
Thanks
CENK
THE PUBLICENEMYhttp://www3.50megs.com/cenk1536/CHAIRMAN Of Computer Information Systems Technology CLUB
Yahoo! for Good
Click here to donate to the Hurricane Katrina relief effort.
[SQL] query tables based on a query
I have a large number of tables with a common naming convention basically: table001, table002, table003 ... table00n I would like to do a query across all of the tables, however I do not know all of the tables before hand, and I do not want to manually generate a query like select * from table001, table002, table003 I have a query that returns the names of the tables I want to query, it is basically like this: select tablename from pg_tables where tablename like 'table%' How do I do this? I've tried creating a Table Function that returns the above set and tried to use that in a select clause, but I cant get it to work either. Thanks for the help
[SQL] BirthDay SQL Issue
Hi there
This is my first posting here, please forgive me if I make any
mistakes here.
Ok
I have the structure
{CLIENTS} = Client ID, First Name, Surname, DOB, Address, Home Phone
No, Mobile Phone No
As one relation / table (There are several others in this db that are
not related to this issue)
My problem is this
I need to be able to select two dates on my webby so I have a range of
dates
E.G. (using English date format DD/MM/)
01/09/2005 to 01/10/2005
Then I want to be able to get, using some nifty query, everyone's
birthday that falls between those two dates. So if someone's birthday
was 04/09/79 it would return that tuple
I have been stuck on this for ages. Any my search for help has brought
me here.
If anyone out there can help me, in any way, I would be very grateful.
Kind regards
Brett Halligan
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] regular expression
Try this,
select substring('6768 - THY','[0-9]*');
substring
---
6768
(1 row)On 10/4/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]
> wrote:How do I do regular _expression_ for the problem that I am havingI have a string called desc, and say that this string in
"TSWUU" -- """4 - DSC"-- "4""6768 - THY" -- "6768" basically string may or may not start with number,
I need substring of digits parts"""4""6768"-This mail sent through IMP: www.resolution.com
---(end of broadcast)---TIP 2: Don't 'kill -9' the postmaster-- with regards,S.Gnanavel
Re: [SQL] regular expression
On Mon, Oct 03, 2005 at 06:08:30PM -0400, [EMAIL PROTECTED] wrote: > How do I do regular expression for the problem that I am having > I have a string called desc, and say that this string in > > "TSWUU" -- "" > "4 - DSC"-- "4" > "6768 - THY" -- "6768" > > basically string may or may not start with number, > I need substring of digits parts > "" > "4" > "6768" See "Pattern Matching" in the documentation: http://www.postgresql.org/docs/8.0/interactive/functions-matching.html Example: test=> SELECT id, data FROM foo; id |data + 1 | TSWUU 2 | 4 - DSC 3 | 6768 - THY (3 rows) test=> SELECT id, substring(data FROM '^([[:digit:]]+)') FROM foo; id | substring +--- 1 | 2 | 4 3 | 6768 (3 rows) -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
