Re: [SQL] Server Side C programming Environment Set up
Peter Eisentraut [EMAIL PROTECTED] writes: make install-all-headers That's not a complete solution though; the headers are only half the problem. Makefiles are the other half, and our story on them is pretty bad. For instance I've been meaning to ask what to do about this open bug report: https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=112244 If you don't have working Makefiles, it doesn't help that much to have all the headers. I think Lamar's perennial issues with running the regression tests in an RPM installation are closely related too ... regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Select from two databases
Hi, I was wondering if there has been any further development on running SQL statements involving more than one database? We are porting a database from SQLServer to Postgres and some queries in related applications use joins on tables that are in different databases. We know we can modify the code to use dblink to accomplish much the same thing in Postgres. Does anyone know of a more elegant solution for Postgres than dblink? Regards Becky Becky Alcorn Unisolve Pty Ltd - Melbourne, Australia +61 3 9568 2005 This line intentionally left blank. ---(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] Join issue on a maximum value
Heflin [EMAIL PROTECTED] writes: Bruno Wolff III wrote: The postgres specific way of doing this is: SELECT DISTINCT ON (auction.auction_id) auction.auction_id, image.image_id, image.image_descr FROM auction JOIN image ON auction.auction_id = image.auction_id WHERE auction.auction_owner = 'Mabel' ORDER BY auction.auction_id, image.image_id DESC The thing that disturbs me about your syntax is that I don't really see an assurance that I'll get the correct image_id. Any chance you can tell me why this works? The ORDER BY DESC is what forces the max image_id to be selected. Read the discussion of SELECT DISTINCT ON in the SELECT reference page; the weather report example may be illuminating. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] staggered query?
hey thanks! it worked:) here's how we did it: select sampletime from data where (extract(seconds from sampletime)::int)::text in (14, 17, 19); -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Denis P Gohel Sent: Wednesday, April 21, 2004 3:12 PM To: [EMAIL PROTECTED] Subject: Re: [SQL] staggered query? Hi Try this.. SELECT Col1 , Col2 FROM yourtable WHERE to_number(to_char(col1, 'SS'),'99') / 10 ) in (10,20,30,40,50,00); HTH Denis - Original Message - From: Vincent Ladlad [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, April 21, 2004 8:23 AM Subject: [SQL] staggered query? hi! im new to SQL, and i need to find a solution to this problem: i have a table with two columns, the first column is of type timestamp. the table contains hundreds of thousands of records. i need to get all the entries/records at every 10 seconds interval. example, given a table: hh/mm/ss | data --- 00:00:00 1 00:00:01 2 00:00:02 3 00:00:03 4 00:00:04 5 00:00:05 6 00:00:06 7 00:00:07 8 .. .. my query should return: 00:00:10 00:00:20 00:00:30 (etc) is this possible? if yes, how do i do it? thanks! --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.484 / Virus Database: 282 - Release Date: 5/27/2003 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(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 --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.484 / Virus Database: 282 - Release Date: 5/27/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.484 / Virus Database: 282 - Release Date: 5/27/2003 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Bug#960: WAS: Trigger calling a function HELP ME! (2)
Ok. I think I found the problem is related to this Bug. is there anywhere to check the status of this bug? regards, = Riccardo G. Facchini ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] rule's behavior with join interesting
On Wednesday 21 April 2004 21:07, Kemin Zhou wrote: Here I have a very simple case table1 table1_removed anotherTable create or replace RULE rec_remove as ON DELETE TO table1 do insert into table1_remove select old.*, a.acc from old g join anotherTable a on g.acc=a.other_acc; === the parser complained ERROR: relation *OLD* does not exist So I used select old.*, a.acc from table1 g join anotherTable a on g.acc=a.other_acc; This worked find. When I run delete on table1, 213 rows. tmp table received 213X213 = 45369 rows. each row is duplicated 213 times. The issue here is that although you can refer to values such as OLD.acc, OLD is not a table but more like single row. So, you probably want ...DO INSERT INSTO table1_remove SELECT old.*, a.acc FROM anotherTable a WHERE a.other_acc = OLD.acc; Your second example just ignored the OLD.acc altogether in the join, so of course you got an unconstraind join of 213 x 213. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Select from two databases
On Thursday 22 April 2004 08:00, Becky Alcorn wrote: Hi, I was wondering if there has been any further development on running SQL statements involving more than one database? We are porting a database from SQLServer to Postgres and some queries in related applications use joins on tables that are in different databases. We know we can modify the code to use dblink to accomplish much the same thing in Postgres. Does anyone know of a more elegant solution for Postgres than dblink? The standard response to this is to suggest using schemas. Any reason why this won't work in your case? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Order by YYYY MM DD in reverse chrono order trouble
Hello,
I'd love to be able to do that, but I cannot just ORDER BY uu.add_date,
because I do not have uu.add_date in the SELECT part of the statement.
The reason I don't have it there is because I need distinct MM DD
values back.
Is there a trick that I could use to make this more elegant?
Thanks,
Otis
--- Edmund Bacon [EMAIL PROTECTED] wrote:
Is there some reason you can't do this:
SELECT DISTINCT
date_part('year', uu.add_date), date_part('month', uu.add_date),
date_part('day', uu.add_date)
FROM uus INNER JOIN ui ON uus.user_id=ui.id INNER JOIN uu ON
ui.id=uu.user_id
WHERE uus.x_id=1
ORDER BY
uu.add_date DESC;
This might be faster, as you only have to sort on one field, and I
think it should give the desired results
[EMAIL PROTECTED] wrote:
Hello,
I am trying to select distinct dates and order them in the reverse
chronological order. Although the column type is TIMESTAMP, in this
case I want only , MM, and DD back.
I am using the following query, but it's not returning dates back in
the reverse chronological order:
SELECT DISTINCT
date_part('year', uu.add_date), date_part('month', uu.add_date),
date_part('day', uu.add_date)
FROM uus INNER JOIN ui ON uus.user_id=ui.id INNER JOIN uu ON
ui.id=uu.user_id
WHERE uus.x_id=1
ORDER BY
date_part('year', uu.add_date), date_part('month', uu.add_date),
date_part('day', uu.add_date) DESC;
This is what the above query returns:
date_part | date_part | date_part
---+---+---
2004 | 2 | 6
2004 | 4 |20
(2 rows)
I am trying to get back something like this:
2004 4 20
2004 4 19
2004 2 6
...
My query is obviously wrong, but I can't see the mistake. I was
wondering if anyone else can see it. Just changing DESC to ASC, did
not work.
Thank you!
Otis
---(end of
broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to
[EMAIL PROTECTED])
--
Edmund Bacon [EMAIL PROTECTED]
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] Order by YYYY MM DD in reverse chrono order trouble
Yes indeed, I seem to have misinterpreted that last one. My apologies.
The distinct solution I mentioned isn't going to solve it, you are absolutely right in
your example.
To get back on track:
You don't have to use a field in the select part of you query to be able to use it in
the order by clause. So using order by add_date should indeed work. Since it appears
that your add_date is a timestamp field (including time) it will order first on the
date part, and next on the time part.
E.g.:
2004-04-12 12:45:22
2004-04-12 09:55:25
2004-04-11 14:25:31
2004-04-11 11:11:25
Since you have a distinct only on the date parts of the timestamp this will make no
difference in the end result, it will still be sorted correctly on the date.
but I'm wondering if a GROUP BY wouldn't also be a good solution? like this:
---CODE
SELECT
date_part('year', uu.add_date), date_part('month', uu.add_date),
date_part('day', uu.add_date)
FROM uus INNER JOIN ui ON uus.user_id=ui.id INNER JOIN uu ON
ui.id=uu.user_id
WHERE uus.x_id=1
GROUP BY
date_part('year', uu.add_date), date_part('month', uu.add_date),
date_part('day', uu.add_date);
---END CODE
That way you don't need the DISTINCT part. The disadvantage however is that all the
fields in the select must eighter be a part of the GROUP BY clause, or be used in an
aggregate function.
Anyways: just descide whatever solution fits best for your needs.
Regards,
Stijn.
Otis wrote:
Hello,
But will this work even with my add_date column, which is a TIMESTAMP
field? Values in this column contain hours, minutes,
seconds, etc., so
won't DISTINCT return multiple rows for add_dates that _are_ distinct,
but are on the same day.
For example:
2004 04 02 11:22:33.034
2004 04 02 22:33:44.055
Thanks,
Otis
--- Stijn Vanroye [EMAIL PROTECTED] wrote:
Hello,
I'd love to be able to do that, but I cannot just ORDER BY
uu.add_date,
because I do not have uu.add_date in the SELECT part of the
statement.
The reason I don't have it there is because I need
distinct MM
DD
values back.
Is there a trick that I could use to make this more elegant?
yes, you could use:
SELECT DISTINCT ON (field1, field2) field1, field3, FieldN
from table
Regards,
Stijn Vanroye
---(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] Trigger calling a function HELP ME! (2)
On Wednesday 21 April 2004 16:16, [EMAIL PROTECTED] wrote: CREATE OR REPLACE FUNCTION public.imp_test_to_out_test(imp_test) RETURNS imp_test AS 'begin return $1; end;' LANGUAGE 'plpgsql' STABLE; CREATE OR REPLACE FUNCTION public.imp_test_trigger() RETURNS trigger AS 'begin return imp_test_to_out_test(new); end;' LANGUAGE 'plpgsql' STABLE; This is your problem. NEW is a special variable, and I don't think you can pass it into another function (other than as NEW.col1, NEW.col2, NEW.col3 etc). You can however use TG_NAME or TG_RELNAME to see what trigger/table called you. I find that's helpful. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Order by YYYY MM DD in reverse chrono order trouble
[EMAIL PROTECTED] writes: I'd love to be able to do that, but I cannot just ORDER BY uu.add_date, because I do not have uu.add_date in the SELECT part of the statement. Sure you can. Back around SQL89 there was a restriction that ORDER BY values had to appear in the SELECT list as well, but no modern database has such a restriction anymore ... regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Server Side C programming Environment Set up
On Thu, 2004-04-22 at 01:18, Peter Eisentraut wrote: Kemin Zhou wrote: IN chapter 33 Extending SQL 33.7.5 Writing Code when run pg_config --includedir-server I got /usr/local/pgsql/include/server but my machine does have this directory make install-all-headers It's explained in the installation instructions. That doesn't happen on most platforms in the standard package. Are you proposing that packagers create a postgresql-headers package and depend on that? If this is the suggested way of solving the headers portion of the problem then lets tell the packagers. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Order by YYYY MM DD in reverse chrono order trouble
Hello,
Hm, doesn't work for me:
[EMAIL PROTECTED] mydb= select distinct date_part('year', uu.add_date),
date_part('month', uu.add_date), date_part('day',
uu.add_date) from uus inner join ui on uus.user_id=ui.id inner join
uu on ui.id=uu.user_id where uus.subscriber_user_id=1 order by
uu.add_date desc;
ERROR: For SELECT DISTINCT, ORDER BY expressions must appear in target
list
I have this version of PostgreSQL installed: postgresql-7.3.4-3.rhl9
Thanks,
Otis
--- Tom Lane [EMAIL PROTECTED] wrote:
[EMAIL PROTECTED] writes:
I'd love to be able to do that, but I cannot just ORDER BY
uu.add_date,
because I do not have uu.add_date in the SELECT part of the
statement.
Sure you can. Back around SQL89 there was a restriction that ORDER
BY
values had to appear in the SELECT list as well, but no modern
database
has such a restriction anymore ...
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Server Side C programming Environment Set up
Peter Eisentraut [EMAIL PROTECTED] writes: Am Donnerstag, 22. April 2004 07:59 schrieb Tom Lane: For instance I've been meaning to ask what to do about this open bug report: https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=112244 Well, perhaps getting the tutorial to compile should be part of the tutorial itself. :) But that doesn't have anything to do with server side C programming. You don't need any PostgreSQL makefiles for that. Hm? src/tutorial contains two .c files that need to be compiled. The bug report is that the Makefile provided for this only works in the original build tree. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Server Side C programming Environment Set up
Rod Taylor [EMAIL PROTECTED] writes: On Thu, 2004-04-22 at 01:18, Peter Eisentraut wrote: make install-all-headers It's explained in the installation instructions. That doesn't happen on most platforms in the standard package. Depends what you mean by standard package? The PGDG and Red Hat RPMs certainly do install-all-headers. They are in the postgresql-devel package, which seems appropriate to me. I agree with the suggestion elsewhere in the thread about generalizing the contrib Makefile framework to the point that it could be installed as part of the -devel RPM, and then used to build user-written backend functions. Ideally you could take one of the contrib subdirectories by itself, and build it using only what is installed by postgresql-devel, without needing access to the original source or build trees. I don't think we are real close yet, though maybe Peter would have an idea what would be needed. (Note this would also provide a usable solution to the build-the-tutorial problem I mentioned.) regards, tom lane ---(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] Server Side C programming Environment Set up
On Thu, 2004-04-22 at 10:11, Peter Eisentraut wrote: Am Donnerstag, 22. April 2004 15:58 schrieb Rod Taylor: make install-all-headers It's explained in the installation instructions. That doesn't happen on most platforms in the standard package. It certainly happens in all the packages that have ever come by me (maybe after a little complaining). Okay, I'll start submitting patches for the packages we tend to use here. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Design Problem...
Our business has multiple cost/profit centers that I call business units,
these are in a table called buinessunits. We also have a table that holds a
list of services that are offerred by a business. Each business unit has
many services it offers; 1 businees unit = Many Services. We want to be
able to query the cost/revenue/profit generated by each business unit and by
each service. The problem is that it is possible that the service can be
switched to a different business unit, and then possibly back to the
original later on. I've looked at multiple configurations, but have not
found a design that I feel is good so far. Need to somehow track when a
particular service was associated with various businessunits. If you want
more info, I can publish the tables for you. This same type of problem
exists in at least two other areas of this database we are developing.
Please help.
Kind Regards,
-Ryan
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of [EMAIL PROTECTED]
Sent: Thursday, April 22, 2004 5:57 AM
To: [EMAIL PROTECTED]
Subject: Re: [SQL] Order by MM DD in reverse chrono order trouble
Hello,
I'd love to be able to do that, but I cannot just ORDER BY uu.add_date,
because I do not have uu.add_date in the SELECT part of the statement.
The reason I don't have it there is because I need distinct MM DD
values back. Is there a trick that I could use to make this more elegant?
Thanks,
Otis
--- Edmund Bacon [EMAIL PROTECTED] wrote:
Is there some reason you can't do this:
SELECT DISTINCT
date_part('year', uu.add_date), date_part('month', uu.add_date),
date_part('day', uu.add_date)
FROM uus INNER JOIN ui ON uus.user_id=ui.id INNER JOIN uu ON
ui.id=uu.user_id
WHERE uus.x_id=1
ORDER BY
uu.add_date DESC;
This might be faster, as you only have to sort on one field, and I
think it should give the desired results
[EMAIL PROTECTED] wrote:
Hello,
I am trying to select distinct dates and order them in the reverse
chronological order. Although the column type is TIMESTAMP, in this
case I want only , MM, and DD back.
I am using the following query, but it's not returning dates back in
the reverse chronological order:
SELECT DISTINCT
date_part('year', uu.add_date), date_part('month', uu.add_date),
date_part('day', uu.add_date)
FROM uus INNER JOIN ui ON uus.user_id=ui.id INNER JOIN uu ON
ui.id=uu.user_id WHERE uus.x_id=1
ORDER BY
date_part('year', uu.add_date), date_part('month', uu.add_date),
date_part('day', uu.add_date) DESC;
This is what the above query returns:
date_part | date_part | date_part
---+---+---
2004 | 2 | 6
2004 | 4 |20
(2 rows)
I am trying to get back something like this:
2004 4 20
2004 4 19
2004 2 6
...
My query is obviously wrong, but I can't see the mistake. I was
wondering if anyone else can see it. Just changing DESC to ASC, did
not work.
Thank you!
Otis
---(end of
broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to
[EMAIL PROTECTED])
--
Edmund Bacon [EMAIL PROTECTED]
---(end of broadcast)---
TIP 8: explain analyze is your friend
---(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] Server Side C programming Environment Set up
Am Donnerstag, 22. April 2004 15:58 schrieb Rod Taylor: make install-all-headers It's explained in the installation instructions. That doesn't happen on most platforms in the standard package. It certainly happens in all the packages that have ever come by me (maybe after a little complaining). ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Server Side C programming Environment Set up
Am Donnerstag, 22. April 2004 07:59 schrieb Tom Lane: Peter Eisentraut [EMAIL PROTECTED] writes: make install-all-headers That's not a complete solution though; the headers are only half the problem. Makefiles are the other half, and our story on them is pretty bad. For instance I've been meaning to ask what to do about this open bug report: https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=112244 Well, perhaps getting the tutorial to compile should be part of the tutorial itself. :) But that doesn't have anything to do with server side C programming. You don't need any PostgreSQL makefiles for that. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] rule's behavior with join interesting
Richard Huxton wrote: On Wednesday 21 April 2004 21:07, Kemin Zhou wrote: Here I have a very simple case table1 table1_removed anotherTable create or replace RULE rec_remove as ON DELETE TO table1 do insert into table1_remove select old.*, a.acc from old g join anotherTable a on g.acc=a.other_acc; === the parser complained ERROR: relation *OLD* does not exist So I used select old.*, a.acc from table1 g join anotherTable a on g.acc=a.other_acc; This worked find. When I run delete on table1, 213 rows. tmp table received 213X213 = 45369 rows. each row is duplicated 213 times. The issue here is that although you can refer to values such as OLD.acc, OLD is not a table but more like single row. So, you probably want ...DO INSERT INSTO table1_remove SELECT old.*, a.acc FROM anotherTable a WHERE a.other_acc = OLD.acc; Old is not a single row at all, it is a placeholder for the result set that is deleted in this case. The rule you probably want is: create rule rec_remove as on delete to table1 do insert into table1_remove select old.*, a.acc from anotherTable a where old.acc = a.other_acc; This unfortunately does NOT support all the other join types, since the parser does not let you use JOIN before any FROM and you have old already in your rangetable, even if you don't see it. Jan Your second example just ignored the OLD.acc altogether in the join, so of course you got an unconstraind join of 213 x 213. -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(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] Server Side C programming Environment Set up
Rod Taylor wrote: On Thu, 2004-04-22 at 01:18, Peter Eisentraut wrote: Kemin Zhou wrote: IN chapter 33 Extending SQL 33.7.5 Writing Code when run pg_config --includedir-server I got /usr/local/pgsql/include/server but my machine does NOT have this directory make install-all-headers It's explained in the installation instructions. That doesn't happen on most platforms in the standard package. Are you proposing that packagers create a postgresql-headers package and depend on that? If this is the suggested way of solving the headers portion of the problem then lets tell the packagers. As fas I am concerned, being a generalized programer, to program the serverside functionality I would need a header file that should include nearly all the server side functionality and a lib, presumably dynamically linkable lib file libpgserver.so. We already have the interface library libpq.so The server side header does not have to include everything. It only needs to be the parts that the user programer needs to manipulate (SPI?). I see a great advantage to be able to plug in some information_managing algorithms into Postgres. Kemin ** Proprietary or confidential information belonging to Ferring Holding SA or to one of its affiliated companies may be contained in the message. If you are not the addressee indicated in this message (or responsible for the delivery of the message to such person), please do not copy or deliver this message to anyone. In such case, please destroy this message and notify the sender by reply e-mail. Please advise the sender immediately if you or your employer do not consent to e-mail for messages of this kind. Opinions, conclusions and other information in this message represent the opinion of the sender and do not necessarily represent or reflect the views and opinions of Ferring. ** ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] lifetime of temp schema versus compiled image of plpgsql proc
Hi, this is pg 7.4.1 I am opening a connection to postgres starting a transaction executing a plpgsql function that creates temp tables executing a plpgsql function that populates the temp tables querying the temp table closing the transaction then on the same connection, I open a transaction, execute a plpgsql function that populates the temp tables and the function bombs with this error message: ERROR: schema pg_temp_8 does not exist I am not specifying on commit when creating the temp tables. Are temp tables created in a transaction discarded when the transaction ends? I'm not explicitly referencing pg_temp_8 in my stored function. Can someone explain what is going on? Have I given enough information? dennis pg-user at calico dash consulting dot com ---(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] lifetime of temp schema versus compiled image of plpgsql proc
Bruce Momjian writes: There is an FAQ item on this --- use EXECUTE. So I should be using EXECUTE for all access to the temp tables? ie inserts, and selects (in this case). Should I use execute for the table creation? Dennis ---(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] lifetime of temp schema versus compiled image of plpgsql proc
There is an FAQ item on this --- use EXECUTE. --- Dennis wrote: Hi, this is pg 7.4.1 I am opening a connection to postgres starting a transaction executing a plpgsql function that creates temp tables executing a plpgsql function that populates the temp tables querying the temp table closing the transaction then on the same connection, I open a transaction, execute a plpgsql function that populates the temp tables and the function bombs with this error message: ERROR: schema pg_temp_8 does not exist I am not specifying on commit when creating the temp tables. Are temp tables created in a transaction discarded when the transaction ends? I'm not explicitly referencing pg_temp_8 in my stored function. Can someone explain what is going on? Have I given enough information? dennis pg-user at calico dash consulting dot com ---(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 -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Is there an easy way to normalize-space with given string functions
Hi,
i am looking for something like
$ SELECT btrim(replace(' too many spaces! ', '\s+',' '), '');
too many spaces
i searched the function list and tried to combine to or more functions, but i
miss a replace function which uses regular expressions.
Do i have to write my own function or did i miss something? trimming is well
supported at the start and end of string, but no trimmin in the middle seems
to be possible.
kind regards
janning
---(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] lifetime of temp schema versus compiled image of plpgsql proc
Dennis [EMAIL PROTECTED] writes: then on the same connection, I open a transaction, execute a plpgsql function that populates the temp tables and the function bombs with this error message: ERROR: schema pg_temp_8 does not exist That's a bit hard to believe. Could we see a complete test case? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] lifetime of temp schema versus compiled image of plpgsql proc
Bruce Momjian [EMAIL PROTECTED] writes: Dennis wrote: So I should be using EXECUTE for all access to the temp tables? ie inserts, and selects (in this case). Should I use execute for the table creation? All access from plpgsql functions for temp tables should use EXECUTE, I think that advice is not correct nor relevant to Dennis' problem. You need EXECUTE if you are dropping and recreating temp tables within the lifetime of a single connection, but he didn't do that. Even if he did do it and hasn't told us so, that does not explain why the error message complains about the temp *schema* and not a temp table. There's something very strange here, because the temp schema name for a given session is definitely fixed for the life of the session. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Is there an easy way to normalize-space with given string functions
Janning Vygen [EMAIL PROTECTED] writes: i searched the function list and tried to combine to or more functions, but i miss a replace function which uses regular expressions. There isn't one in the SQL standard. Most people who need one write a one-liner function in plperl or pltcl. (Mind you, I don't know why we don't offer a built-in one --- the needed regex engine is in there anyway. I guess no one has gotten around to getting agreement on a syntax.) regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
