Re: [SQL] User Defined Functions Errors
Have a try with RAISE NOTE or RAISE EXCEPTION keep in mind that exceptions should be exceptional. So a good idea of whether to use them is to ask 'Do I expect such error' or 'is an explicit error useful for the caller'. I'ts often better to just return an empty relation |-Original Message- |From: A. Kulikov [mailto:[EMAIL PROTECTED] |Sent: Montag, 18. April 2005 20:32 |To: [email protected] |Subject: [SQL] User Defined Functions Errors | | |How to I return an error from inside a user defined function? For |example the following: | |CREATE or REPLACE FUNCTION drop_node (integer) RETURNS text |AS ' | |DECLARE | |mleft INTEGER; |mright INTEGER; | |BEGIN | |-- Check if the desired node exists |SELECT lft, rgt FROM structure WHERE id = $1 INTO mleft, mright; | |IF mleft IS NULL THEN | RETURN ''No entry found with an id of ''||$2; |END IF; | |-- Drop the node and its subtree |DELETE FROM structure WHERE lft >= mleft AND rgt <= mright; | |-- Close the gap |UPDATE structure SET rgt = rgt - (mright - mleft + 1) WHERE |rgt > mright; |UPDATE structure SET lft = lft - (mright - mleft + 1) WHERE |lft > mleft; | |RETURN ''ok''; | |END; |' |LANGUAGE 'plpgsql'; | |Should be terminated with an error @ RETURN "No Entry found"; instead |of returning the error text. | |best regards and thanks, | |Alex |-- |The mind is essential -- http://essentialmind.com/ | |---(end of |broadcast)--- |TIP 4: Don't 'kill -9' the postmaster | ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] "Money" Data Type Problem
Hai friends, I have a field with data type 'Money' with my table. I select this field a select query and displays the same in a textbox. While doing this a dollar ($) is prefixed to the actual table value. How to avoid this symbol so as to display the actual value only. Any format change needed in config files. Pls forward me the reply. Sreejith ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] can a function return a virtual table?
That was a nice answer - rather compleete. However at least I am questioning myself for a long time about what happens if one does a select from a SRF. The function may return millions of records (i.e. select * from x where a>1). Is this data streamed through the query process or does postgres create a temporary table. An "explain select * from srf()" just returns a function invocation. :-/ How does this work? |-Original Message- |From: Michael Fuhr [mailto:[EMAIL PROTECTED] |Sent: Dienstag, 19. April 2005 04:43 |To: Kai Hessing |Cc: [email protected] |Subject: Re: [SQL] can a function return a virtual table? | | |On Wed, Apr 13, 2005 at 03:44:25PM +0200, Kai Hessing wrote: |> |> This is the question i'm telling myself. It is because we |don't really |> delete table entries, just setting a status field to '-1'. So a valid |> select would look like: SELECT xyz, abc FROM (SELECT * FROM |tablex WHERE |> status > -1); | |I'll pick a nit and point out that the above isn't a valid query: | |test=> SELECT xyz, abc FROM (SELECT * FROM tablex WHERE status > -1); |ERROR: subquery in FROM must have an alias |HINT: For example, FROM (SELECT ...) [AS] foo. | |In this simple example you could omit the subquery altogether: | |SELECT xyz, abc FROM tablex WHERE status > -1; | |> It would be much nicer to have to write something like: |SELECT xyz, abc |> FROM active(tablex); where the function 'active(x)' returns a virtual |> table with all entries from table x where status is > -1. But sadly I |> have no idea how write such a function. Good old O'reilly |can't help (or |> i'm to dumb *g*). | |See the documentation for writing set-returning functions (SRFs). |The following links should get you started (if you're using a version |of PostgreSQL older than 8.0, then see the documentation for that |version): | |http://www.postgresql.org/docs/8.0/interactive/queries-table-ex |pressions.html#QUERIES-TABLEFUNCTIONS |http://www.postgresql.org/docs/8.0/interactive/xfunc-sql.html#AEN29503 |http://www.postgresql.org/docs/8.0/interactive/plpgsql-control- |structures.html#AEN32823 | |Another possibility would be to use views. You'd need to create a |view on each table. | |http://www.postgresql.org/docs/8.0/interactive/tutorial-views.html |http://www.postgresql.org/docs/8.0/interactive/sql-createview.html | |Yet another possibility would be to move the inactive rows to a |separate table. You could reconstruct the original table with a |UNION of the active and inactive tables. | |http://www.postgresql.org/docs/8.0/interactive/queries-union.html | |-- |Michael Fuhr |http://www.fuhr.org/~mfuhr/ | |---(end of |broadcast)--- |TIP 8: explain analyze is your friend | ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Getting the output of a function used in a where clause
Thanks Tom and Rod. There are indeed several additional conditions on the "real" query which prune the search space (I formulate a quick search box and filter on Lat/Lon's within the box). Since my user interface limits the search to a 30 mile radius, there are at most 81 results (in New York city, far fewer, for Why don't you use a GiST index which will index this bounding box search ? ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Query about SQL in PostgreSQL
Hi, I am new user of PostGreSQL 8.0.1. While using it i faced following issue. As SQL is Case insensetive Language So the Uper or Lower cases are not significant. But while using the database there is problem. If i Create new Table with name (tblstudent) then upon SQL queries it is fine to use Select * from tblstudent. However i face probel if i give the name of table in Capital letters i.e. if the name is (tblStudent) then upon using Select * from tblStudent, following error is appeard: ERROR: relation "tblst" does not exist And if i use the query Select * from "tblStudent" then it works fine. Please help me out in this regard. Nadeem Yahoo! Mail Mobile Take Yahoo! Mail with you! Check email on your mobile phone.
Re: [SQL] Query about SQL in PostgreSQL
Postgres has the weird behavour to compare identifies case sensitive BUT to downcast any non-quoted identifier inside an SQL statement. So it is reccomended to just use lower case (for readability) -Original Message-From: Muhammad Nadeem Ashraf [mailto:[EMAIL PROTECTED]Sent: Dienstag, 19. April 2005 11:49To: [email protected]: [SQL] Query about SQL in PostgreSQL Hi, I am new user of PostGreSQL 8.0.1. While using it i faced following issue. As SQL is Case insensetive Language So the Uper or Lower cases are not significant. But while using the database there is problem. If i Create new Table with name (tblstudent) then upon SQL queries it is fine to use Select * from tblstudent. However i face probel if i give the name of table in Capital letters i.e. if the name is (tblStudent) then upon using Select * from tblStudent, following error is appeard: ERROR: relation "tblst" does not exist And if i use the query Select * from "tblStudent" then it works fine. Please help me out in this regard. Nadeem Yahoo! Mail MobileTake Yahoo! Mail with you! Check email on your mobile phone.
Re: [SQL] Query about SQL in PostgreSQL
On 19.04.2005 11:48 Muhammad Nadeem Ashraf wrote: > Hi, > I am new user of PostGreSQL 8.0.1. While using it i faced following > issue. As SQL is Case insensetive Language So the Uper or Lower cases > are not significant. But while using the database there is problem. > If i Create new Table with name (tblstudent) then upon SQL queries it is > fine to use Select * from tblstudent. However i face probel if i give > the name of table in Capital letters i.e. if the name is (tblStudent) > then upon using Select * from tblStudent, following error is appeard: > ERROR: relation "tblst" does not exist > > And if i use the query Select * from "tblStudent" then it works fine. > > This is how SQL is defined in the ANSI standard. If you use double quotes around your object names, then it's case sensitiv. If you omit the double quotes it's not. General rule: do not use double quotes at all, and your fine (this is true for other DBMS like Oracle and Firebird as well) Thomas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Query about SQL in PostgreSQL
On Apr 19, 2005, at 5:48 AM, Muhammad Nadeem Ashraf wrote: Hi, I am new user of PostGreSQL 8.0.1. While using it i faced following issue. As SQL is Case insensetive Language So the Uper or Lower cases are not significant. But while using the database there is problem. If i Create new Table with name (tblstudent) then upon SQL queries it is fine to use Select * from tblstudent. However i face probel if i give the name of table in Capital letters i.e. if the name is (tblStudent) then upon using Select * from tblStudent, following error is appeard: ERROR: relation "tblst" does not exist And if i use the query Select * from "tblStudent" then it works fine. SQL is case insensitive. However, identifiers (table names, column names, index names, etc.) are case sensitive in Postgresql. So, your operational understanding of how things work seems to be correct. I tend to use all lower-case, just because it saves me some thought, but I know some folks like to use a mix of upper and lower case so that they can be used in a web application, for example. If you do use mixed or upper case, you do have to quote them. If you still have a problem, could you be more specific about what it is? Sean ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Query about SQL in PostgreSQL
Muhammad Nadeem Ashraf wrote: Hi, I am new user of PostGreSQL 8.0.1. While using it i faced following issue. As SQL is Case insensetive Language So the Uper or Lower cases are not significant. But while using the database there is problem. If i Create new Table with name (tblstudent) then upon SQL queries it is fine to use Select * from tblstudent. However i face probel if i give the name of table in Capital letters i.e. if the name is (tblStudent) then upon using Select * from tblStudent, following error is appeard: ERROR: relation "tblst" does not exist And if i use the query Select * from "tblStudent" then it works fine. If you quote your table-names when you create them you should always quote them when you use them. If you don't quote your table-names when you create them, there is no need to quote them when you use them. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Query about SQL in PostgreSQL
On Tue, Apr 19, 2005 at 02:48:46AM -0700, Muhammad Nadeem Ashraf wrote: > Hi, > I am new user of PostGreSQL 8.0.1. While using it i faced following > issue. As SQL is Case insensetive Language So the Uper or Lower cases > are not significant. But while using the database there is problem. > If i Create new Table with name (tblstudent) then upon SQL queries it > is fine to use Select * from tblstudent. However i face probel if i give > the name of table in Capital letters i.e. if the name is (tblStudent) > then upon using Select * from tblStudent, following error is appeard: > ERROR: relation "tblst" does not exist > > And if i use the query Select * from "tblStudent" then it works fine. > > > Please help me out in this regard. What exactly is the problem? Just use the names of tables you have created. -- __ "Nothing is as subjective as reality" Reinoud van Leeuwen[EMAIL PROTECTED] http://www.xs4all.nl/~reinoud __ ---(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] can a function return a virtual table?
On Tue, Apr 19, 2005 at 09:34:43 +0200, KÖPFERL Robert <[EMAIL PROTECTED]> wrote: > That was a nice answer - rather compleete. > > However at least I am questioning myself for a long time about what happens > if one does a select from a SRF. The function may return millions of records > (i.e. select * from x where a>1). Is this data streamed through the query > process or does postgres create a temporary table. > > An "explain select * from srf()" just returns a function invocation. :-/ > How does this work? If the function isn't written using a language of SQL, it isn't going to optimize well when you only want to see part of the table. ---(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] Query about SQL in PostgreSQL
On Tue, Apr 19, 2005 at 11:59:58AM +0200, KÖPFERL Robert wrote: > Postgres has the weird behavour to compare identifies case sensitive BUT to > downcast any non-quoted identifier inside an SQL statement. > So it is reccomended to just use lower case (for readability) Or never double-quote identifiers. I sort of don't believe exactly the original poster's report, however, because the error message wasn't right no matter what. I'd like to see a real session transcript. Also, this probably belongs on -general. A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] "Money" Data Type Problem
On Tue, 19 Apr 2005, sreejith s wrote: > Hai friends, > I have a field with data type 'Money' with my table. I select this > field a select query and displays the same in a textbox. While doing > this a dollar ($) is prefixed to the actual table value. How to avoid > this symbol so as to display the actual value only. Any format change > needed in config files. Pls forward me the reply. I believe money uses the currency symbol based on LC_MONETARY so there might exist one without a symbol, however you'd probably be best off replacing money with an appropriate numeric field. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] trying to do an update a bit confused.
update tblcase set merchandisetotal = ( COALESCE(( SELECT sum(m.quantity::numeric * m.amount) AS merchandiseamount FROM tblmerchandise m WHERE m.caseid = tblcase.caseid AND m.clientnum::text = tblcase.clientnum::text), 0.0) ) I tried running the above and it wants to do a sum on all the records in tblcase instead of just the one being updated, what am I doing wrong? Joel Fradkin
Re: [SQL] Query about SQL in PostgreSQL
"Muhammad Nadeem Ashraf" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi, > I am new user of PostGreSQL 8.0.1. While using it i faced following issue. > As SQL is Case insensetive Language So the Uper or Lower cases are not > significant. But while using the database there is problem. > If i Create new Table with name (tblstudent) then upon SQL queries it is > fine to use Select * from tblstudent. However i face probel if i give the > name of table in Capital letters i.e. if the name is (tblStudent) then > upon using Select * from tblStudent, following error is appeard: > ERROR: relation "tblst" does not exist That should have worked. Can you provide a session transcript that shows this? > > And if i use the query Select * from "tblStudent" then it works fine. > > > Please help me out in this regard. > Nadeem > > > > - > Yahoo! Mail Mobile > Take Yahoo! Mail with you! Check email on your mobile phone. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] DateAdd function ?
"Zlatko Matiæ" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
>I am currently migrating from MSDE to PostgreSQL and have to rewrite the
>function that is calculating next date of sampling...
> In MSDE there is a DateAdd function. I can't find the appropriate function
> in postgre. Can you help me?
There is no "dateadd" function in PostgreSQL, but you can write your own
easily enough. The following should give you an idea of the logic you can
use:
jeck=# select current_date;
date
2005-04-19
(1 row)
jeck=# select current_date + cast('1 day' as interval);
?column?
2005-04-20 00:00:00-05
(1 row)
jeck=# select current_date + cast('3 months' as interval);
?column?
2005-07-19 00:00:00-05
(1 row)
jeck=# select current_date + 3 * cast('1 month' as interval);
?column?
2005-07-19 00:00:00-05
(1 row)
> The function in MSDE is the following:
>
> ALTER FUNCTION dbo.slisp
> (
> @UCESTALOST_BROJ int,
> @UCESTALOST_JEDINICA nvarchar (50),
> @DATUM_ISPITIVANJA datetime
> )
> RETURNS datetime
> AS
> BEGIN
> DECLARE @SLISP datetime
> IF @UCESTALOST_JEDINICA='m' SET @SLISP=DATEADD(month, @UCESTALOST_BROJ,
> MAX(@DATUM_ISPITIVANJA))
> IF @UCESTALOST_JEDINICA='g' SET @SLISP=DATEADD(year, @UCESTALOST_BROJ,
> MAX(@DATUM_ISPITIVANJA))
> IF @UCESTALOST_JEDINICA='d' SET @SLISP=DATEADD(day, @UCESTALOST_BROJ,
> MAX(@DATUM_ISPITIVANJA))
> RETURN @SLISP
> END
>
> Thanks.
>
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Query about SQL in PostgreSQL
Forget that message. I need another cup of coffee! "Jeff Eckermann" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > "Muhammad Nadeem Ashraf" <[EMAIL PROTECTED]> wrote in message > news:[EMAIL PROTECTED] >> Hi, >> I am new user of PostGreSQL 8.0.1. While using it i faced following >> issue. As SQL is Case insensetive Language So the Uper or Lower cases are >> not significant. But while using the database there is problem. >> If i Create new Table with name (tblstudent) then upon SQL queries it is >> fine to use Select * from tblstudent. However i face probel if i give the >> name of table in Capital letters i.e. if the name is (tblStudent) then >> upon using Select * from tblStudent, following error is appeard: >> ERROR: relation "tblst" does not exist > > That should have worked. Can you provide a session transcript that shows > this? > >> >> And if i use the query Select * from "tblStudent" then it works fine. >> >> >> Please help me out in this regard. >> Nadeem >> >> >> >> - >> Yahoo! Mail Mobile >> Take Yahoo! Mail with you! Check email on your mobile phone. > > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] DateAdd function ?
"Jeff Eckermann" <[EMAIL PROTECTED]> writes:
> There is no "dateadd" function in PostgreSQL, but you can write your own
> easily enough. The following should give you an idea of the logic you can
> use:
> jeck=# select current_date + cast('1 day' as interval);
Alternatively, maybe you want to use the date-plus/minus-integer operators.
regression=# select current_date;
date
2005-04-19
(1 row)
regression=# select current_date + 1;
?column?
2005-04-20
(1 row)
regression=# select current_date - 1;
?column?
2005-04-18
(1 row)
There isn't really a date-plus-interval operator --- what's happening
there is the date is implicitly promoted to timestamp and then
timestamp-plus-interval is used. You might want this if you want to
add symbolic quantities like '2 months' or '1 year', but for small
integer numbers of days, the integer operations are faster and simpler
to use.
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] trying to do an update a bit confused.
You're most probably missing a Where clause after the parentensis. see: -Original Message-From: Joel Fradkin [mailto:[EMAIL PROTECTED]Sent: Dienstag, 19. April 2005 16:06To: [email protected]: [SQL] trying to do an update a bit confused. update tblcase set merchandisetotal = ( COALESCE(( SELECT sum(m.quantity::numeric * m.amount) AS merchandiseamount FROM tblmerchandise m WHERE m.caseid = tblcase.caseid AND m.clientnum::text = tblcase.clientnum::text), 0.0) ) WHERE ; I tried running the above and it wants to do a sum on all the records in tblcase instead of just the one being updated, what am I doing wrong? Joel Fradkin
[SQL] Debet-Credit-Balance Calculation
Dear All,I have problem to calculation
balance from debet and credit.my transaction table:
id |
trx_timestamptz |
account | trx_type_id |
amount++--+-+-
3 | 2005-04-14 17:16:49+08 | 01.2010100.2
| 1 |
100.00 4 | 2005-04-14 17:17:12+08 | 01.2010100.2
| 1
| 5.00 5 | 2005-04-14 17:17:12+08 |
01.2010100.3 | 1
| 60.00 6 | 2005-04-14 17:17:47+08 | 01.2010100.2
| 2
| 7000.00 7 | 2005-04-16 00:32:50+08 |
01.2010100.3 | 1
| 2.00 11 | 2005-04-16 02:45:06+08 |
01.2010100.2 | 1
| 10.00 12 | 2005-04-16 02:46:02+08 | 01.2010100.2
| 1
| 2.00 13 | 2005-04-16 02:46:59+08 |
01.2010100.2 | 2
| 163000.00 14 | 2005-04-16 02:50:17+08 | 01.2010100.5
| 1 |
10.00 15 | 2005-04-16 02:53:42+08 | 01.2010301.1
| 1 |
10.00 16 | 2005-04-16 02:57:22+08 | 01.2010100.1
| 1 |
200.00 17 | 2005-04-16 23:56:44+08 | 01.2010200.0
| 1 |
1000.00 18 | 2005-04-17 18:58:57+08 | 01.2010100.3
| 1 |
10.00 19 | 2005-04-17 19:13:05+08 | 01.2010100.2
| 1 |
10.00 20 | 2005-04-17 19:13:45+08 | 01.2010100.2
| 1 |
20.00 21 | 2005-04-17 19:15:36+08 | 01.2010100.2
| 1
| 5.00 22 | 2005-04-17 19:17:17+08 |
01.2010100.5 | 2
| 1.00 23 | 2005-04-17 19:18:06+08 |
01.2010100.4 | 1
| 20.00 24 | 2005-04-17 21:45:31+08 | 01.2010100.2
| 1
| 9000.00 25 | 2005-04-17 22:16:08+08 |
01.2010100.6 | 1
| 10.00
-
CREATE TABLE "public"."transactions" (
"id" SERIAL, "trx_timestamptz" TIMESTAMP(0) WITH TIME ZONE DEFAULT
('now'::text)::timestamp(6) with time zone NOT NULL, "account"
CHAR(16) NOT NULL, "trx_type_id" INTEGER NOT NULL,
"amount" NUMERIC(15,2) DEFAULT 0 NOT NULL, "uid" INTEGER NOT NULL,
CONSTRAINT "transactions_pkey" PRIMARY KEY("id")) WITH
OIDS;
---
and transaction type :
id | trx_name |
t_type+--+ 1 | Credit |
CR 2 | Debet | DB
-CREATE TABLE "public"."trx_type" ( "id" SERIAL,
"trx_name" VARCHAR(32), "t_type" CHAR(2),
CONSTRAINT "trx_type_pkey" PRIMARY KEY("id"), CONSTRAINT
"trx_typei_check0" CHECK ((t_type = 'CR'::bpchar) OR (t_type =
'DB'::bpchar))) WITH OIDS;
-
so, i'm using this query:
SELECT trans.id,
trans.trx_timestamptz, trans.account, trans.debet,
trans.creditFROM (
SELECT
transactions.id,
transactions.trx_timestamptz,
transactions.account,
CASE WHEN trx_type.t_type =
'DB' THEN
transactions.amount
ELSE
0 END AS
debet, CASE
WHEN trx_type.t_type = 'CR'
THEN
transactions.amount
ELSE
0 END AS credit
FROM
transactions INNER JOIN trx_type ON
(transactions.trx_type_id = trx_type.id)
) AS trans
result from above query :
id |
trx_timestamptz |
account | debet
|
credit++--+---+-
3 | 2005-04-14 17:16:49+08 | 01.2010100.2
| 0 | 100.00
4 | 2005-04-14 17:17:12+08 | 01.2010100.2
| 0 |
5.00 5 | 2005-04-14 17:17:12+08 | 01.2010100.3
| 0 |
60.00 6 | 2005-04-14 17:17:47+08 | 01.2010100.2 |
7000.00 |
0 7 | 2005-04-16 00:32:50+08 | 01.2010100.3
| 0 |
2.00 11 | 2005-04-16 02:45:06+08 | 01.2010100.2
| 0 |
10.00 12 | 2005-04-16 02:46:02+08 | 01.2010100.2
| 0 |
2.00 13 | 2005-04-16 02:46:59+08 | 01.2010100.2 | 163000.00
| 0 14 |
2005-04-16 02:50:17+08 | 01.2010100.5
| 0 |
10.00 15 | 2005-04-16 02:53:42+08 | 01.2010301.1
| 0 |
10.00 16 | 2005-04-16 02:57:22+08 | 01.2010100.1
| 0 |
200.00 17 | 2005-04-16 23:56:44+08 | 01.2010200.0
| 0 | 1000.00 18 |
2005-04-17 18:58:57+08 | 01.2010100.3
| 0 |
10.00 19 | 2005-04-17 19:13:05+08 | 01.2010100.2
| 0 |
10.00 20 | 2005-04-17 19:13:45+08 | 01.2010100.2
| 0 |
20.00 21 | 2005-04-17 19:15:36+08 | 01.2010100.2
| 0 |
5.00 22 | 2005-04-17 19:17:17+08 | 01.2010100.5 |
1.00 |
0 23 | 2005-04-17 19:18:06+08 | 01.2010100.4
| 0 |
2
Re: [SQL] trying to do an update a bit confused.
I am not updating 1 record. I have : WHERE m.caseid = tblcase.caseid AND m.clientnum::text = tblcase.clientnum::text) Which should do the aggregate on the record that is being updated (least as I understood it). It should update all record in case with either 0 if there are no merchandise records or do a sum of the merch records for the case being updated. Joel I also never heard back on the merge join issue (why do I need to specify them off on one machine and it runs faster on the other). I am guessing it is memory related and config related, but I am about to give up on postgres as I am just not getting all my views to run fast enough. You guys could say my views are bad SQL design etc, but they run fine in MSSQL. I don’t mind visiting each one to make them better, but I am just not able to increase the speed on all of them. The last one about assoc finally did run in 3 secs with merge joins off which is pretty fast, but now I have others that seem pretty simple to me and yet run very slow. You're most probably missing a Where clause after the parentensis. see: -Original Message- From: Joel Fradkin [mailto:[EMAIL PROTECTED] Sent: Dienstag, 19. April 2005 16:06 To: [email protected] Subject: [SQL] trying to do an update a bit confused. update tblcase set merchandisetotal = ( COALESCE(( SELECT sum(m.quantity::numeric * m.amount) AS merchandiseamount FROM tblmerchandise m WHERE m.caseid = tblcase.caseid AND m.clientnum::text = tblcase.clientnum::text), 0.0) ) WHERE ; I tried running the above and it wants to do a sum on all the records in tblcase instead of just the one being updated, what am I doing wrong? Joel Fradkin
Re: [SQL] tsearch2
On Tue, 19 Apr 2005, Dan Feiveson wrote:
Thanks Oleg!
Did as you recommended:
set_curcfg('default');
got new error running query containing:
to_tsquery('advanced|tech');
rank(avectors,a2);
ERROR: ExecMakeTableFunctionResult: expression is not a function call
Past message board suggests this is a pg error, not tsearch2 -- do you know
of any workarounds?
upgrade your postgresql
Thanks for all your help Oleg,
Dan
- Original Message -
From: "Oleg Bartunov"
To: "Dan Feiveson" <[EMAIL PROTECTED]>
Sent: Monday, April 18, 2005 10:13 PM
Subject: Re: [SQL] tsearch2
On Mon, 18 Apr 2005, Dan Feiveson wrote:
Hi Oleg,
Am getting when running certain functions in tsearch2:
ERROR: could not find tsearch config by locale
Message boards suggest problem comes from LC_CTYPE and LC_COLLATE not
being
specifiec in cfg (which in our case they are not). Wondering what they
should be set to and if that might affect our existing settings.
I see no problem if you follow instructions from documentation.
A lot of people use tsearch2 and I don't believe your case is very
specific. You may, at least, use set_curcfg('your_configuration_name')
to set default configuration and show_curcfg() to see current config:
test=# select oid,* from pg_ts_cfg;
oid | ts_name | prs_name |locale
-+-+--+--
6214174 | default | default | C
6214175 | default_russian | default | ru_RU.KOI8-R
6214176 | simple | default |
(3 rows)
test=# select show_curcfg();
show_curcfg
-
6214175
(1 row)
My default config is 'default_russian' which corresponds to my locale
setup 'ru_RU.KOI8-R' and I have no problem. If your locale is 'C', than
you should use 'default' configuration.
test=# select set_curcfg('default');
set_curcfg
(1 row)
test=# select show_curcfg();
show_curcfg
-
6214174
(1 row)
You need to grant permission to use on tseach2 configuration tables (
pg_ts_* ).
Thanks a lot Oleg,
Dan
- Original Message -
From: "Oleg Bartunov"
To: "Dan Feiveson" <[EMAIL PROTECTED]>
Cc:
Sent: Monday, April 18, 2005 4:19 PM
Subject: Re: [SQL] tsearch2
Dan,
I don't remember what the probem you have ?
Oleg
On Mon, 18 Apr 2005, Dan Feiveson wrote:
Hi Oleg,
Still trying to get tsearch2 to work ... from archived message board
it
looks like our problem is that we don't have LC_CTYPE and LC_COLLATE
established.
We're running 7.3.4 - are there any potential pitfalls if we set
LC_CTYPE and LC_COLLATE (in conjunction with other settings? -- We set
the
locale of C on our current configuration to allow LIKE searches to use
indexes - will this be affected? Also what grants do we need to run to
get
a non super user working with tsearch2?
Thanks again for all your help,
Dan
Dan Feiveson
DataJoe LLC
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: [email protected], http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(end of
broadcast)---
TIP 1: subscribe and unsubscribe commands go to
[EMAIL PROTECTED]
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: [email protected], http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: [email protected], http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(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] trying to do an update a bit confused.
On 4/19/05, Joel Fradkin <[EMAIL PROTECTED]> wrote: > > > > update tblcase set merchandisetotal = > > ( > > COALESCE(( SELECT sum(m.quantity::numeric * m.amount) AS merchandiseamount > >FROM tblmerchandise m > > WHERE m.caseid = tblcase.caseid AND m.clientnum::text = > tblcase.clientnum::text), 0.0) > > ) > > Put the coalesce inside the select: update tblcase set merchandisetotal = (SELECT COALESCE(sum(m.quantity::numeric * m.amount),0.0) AS merchandiseamount FROM tblmerchandise m WHERE m.caseid = tblcase.caseid AND m.clientnum::text = tblcase.clientnum::text) -- Regards, DBA* Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Debet-Credit-Balance Calculation
I think you forget FOREIGN KEY:
transactions.trx_type_id -> trx_type.id
MAMH> Dear All,
MAMH> I have problem to calculation
MAMH> balance from debet and credit.
MAMH> my transaction table:
...
MAMH> CREATE TABLE "public"."transactions" (
MAMH>
MAMH> "id" SERIAL,
MAMH> "trx_timestamptz" TIMESTAMP(0) WITH TIME ZONE DEFAULT
MAMH> ('now'::text)::timestamp(6) with time zone NOT NULL,
MAMH> "account"
MAMH> CHAR(16) NOT NULL,
MAMH> "trx_type_id" INTEGER NOT NULL,
MAMH>
MAMH> "amount" NUMERIC(15,2) DEFAULT 0 NOT NULL,
MAMH> "uid" INTEGER NOT NULL,
MAMH> CONSTRAINT "transactions_pkey" PRIMARY KEY("id")
MAMH> ) WITH
MAMH> OIDS;
...
MAMH> and transaction type :
...
MAMH> CREATE TABLE "public"."trx_type" (
MAMH> "id" SERIAL,
MAMH> "trx_name" VARCHAR(32),
MAMH> "t_type" CHAR(2),
MAMH>
MAMH> CONSTRAINT "trx_type_pkey" PRIMARY KEY("id"),
MAMH> CONSTRAINT
MAMH> "trx_typei_check0" CHECK ((t_type = 'CR'::bpchar) OR (t_type =
MAMH> 'DB'::bpchar))
MAMH> ) WITH OIDS;
MAMH> -
MAMH> so, i'm using this query:
...
MAMH> ... query eat all my cpu process and take a long time ...
--
regards,
Mihail mailto:[EMAIL PROTECTED]
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] Debet-Credit-Balance Calculation
Oops! [EMAIL PROTECTED] ("Muhyiddin A.M Hayat") was seen spray-painting on a
wall:
> everything is ok, but when record > 100 that query eat all my
> cpu process and take a long time, i have wait for 3 mimutes
> but query doesn't finish. (pgsql-8.0-1 running on Dual Xeon 2.8 and
> 2GB of RAM)
What you're asking for is fairly much inherently exceedingly
expensive, and that's not really a PostgreSQL issue, it would be much
the same with any database.
The cost of the balance calculation for the first row may be 1.
For row 2, it's 1+1 = 2.
For row 3, it needs the balance from #2, so cost = 2+1 = 3.
Those add up, so the cost leaps thus:
Individual costs RowAggregate
1 1
1 + 2 = 3 4
1 + 2 + 3 = 6 10
1 + 2 + 3 + 4 = 10 20
and so forth...
The "naive" algorithm for this essentially results in the cost of the
query increasingly with O(n^3) where n is the number of elements in
the table.
You can get closer to O(n) by cacheing balances, but that will _not_
fall in an obvious way from an SQL query.
There is an easy way to do this; write a plpgsql set returning
function which adds the balance to the last column of the table. That
query will always have a cost in both time and memory proportional to
the size of the table, and the memory cost may bite you as table size
grows...
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','gmail.com').
http://linuxdatabases.info/info/x.html
"It's like a house of cards that Godzilla has been blundering
through." -- Moon, describing how system messages work on ITS
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
