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:

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

[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

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 ) m

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

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

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 <[

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

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,

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: pgsql-sql@postgresql.org > Sent: Wednesday, September 07, 2005 4:05 PM > Subject: Re: [SQL] Help with multistage query > > I have a perl script that issu

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

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 SERIA

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.

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

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

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

[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 ""

[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

[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

[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";    TableCountQu

[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 *

[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 is

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 - DS

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" > > basic