[SQL] LIKE on index not working
Hi all,
For some reason I just can't get this to use the index for the following
query. I'm using PostgreSQL 7.3.4.
Here's the details (let me know if you need anymore information to provide
any assistance):
Indexes: person_pkey primary key btree (personid),
ix_person_active btree (bactive),
ix_person_fullname btree (tsurname, tfirstname),
ix_person_member btree (bmember),
ix_person_supporter btree (bsupporter),
ix_person_surname btree (lower(tsurname))
smartteamscouts=# explain analyze select * from person where bmember = 1 AND
lower(tsurname) like lower('weaver');
QUERY PLAN
---
Seq Scan on person (cost=0.00..12946.58 rows=310 width=416) (actual
time=873.94..1899.09 rows=6 loops=1)
Filter: ((bmember = 1) AND (lower((tsurname)::text) ~~ 'weaver'::text))
Total runtime: 1899.64 msec
(3 rows)
smartteamscouts=# explain analyze select * from person where bmember = 1 AND
lower(tsurname) = lower('weaver');
QUERY PLAN
---
Index Scan using ix_person_surname on person (cost=0.00..1265.78 rows=310
width=416) (actual time=0.91..2.03 rows=6 loops=1)
Index Cond: (lower((tsurname)::text) = 'weaver'::text)
Filter: (bmember = 1)
Total runtime: 2.36 msec
(4 rows)
As you can see, using the '=' operator it works just fine, but as soon as
the 'like' operator comes into it, no good.
Is this a bug in 7.3.4? Or is it something else I need to adjust?
Thanks for your help!
Chris
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] connection delay
I have a client made in java who interogate postgres using jdbc driver. If the tcp conection falls (for a few seconds) the client give an error message about that connection. How can I avoid this error? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] connection delay
O kyrios cristi egrapse stis Jul 22, 2004 :
> I have a client made in java who interogate postgres using jdbc driver.
> If the tcp conection falls (for a few seconds) the client give an error
> message about that connection.
> How can I avoid this error?
If its not a jdbc issue, maybe you can play with
/* for a new connection */
System.setProperty("sun.net.client.defaultConnectTimeout", "1");
/* for already established connection */
System.setProperty("sun.net.client.defaultReadTimeout", "1");
the interval is given in milliseconds.
I have tested on FreeBSD 5.1-RELEASE-p10,
1.4.2-p5 JVM
>
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
>
--
-Achilleus
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] next integer in serial key
Actually it does work, call nextval to get your next value, then call your INSERT
statement,
explicitly giving said value for the serial column. Then you can proceed with using
said value in
the INSERT statement of the related inserts with foreign keys to it.
Alternatively, you can do:
INSERT (accepting the default)
then SELECT currval(the_sequence_object);
then
NOTE: 2nd method assumes that nobody else called nextval() on the sequence between
when you did the
insert and when you did the select currval(). Note that being inside a transaction is
NOT
sufficient, you need an explicit lock on the sequence. I do not recommend the 2nd
method, too much
can go wrong.
Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
[EMAIL PROTECTED]
Fax: (416) 441-9085
> -Original Message-
> From: Kenneth Gonsalves [mailto:[EMAIL PROTECTED]
> Sent: Thursday, July 22, 2004 12:13 AM
> To: [EMAIL PROTECTED]
> Subject: Re: [SQL] next integer in serial key
>
>
> On Thursday 22 July 2004 10:25 am, you wrote:
> > The same way the default value is defined, which you can
> find by doing:
> > \d tablename
> >
> > Which usually gives something like:
> > Table
> "public.gbs_floorplans"
> > Column| Type |
>
> > Modifiers
> >
> >
> --+---+---
> -
> >- -
> > floorplan_id | integer | not null default
> > nextval('public.gbs_floorplans_floorplan_id_seq'::text)
> > division_id | character(3) | not null
> > floorplan_display_id | character(10) | not null
> >
> > Hence
> > SELECT nextval('public.gbs_floorplans_floorplan_id_seq'::text)
>
> nope. what happens is that i enter data into a table with a
> serial type id,
> and then use that id as a foreign key to enter data into
> another table. so i
> need to know the id for the second entry. i commit after both entries
> succeed. If i use nextval to find the id, this increments the
> id, which will
> defeat the purpose.
> --
> regards
> kg
>
> http://www.onlineindianhotels.net - hotel bookings
> reservations in over 4600
> hotels in India
> http://www.ootygolfclub.org
>
---(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] next integer in serial key
On Thu, 2004-07-22 at 12:48, [EMAIL PROTECTED] wrote: > Actually it does work, call nextval to get your next value, then call > your INSERT statement, > explicitly giving said value for the serial column. Then you can > proceed with using said value in > the INSERT statement of the related inserts with foreign keys to it. > > Alternatively, you can do: > INSERT (accepting the default) > then SELECT currval(the_sequence_object); > then > > NOTE: 2nd method assumes that nobody else called nextval() on the > sequence between when you did the > insert and when you did the select currval(). Note that being inside > a transaction is NOT > sufficient, you need an explicit lock on the sequence. I do not > recommend the 2nd method, too much > can go wrong. This last paragraph is wrong and irrelevant. It is a point which for some reason is continually being misunderstood. currval() *always* returns the last value generated for the sequence in the *current session*. It is specifically designed to do what you are suggesting without any conflict with other sessions. There is *never* any risk of getting a value that nextval() returned to some other user's session. The downside is that it operates outside the transaction and therefore cannot be rolled back. It is also necessary to run nextval() in the session (either explicitly or by letting a serial column take its default) before you can use currval() on the sequence. Oliver Elphick ---(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] LIKE on index not working
Am Donnerstag, 22. Juli 2004 09:38 schrieb Chris Cox: > For some reason I just can't get this to use the index for the following > query. I'm using PostgreSQL 7.3.4. In 7.3, LIKE cannot use an index unless you set the locale to C. In 7.4, LIKE can use an index, but it has to be a different kind of index, as explained here: http://www.postgresql.org/docs/7.4/static/indexes-opclass.html -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] surrogate key or not?
Josh, I reckon you are the one in the know so I'll take advantage of that and ascertain myself of your advice. I am the primary designer for the database schema of GnuMed (www.gnumed.org) - a practice management application intended to store medical data. Obviously we wouldn't want ambigous data. I have until now used surrogate primary keys on all table like so: create table diagnosis ( pk serial primary key, fk_patient integer not null references patient(pk) on update cascade on delete cascade, narrative text not null, unique(fk_patient, narrative) ); Note that fk_patient would not do for a primary key since you can have several diagnoses for a patient. However, the combination of fk_patient and narrative would, as is implied by the unique() constraint. For fear of having the real primary key change due to business logic changes I have resorted to the surrogate key. Short question: Is this OK re your concerns for using surrogates, eg. using a surrogate but making sure that at any one time there *would* be a real primary key candidate ? This would amount to: > Streets > IDStreet Name Location > 345 Green StreetWest Side of City > 2019 Green StreetIn Front of Consulate > 5781 Green StreetShortest in Town Key: ID UNIQUE: Key, Location Is that OK ? Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] next integer in serial key
> > Alternatively, you can do: > > INSERT (accepting the default) > > then SELECT currval(the_sequence_object); > > then > > > > NOTE: 2nd method assumes that nobody else called nextval() on the > > sequence between when you did the > > insert and when you did the select currval(). Note that > being inside > > a transaction is NOT > > sufficient, you need an explicit lock on the sequence. I do not > > recommend the 2nd method, too much > > can go wrong. > > This last paragraph is wrong and irrelevant. It is a point which for > some reason is continually being misunderstood. > > currval() *always* returns the last value generated for the > sequence in > the *current session*. It is specifically designed to do what you are > suggesting without any conflict with other sessions. There is *never* > any risk of getting a value that nextval() returned to some > other user's > session. That statement depends on different factors. If you for example have an application server, and the database connection is shared across multiple application server clients (or the query results get cached by your application server, Ugh!), the statement IS valid: I encountered this issue 2 years ago with coldfusion 4.5 using the unixODBC driver against Postgres 7.1.1 So without knowing his architecture, I needed to state that caveat, albeit rare. Even with knowing the architecture, the point still holds that you need to call currval() before another insert (or any call to nextval) is made. That probably should have been clearer, sorry. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] next integer in serial key
On Thursday 22 July 2004 05:18 pm, [EMAIL PROTECTED] wrote: > Alternatively, you can do: > INSERT (accepting the default) > then SELECT currval(the_sequence_object); > then did this. barf: foreign key not in original table -- regards kg http://www.onlineindianhotels.net - hotel bookings reservations in over 4600 hotels in India http://www.ootygolfclub.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] next integer in serial key
On Thursday 22 July 2004 05:45 pm, Oliver Elphick wrote: > On Thu, 2004-07-22 at 12:48, [EMAIL PROTECTED] wrote: > > Actually it does work, call nextval to get your next value, then call > > your INSERT statement, > > explicitly giving said value for the serial column. Then you can > > proceed with using said value in > > the INSERT statement of the related inserts with foreign keys to it. > > > > Alternatively, you can do: > > INSERT (accepting the default) > > then SELECT currval(the_sequence_object); > > then > > > > NOTE: 2nd method assumes that nobody else called nextval() on the > > sequence between when you did the > > insert and when you did the select currval(). Note that being inside > > a transaction is NOT > > sufficient, you need an explicit lock on the sequence. I do not > > recommend the 2nd method, too much > > can go wrong. > > This last paragraph is wrong and irrelevant. It is a point which for > some reason is continually being misunderstood. > > currval() *always* returns the last value generated for the sequence in > the *current session*. It is specifically designed to do what you are > suggesting without any conflict with other sessions. There is *never* > any risk of getting a value that nextval() returned to some other user's > session. > > The downside is that it operates outside the transaction and therefore > cannot be rolled back. It is also necessary to run nextval() in the > session (either explicitly or by letting a serial column take its > default) before you can use currval() on the sequence. in short, the only safe way of doing this is to commit on insert to the main table and then query it to get the value to insert in the other tables - and if the subsequent inserts fail .. -- regards kg http://www.onlineindianhotels.net - hotel bookings reservations in over 4600 hotels in India http://www.ootygolfclub.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] next integer in serial key
That usually works. But if you can have 2 records in that table that are identical except the serial column, your query to get the id will return 2 results. Its also inefficient, if that query is costly (whether or not it can return 2 results). That's why I do: SELECT nextval(my_tables_sequence) AS next_id; INSERT INTO mytable (serial_column, data_columns...) VALUES (next_id, data_columns...) INSERT INTO related_table (fkey_column, other_columns...) VALUES (next_id, other_columns...) You can even do ALL that inside a transaction which guarantees that either: 1) ALL of the inserts are done OR 2) NONE of the inserts are done (Note it doesn't roll back the sequence, that id on rollback would become unused) Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] Fax: (416) 441-9085 > -Original Message- > From: Kenneth Gonsalves [mailto:[EMAIL PROTECTED] > Sent: Thursday, July 22, 2004 7:52 AM > To: Oliver Elphick; [EMAIL PROTECTED] > Cc: Postgresql Sql Group (E-mail) > Subject: Re: [SQL] next integer in serial key > > > On Thursday 22 July 2004 05:45 pm, Oliver Elphick wrote: > > On Thu, 2004-07-22 at 12:48, [EMAIL PROTECTED] wrote: > > > Actually it does work, call nextval to get your next > value, then call > > > your INSERT statement, > > > explicitly giving said value for the serial column. Then you can > > > proceed with using said value in > > > the INSERT statement of the related inserts with foreign > keys to it. > > > > > > Alternatively, you can do: > > > INSERT (accepting the default) > > > then SELECT currval(the_sequence_object); > > > then > > > > > > NOTE: 2nd method assumes that nobody else called nextval() on the > > > sequence between when you did the > > > insert and when you did the select currval(). Note that > being inside > > > a transaction is NOT > > > sufficient, you need an explicit lock on the sequence. I do not > > > recommend the 2nd method, too much > > > can go wrong. > > > > This last paragraph is wrong and irrelevant. It is a point > which for > > some reason is continually being misunderstood. > > > > currval() *always* returns the last value generated for the > sequence in > > the *current session*. It is specifically designed to do > what you are > > suggesting without any conflict with other sessions. There > is *never* > > any risk of getting a value that nextval() returned to some > other user's > > session. > > > > The downside is that it operates outside the transaction > and therefore > > cannot be rolled back. It is also necessary to run nextval() in the > > session (either explicitly or by letting a serial column take its > > default) before you can use currval() on the sequence. > > in short, the only safe way of doing this is to commit on > insert to the main > table and then query it to get the value to insert in the > other tables - and > if the subsequent inserts fail .. > -- > regards > kg > > http://www.onlineindianhotels.net - hotel bookings > reservations in over 4600 > hotels in India > http://www.ootygolfclub.org > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] next integer in serial key
On Thu, 22 Jul 2004 [EMAIL PROTECTED] wrote: > > > Alternatively, you can do: > > > INSERT (accepting the default) > > > then SELECT currval(the_sequence_object); > > > then > > > > > > NOTE: 2nd method assumes that nobody else called nextval() on the > > > sequence between when you did the > > > insert and when you did the select currval(). Note that > > being inside > > > a transaction is NOT > > > sufficient, you need an explicit lock on the sequence. I do not > > > recommend the 2nd method, too much > > > can go wrong. > > > > This last paragraph is wrong and irrelevant. It is a point which for > > some reason is continually being misunderstood. > > > > currval() *always* returns the last value generated for the > > sequence in > > the *current session*. It is specifically designed to do what you are > > suggesting without any conflict with other sessions. There is *never* > > any risk of getting a value that nextval() returned to some > > other user's > > session. > > That statement depends on different factors. If you for example have an application > server, and the > database connection is shared across multiple application server clients (or the > query results get > cached by your application server, Ugh!), the statement IS valid: I encountered > this issue 2 years > ago with coldfusion 4.5 using the unixODBC driver against Postgres 7.1.1 If your application server will share your connection without your explicit releasing of it, then yes, currval() is unsafe. So are basically transactions, cursors, session variables and deferrable constraints. That's not a valid platform to be using a database from really. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Converting a plperlu function to a plpgsql function
Hello,
I am trying to convert a database function that is written in perl to a
PL/pgSQL function. However, there a a couple of lines that I don't
think can be converted.
First line:
my @active_tables=split(/,/,$tables);
Is there anyway to split a variable like the perl split above?
Second line:
if ($r=~/^-([0-9]?)([A-z_]+)/)
{
my $locid = $1;
my $table = $2;
Is there any way to to regular expressions similar to above. I need to
be able to save the matches for later use. I know you can do regular
expressions in sql but I can't find any equivalent for the above.
Now you may be saying that why don't I just leave it in perl? Well, I
would like to but my boss wants me to convert this to PL/pgSQLlong
story. Anyway, if it can't be done it can't be done. But I was just
wondering if anyone has any advice.
Thanks for the help.
Later
--
Devin Whalen
Programmer
Synaptic Vision Inc
Phone-(416) 539-0801
Fax- (416) 539-8280
1179A King St. West
Toronto, Ontario
Suite 309 M6K 3C5
Home-(416) 653-3982
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] LIKE on index not working
[EMAIL PROTECTED] ("Chris Cox") writes:
> Hi all,
>
> For some reason I just can't get this to use the index for the following
> query. I'm using PostgreSQL 7.3.4.
>
> Here's the details (let me know if you need anymore information to provide
> any assistance):
>
> Indexes: person_pkey primary key btree (personid),
> ix_person_active btree (bactive),
> ix_person_fullname btree (tsurname, tfirstname),
> ix_person_member btree (bmember),
> ix_person_supporter btree (bsupporter),
> ix_person_surname btree (lower(tsurname))
>
> smartteamscouts=# explain analyze select * from person where bmember = 1 AND
> lower(tsurname) like lower('weaver');
> QUERY PLAN
>
> ---
> Seq Scan on person (cost=0.00..12946.58 rows=310 width=416) (actual
> time=873.94..1899.09 rows=6 loops=1)
>Filter: ((bmember = 1) AND (lower((tsurname)::text) ~~ 'weaver'::text))
> Total runtime: 1899.64 msec
> (3 rows)
>
> smartteamscouts=# explain analyze select * from person where bmember = 1 AND
> lower(tsurname) = lower('weaver');
> QUERY PLAN
>
> ---
> Index Scan using ix_person_surname on person (cost=0.00..1265.78 rows=310
> width=416) (actual time=0.91..2.03 rows=6 loops=1)
>Index Cond: (lower((tsurname)::text) = 'weaver'::text)
>Filter: (bmember = 1)
> Total runtime: 2.36 msec
> (4 rows)
>
> As you can see, using the '=' operator it works just fine, but as soon as
> the 'like' operator comes into it, no good.
>
> Is this a bug in 7.3.4? Or is it something else I need to adjust?
A problem with this is that it needs to evaluate lower(tsurname) for
each row, which makes the index pretty much useless.
If you had a functional index on lower(tsurname), that might turn out
better...
create index ix_lower_surname on person(lower(tsurname));
--
"cbbrowne","@","acm.org"
http://www3.sympatico.ca/cbbrowne/oses.html
Make sure your code does nothing gracefully.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Problem with transaction in functions and tempory tables
Hello, I'm using PostgreSQL 7.4 I have a function wich use temporary tables. I read about temporary tables and they exists during the session. But i have to call this function many times in the same sesion with diferents parameters and expecting different results. So, there is a problem because the temporary table already exists during the second execution of the funcition. To avoid this, I used this sintax after de create table statement "ON COMMIT DROP" which destroy the table in the next commit. for example, If i run this script many times in the same session there weren't problems: begin; create temporary table test(x integer) ON COMMIT DROP; INSERT INTO test values(1); select * from test; commit; Then I tried to use this in function: CREATE OR REPLACE FUNCTION "public"."f_test" () RETURNS SETOF "pg_catalog"."record" AS'BEGIN CREATE TEMPORARY TABLE test( x integer ) ON COMMIT DROP; INSERT INTO test values (1); --RETORNA LOS RESULTADOS FOR res IN SELECT x FROM test LOOP RETURN NEXT res; END LOOP; RETURN;END;'LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY DEFINER; and then I executed the function this way: BEGIN; SELECT * FROM f_test() AS R(x INTEGER); COMMIT; but in the second execution, it falis with an error wich said that doesn't exist the relation with OID ... I supose it is because the table doesn't exist because in the second execution the function couldn't create the table or it is using an old reference of the dropped table. I think if I put the begin and the commit inside the function, it will work. I tried this way, but it doesn't compile: CREATE OR REPLACE FUNCTION "public"."f_test" () RETURNS SETOF "pg_catalog"."record" AS'BEGIN BEGIN; CREATE TEMPORARY TABLE test( x integer ) ON COMMIT DROP; . I tried too with START, but without success. I'd appeciate some help. Tanks, Gerardo.
Re: [SQL] surrogate key or not?
Kasten, > I have until now used surrogate primary keys on all table like > so: > Short question: Is this OK re your concerns for using > surrogates, eg. using a surrogate but making sure that at any > one time there *would* be a real primary key candidate ? Yes, this is an example of that. You have a long text field as part of the key, and that would kill you performance-wise if diagnosis was referred to in other tables and joined in views. Keep in mind, though, that if a table is an "end node"; that is, if its PK is not used as an FK by any other table, then worries about the performance of keys and size of indexes are unfounded. In fact, for such tables, the surrogate key is a performance drag; it adds a column and an index which are not needed. Now, addressing your table, I would have concerns other than the use of primary keys.I suggest humbly that your data model/business logic may need some development: create table diagnosis ( pk serial primary key, fk_patient integer not null references patient(pk) on update cascade on delete cascade, narrative text not null, unique(fk_patient, narrative) ); This was obviously created so that a patient could have multiple diagnoses. However, there is no information in the table to indicate *why* there are multiple diagnoses. And you are using a real key based on a long text field; always hazardous, as there are many ways to phrase the same information and duplication is likely. To do it in english, your postulates look like: PATIENT 67 was given a diagnosis of WATER ON THE KNEE. PATIENT 456 was given a diagnosis of ACUTE HYPOCHONDRIA. But this is a bit sketchy. Who made these diagnoses? When did they make them? Why? This table could carry a *lot* more information, and should (sql is shorthand) create table diagnosis ( pk serial primary key, fk_patient integer references patient(pk), fk_visit integer references visits(pk), fk_complaint integer references complaints(pk) fk_staff integer references medical_staff(pk) narrative text, unique(fk_patient, fk_visit, fk_complaint, fk_staff) ); Then your postulates become *much* more informative: PATIENT 67 was given a diagnosis by STAFF MEMBER 12 on his VISIT #3 in response to NOT BEING ABLE TO WALK of WATER ON THE KNEE PATIENT 456 was given a diagnosis by STAFF MEMBER 19 on his VISIT #192 in response to THE CREEPY-CRAWLIES of ACUTE HYPOCHONDRIA It also allows you to establish a much more useful key; it's reasonable to expect that a single staff member on one visit in response to one complaint would only give one diagnosis. Otherwise, you have more than database problems. And it prevents you from having to rely on a flaky long text key. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] LIKE on index not working
"Chris Cox" <[EMAIL PROTECTED]> writes:
> For some reason I just can't get this to use the index for the following
> query. I'm using PostgreSQL 7.3.4.
It works for me in 7.3.6 (see below). I'd guess that you are using a
non-LIKE-safe locale setting --- can you get LIKE to use indexes at
all?
regression=# create table fooey(f1 varchar);
CREATE TABLE
regression=# create index fooeyi on fooey(lower(f1));
CREATE INDEX
regression=# explain select * from fooey where lower(f1) = lower('z');
QUERY PLAN
--
Index Scan using fooeyi on fooey (cost=0.00..17.08 rows=5 width=32)
Index Cond: (lower((f1)::text) = 'z'::text)
(2 rows)
regression=# explain select * from fooey where lower(f1) like lower('z');
QUERY PLAN
--
Index Scan using fooeyi on fooey (cost=0.00..17.08 rows=5 width=32)
Index Cond: (lower((f1)::text) = 'z'::text)
Filter: (lower((f1)::text) ~~ 'z'::text)
(3 rows)
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Problem with transaction in functions and tempory tables
On Thu, 22 Jul 2004, Gerardo Castillo wrote: > Hello, > > I'm using PostgreSQL 7.4 > > I have a function wich use temporary tables. I read about temporary tables > and they exists during the session. > But i have to call this function many times in the same sesion with > diferents parameters and expecting different results. So, there is a problem > because the temporary table already exists during the second execution of > the funcition. If you're going to use temporary tables in a plpgsql function, you really need to use the table only through EXECUTE commands so that the plans don't get saved. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Converting a plperlu function to a plpgsql function
Devin Whalen wrote:
First line:
my @active_tables=split(/,/,$tables);
Is there anyway to split a variable like the perl split above?
I'm no perl guru, but in 7.4 I believe this does what you're looking for:
regression=# select string_to_array('1,2,3',',');
string_to_array
-
{1,2,3}
(1 row)
Second line:
if ($r=~/^-([0-9]?)([A-z_]+)/)
{
my $locid = $1;
my $table = $2;
Not sure about this one. Hopefully someone else can chime in. Maybe a
little less efficient, but it seems like it would be easy enough to
parse when true.
HTH,
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Converting a plperlu function to a plpgsql function
--- Joe Conway <[EMAIL PROTECTED]> wrote:
> Devin Whalen wrote:
> > First line:
> > my @active_tables=split(/,/,$tables);
> >
> > Is there anyway to split a variable like the perl
> split above?
>
> I'm no perl guru, but in 7.4 I believe this does
> what you're looking for:
>
> regression=# select string_to_array('1,2,3',',');
> string_to_array
> -
> {1,2,3}
> (1 row)
>
> > Second line:
> >
> > if ($r=~/^-([0-9]?)([A-z_]+)/)
> > {
> > my $locid = $1;
> > my $table = $2;
PostgreSQL doesn't offer capturing parentheses. The
regex library does offer that, but no-one has yet done
the coding to bring that functionality into
PostgreSQL.
You could do it in two steps:
1. Test using a regular expression
2. locid := substr(r,1); table := substr(r,2,1-len(r))
(better check the syntax on those).
>
> Not sure about this one. Hopefully someone else can
> chime in. Maybe a
> little less efficient, but it seems like it would be
> easy enough to
> parse when true.
>
> HTH,
>
> Joe
>
> ---(end of
> broadcast)---
> TIP 1: subscribe and unsubscribe commands go to
> [EMAIL PROTECTED]
>
__
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] Converting a plperlu function to a plpgsql function
On Thu, 2004-07-22 at 15:09, Jeff Eckermann wrote:
> --- Joe Conway <[EMAIL PROTECTED]> wrote:
> > Devin Whalen wrote:
> > > First line:
> > > my @active_tables=split(/,/,$tables);
> > >
> > > Is there anyway to split a variable like the perl
> > split above?
> >
> > I'm no perl guru, but in 7.4 I believe this does
> > what you're looking for:
> >
> > regression=# select string_to_array('1,2,3',',');
> > string_to_array
> > -
> > {1,2,3}
> > (1 row)
> >
> > > Second line:
> > >
> > > if ($r=~/^-([0-9]?)([A-z_]+)/)
> > > {
> > > my $locid = $1;
> > > my $table = $2;
>
> PostgreSQL doesn't offer capturing parentheses. The
> regex library does offer that, but no-one has yet done
> the coding to bring that functionality into
> PostgreSQL.
>
> You could do it in two steps:
> 1. Test using a regular expression
> 2. locid := substr(r,1); table := substr(r,2,1-len(r))
> (better check the syntax on those).
>
> >
> > Not sure about this one. Hopefully someone else can
> > chime in. Maybe a
> > little less efficient, but it seems like it would be
> > easy enough to
> > parse when true.
> >
> > HTH,
> >
> > Joe
> >
> > ---(end of
> > broadcast)---
> > TIP 1: subscribe and unsubscribe commands go to
> > [EMAIL PROTECTED]
> >
>
>
>
>
Hey,
Thanks for the advice guys. The main reason that we wanted to change
from a perl function was because we were having problems getting the
perl library installed for postgres. We think we have solved that
problem so it looks like I won't have to convert the function. However,
your responses have made me realize that I can convert it. So I think
I will convert it because it saves having to connect up to the database
from inside the perl function. This might be a little faster??
Thanks for the help.
Later
--
Devin Whalen
Programmer
Synaptic Vision Inc
Phone-(416) 539-0801
Fax- (416) 539-8280
1179A King St. West
Toronto, Ontario
Suite 309 M6K 3C5
Home-(416) 653-3982
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Converting a plperlu function to a plpgsql function
Jeff Eckermann <[EMAIL PROTECTED]> writes:
>> Second line:
>>
>> if ($r=~/^-([0-9]?)([A-z_]+)/)
>> {
>> my $locid = $1;
>> my $table = $2;
> PostgreSQL doesn't offer capturing parentheses.
Hm? See the substring function:
regression=# select substring('foobar' from 'o(.)a');
substring
---
b
(1 row)
You'd have to use it twice to collect two separate substrings, which is
mildly annoying, but it's hard to see how to do better without bizarre
behind-the-scenes stuff (like Perl's magic $n variables).
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] LIKE on index not working
Hi Peter, Thanks for the tip. The locale we're using is en_US.UTF-8. From my limited knowledge of locales, that's a non-C one isn't it? Am I right in saying that to fix it I need to initdb again with a C locale? How do I go about doing that on an environment with some 132 databases? What a pain! Chris - Original Message - From: "Peter Eisentraut" <[EMAIL PROTECTED]> To: "Chris Cox" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, July 22, 2004 10:41 PM Subject: Re: [SQL] LIKE on index not working > Am Donnerstag, 22. Juli 2004 09:38 schrieb Chris Cox: > > For some reason I just can't get this to use the index for the following > > query. I'm using PostgreSQL 7.3.4. > > In 7.3, LIKE cannot use an index unless you set the locale to C. In 7.4, LIKE > can use an index, but it has to be a different kind of index, as explained > here: > > http://www.postgresql.org/docs/7.4/static/indexes-opclass.html > > -- > Peter Eisentraut > http://developer.postgresql.org/~petere/ > ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] surrogate key or not?
I want to add some notes > create table diagnosis ( > pk serial primary key, > fk_patient integer > not null > references patient(pk) > on update cascade > on delete cascade, > narrative text > not null, > unique(fk_patient, narrative) > ); 1) a sequence generates INT8 values (in general) and you have INT4 field to refer to a serial field. 2) narrative TEXT is very bad to check uniquness becauce it is non-formal human-generated native-language text. so it contains missprints, it may be rephrased many ways with the same meaning. 3) afaik a diagnosis doesn't belong to a patient, it belongs to a History, and History is marked with a date and status and belongs to a patient. do not treat my words as The Truth. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
