Re: [SQL] Function error
Hi , Thanx a lot it worked . Is there any equivalent of dateadd function in postgres ? Regards, -Sugandha - Original Message - From: "Janning Vygen" <[EMAIL PROTECTED]> To: "Sugandha Shah" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, August 13, 2002 12:31 PM Subject: Re: [SQL] Function error > Am Dienstag, 13. August 2002 08:06 schrieb Sugandha Shah: > > Hi , > > > > I'm porting MS- SQL stored procedure to postgres . I'm getting this > > error : > > > > Error occurred while executing PL/pgSQL function > > sel_free_disk_space line 7 at SQL statement > > SELECT query has no destination for result data. > > If you want to discard the results, use PERFORM instead. > > you need to SELECT INTO if you want to set a varaible in plpgsql > > try this: > DECLARE > var_free integer; > -- var_free is used just to name it differently from the column name > BEGIN > SELECT INTO var_free > free from logical_drive where computer_id = $1 and > letter = upper($2); > IF var_free THEN > ... > > > Janning > > > CREATE FUNCTION sel_free_disk_space(int4,bpchar) RETURNS integer > > AS ' DECLARE > > -- Declare variable to store the free space. > > free INTEGER; > > > > BEGIN > >select free from logical_drive where computer_id = $1 and > > letter = upper($2); > > IF free IS NULL THEN > >RETURN -1; > > END IF; > > > > RETURN free; > > END; > > 'LANGUAGE 'plpgsql'; > > > > > > I'm not able to understand what I'm missing ? > > > > Secondly is there any equivalent of exec for postgres ? > > > > Any help will be highly appreciated. > > > > Regards, > > -Sugandha > > -- > Planwerk 6 /websolutions > Herzogstraße 86 > 40215 Düsseldorf > > fon 0211-6015919 > fax 0211-6015917 > http://www.planwerk6.de > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Function error
> Hi , > > Thanx a lot it worked . > > Is there any equivalent of dateadd function in postgres ? I highly recommend you actually read the Postgres manual's entries on date and time manipulation. You can just add intervals to dates: SELECT datefield + INTERVAL '1 month'; Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Conversion
On Tue, Aug 13, 2002 at 03:14:38PM +0800, Christopher Kings-Lynne wrote: > > http://www.postgresql.org/idocs/index.php?datatype-datetime.html says: > > > > The types abstime and reltime are lower precision types which are used > > internally. You are discouraged from using any of these types in new > > applications and are encouraged to move any old ones over when > > appropriate. Any or all of these internal types might disappear in a > > future release. > > Yes, but in absence of: > > SELECT EXTRACT(TIMESTAMP FROM EPOCH '12341234234'); Sounds nice :) > (Hint Hint Thomas!!!) > > It's all he can do. I suggest using the syntax above to convert his integer > column to a timestamp column. Sure. I use the same. But I don't like it because of that caution :( The other way is SELECT 'epoch'::timestamp + (int4field::text || 's')::interval, but it's much much slower... And it seems not to handle timestamps after 2038-01-19. -- Fduch M. Pravking ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Is this valid?
On Mon, 2002-08-12 at 23:58, Weiping He wrote: > Wei Weng wrote: > > >I am not sure if this is the right mailing list I talk to. Please let me > >know if I had violated any unwritten rules. :) > > > >I have a global variable PGconn* m_pgconn that is the connection handle > >for connecting to the postgresql database. Can I access/use the handle > >from multiple threads? Say I have a thread that does some insertion > >through this handle/database connection(m_pgconn) and also another > >thread that do some insertion *on the same table* through this > >handle(m_pgconn), will that break? > > > >Thanks > > > > > > > > > I don't think it's a good idea to use global variable > in multi-thread environment. > I think use different handle in differents is better. > > libpq is thread safe, but it doesn't necessary mean > that you could free of your own code. > Is it going to be resource draining if I make lots of connections in a very short time? -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Delete function without knowing the elements
All, I created a function that I can't seem to delete. [no sure how many parameters] CREATE OR REPLACE FUNCTION insertEntry (int4, varchar, varchar, numeric, varchar, timestamp, varchar, int4, numeric, varchar, int4, ,) RETURNS INT4 AS ' . END; ' LANGUAGE 'plpgsql'; How can I delete this without knowing how many I used to create it? This is what I used to delete others in the past. DROP FUNCTION insertEntry(int4, varchar, varchar, ...); Thanks, -pete ---(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] Delete function without knowing the elements
Use \df function_name to get the argument list and then drop it. > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Peter Atkins > Sent: Tuesday, August 13, 2002 12:30 PM > To: [EMAIL PROTECTED] > Subject: [SQL] Delete function without knowing the elements > > > All, > > I created a function that I can't seem to delete. > > [no sure how many parameters] > > CREATE OR REPLACE FUNCTION insertEntry (int4, varchar, varchar, numeric, > varchar, timestamp, varchar, int4, numeric, varchar, int4, ,) > RETURNS INT4 AS ' > . > END; > ' LANGUAGE 'plpgsql'; > > How can I delete this without knowing how many I used to create > it? This is > what I used to delete others in the past. > > DROP FUNCTION insertEntry(int4, varchar, varchar, ...); > > Thanks, > -pete > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(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] pg_dump's octal strings -> oracle data load
Hi, I am working on the oracle port of my pg application and my data load script contains \012 characters generated by pg. Anyone have any ideas on what to change these to so that: 1) oracle doesn't barf during the load (^M causes this... I'm loading from a -D flagged pg_dump sql script not sql loader) 2) I get clean line breaks in perl generated html (^L does not do this.) ? Obviously would do it but I'm hoping there is a better candidate for search and replace. Thanks! --- Thomas Good e-mail: [EMAIL PROTECTED] Programmer/Analyst phone: (+1) 718.818.5528 Residential Services fax: (+1) 718.818.5056 Behavioral Health Services, SVCMC-NY mobile: (+1) 917.282.7359 -- -- SQL Clinic - An Open Source Clinical Record www.sqlclinic.net ---(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] update on a large table
Thanks Doug, I was doing an UPATE on 100 million rows and I was updating an indexed column, it was also the column that I was basing my seach on. UPDATE "Calls" SET "GroupCode"='100 my street' WHERE "GroupCode"='' AND "Site"='05' GroupCode was Indexed. I dropped the index and the query ran in under one hour. Now I have been running Vaccum Analyze for three days.. Thanks, -Aaron Held ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] concurrent connections is worse than serialization?
I have a testing program that uses 30 concurrent connections (max_connections = 32 in my postgresql.conf) and each does 100 insertions to a simple table with index. It took me approximately 2 minutes to finish all of them. But under the same environment(after "delete From test_table, and vacuum analyze"), I then queue up all those 30 connections one after another one (serialize) and it took only 30 seconds to finish. Why is it that the performance of concurrent connections is worse than serializing them into one? I was testing them using our own (proprietary) scripting engine and the extension library that supports postgresql serializes the queries by simply locking when a query manipulates a PGconn object and unlocking when it is done. (And similiarly, it creates a PGconn object on the stack for each concurrent queries.) Thanks -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Few Queries
Hi ,
I'm still facing few problems and hence the query
. I have searched the archives as well as read the manual.
1. I 'm firing a query and it returns the value
in variable which I need to pass to other query . Is this a right way to
pass the value ? I'm newbie to this Database and hence facing
lot of syntax problems.
CREATE FUNCTION del_old_history() RETURNS bool AS
'declare var_history_age_limit int4;
set_time datetime;BEGIN select into
var_history_age_limit history_age_limit from database_info;IF
(var_history_age_limit is not null)
THEN set_time := select current_date()+
INTERVAL ' ' $var_history_age_limit day' ';
--begin
transaction delete from history where complete_time <=
set_time;END IF;
return
true;
END;'LANGUAGE 'plpgsql';
I get this error :
Error: ERROR: parser: parse error at or near "select"
NOTICE: Error occurred while executing PL/pgSQL function
del_old_history
NOTICE: line 8 at assignment
2. Is there any equiavlent of MS -SQLServer
'trancount ' in postgres ?
3. if object_id ('database_info') is null
how is above
statement verified in postgres . I tried looking for OID .
Any help will be highly
appreciated.
Regards,
-Sugandha
Re: [SQL] Few Queries
Am Mittwoch, 14. August 2002 07:05 schrieb Sugandha Shah:
> 1. I 'm firing a query and it returns the value in variable which I
> need to pass to other query . Is this a right way to pass the
> value ? I'm newbie to this Database and hence facing lot of
> syntax problems.
>
> CREATE FUNCTION del_old_history() RETURNS bool AS '
> declare
>var_history_age_limit int4;
>set_timedatetime;
> BEGIN
> select into var_history_age_limit history_age_limit from
> database_info; IF (var_history_age_limit is not null) THEN
> set_time := select current_date()+ INTERVAL ' '
If you do a aselect you need select into. Normal assignment is only
possible with simple expression. Try:
SELECT INTO set_time current_date()+ INTERVAL ' ';
> 2. Is there any equiavlent of MS -SQLServer 'trancount ' in
> postgres ?
you should only post one question per mail and i dont know waht
trancount is.
do you mean something like getting the affected rows?
Look at this file in the postgresdocs (7.2.1)
plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS
> 3. if object_id ('database_info') is null
>how is above statement verified in postgres . I tried looking
> for OID .
same as answer to question number 2.
something like
GET DIAGNOSTICS var_oid = RESULT_OID;
IF var_oid IS NULL THEN
janning
--
PLANWERK 6 /websolutions
Herzogstraße 86
40215 Düsseldorf
fon 0211-6015919
fax 0211-6015917
http://www.planwerk6.de
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Need Help for select
On Mon, 12 Aug 2002 08:11:48 -0700 (PDT) "Ludwig Lim" <[EMAIL PROTECTED]> wrote: Hi, thank you for your quick response, but the answer you gave me doesnt give the result i want. Let me try to explain what i want. Lets say that table a contains informations about some items of the type foo. Table b holds information about what item bar is selected by foo. In short: each item foo can have 0..n items bar selected. Thats the left side. The ride side as follows. Table d contains information about subitems. Table c holds information about subitems and items of type bar. Each subitem can have 0..n items bar selected. What i want is that a subitem is only activated for a foo item if the foo-item has exactly selected the same bar items selected as as the relation between table c and d. Example 1: The foo-item A_Name1 has selected the bar-items 1 and 2. The subitem D_Name1 is only activated for a foo-item if that foo-item has selected the bar-items 1 and 2, this happens for A_Name1. Example 2: The foo-item A_Name4 has selected the bar-item 5. The subitem D_Name3 is only activated for a foo-item if that foo-item has selected the bar-item 5, this happens for A_Name4. Hope these informations describe my problema little bit better. I have played with some plpgsql-functions but found no way. The problem is the 0..n relation between a+b and c+d. Regards andre > > --- Andre Schubert <[EMAIL PROTECTED]> wrote: > > Hi all, > > > > i need help to build a select query or > > plpgsql-fucntion > > for the following tables. > >> Is it possible to build a select query that selects > > d.name for each a.name where > > a.id = b.a_id and d.id = c.d_id and each b.c_id must > > exist in c.b_id. > > > > Example: > > a:b: c : d: > > id | name a_id | c_idb_id | d_idid | > > name > > |--- ---|- ---|- > > -| > > 1 | A_Name11 | 1 1 | 1 1 | > > D_Name1 > > 2 | A_Name21 | 2 2 | 1 2 | > > D_Name2 > > 3 | A_Name32 | 1 3 | 2 3 | > > D_Name3 > > 4 | A_Name43 | 3 4 | 2 > > 3 | 4 5 | 3 > > 4 | 5 > > > > i wish to have to following result: > > | > > A_Name1 | D_Name1 > > A_Name3 | D_Name2 > > A_Name4 | D_Name3 > > > > I hope someone could understand the problem > > You can use views to to simplify complicated queries > > Create a view that will join table A & B > > Create view view_ab(name,id) as > select name,c_id > from a,b > where id = c_id; > > Create a view that will join table C & D > > Create view view_cd(name2,id2) as > select name,b_id > from c,d > where id=d_id; > > Create a query that will join the views "view_ab" and > "view_cd" > > Select name,name2 > from view_ab,view_cd > where id=id2; > > > > __ > Do You Yahoo!? > HotJobs - Search Thousands of New Jobs > http://www.hotjobs.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] tsearch vs. fulltextindex
Hi, I've just done some performance comparisons between contrib/fulltextindex and contrib/tsearch. Even with every optimisation I can think of for fulltextindex, tsearch is 300 times faster ;) Plus it doesn't require a separate table or complicated queries. I think we should strongly encourage people to use tsearch instead of fulltextindex. I hope to commit some change to fulltextindex in the near future, so I'll add a note to the readme then. Chris eg: australia=# explain analyse select food_id, category_id, description from test_foods where not pending and fulltextidx ## 'baskin&fruit'; NOTICE: QUERY PLAN: Index Scan using fulltextidx_idx on test_foods (cost=0.00..45.93 rows=11 width=40) (actual time=0.22..1.53 rows=8 loops=1) Total runtime: 1.70 msec EXPLAIN australia=# explain analyze SELECT distinct(f.food_id), f.category_id, f.description, f.brand FROM food_foods f, food_foods_fti f0, food_foods_fti f1 WHERE NOT f.pending AND f0.id=f.oid AND f0.string ~ '^baskin' AND f1.id=f.oid AND f1.string ~ '^fruit'; NOTICE: QUERY PLAN: Unique (cost=12.10..12.11 rows=1 width=66) (actual time=532.11..532.25 rows=8 loops=1) -> Sort (cost=12.10..12.10 rows=1 width=66) (actual time=532.10..532.14 rows=8 loops=1) -> Nested Loop (cost=0.00..12.09 rows=1 width=66) (actual time=292.41..531.89 rows=8 loops=1) -> Nested Loop (cost=0.00..6.07 rows=1 width=8) (actual time=292.35..531.35 rows=8 loops=1) -> Index Scan using food_foods_fti_string_idx on food_foods_fti f0 (cost=0.00..3.03 rows=1 width=4) (actual time=0.07..0.45 rows=23 loops=1) -> Index Scan using food_foods_fti_string_idx on food_foods_fti f1 (cost=0.00..3.03 rows=1 width=4) (actual time=0.04..16.52 rows=1092 loops=23) -> Index Scan using food_foods_oid_idx on food_foods f (cost=0.00..6.01 rows=1 width=58) (actual time=0.03..0.04 rows=1 loops=8) Total runtime: 532.49 msec EXPLAIN ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [HACKERS] tsearch vs. fulltextindex
Good point. Some said fulltextindex was better for certain queries, but if no one can come up with such a case, we can remove it. --- Christopher Kings-Lynne wrote: > Hi, > > I've just done some performance comparisons between contrib/fulltextindex > and contrib/tsearch. Even with every optimisation I can think of for > fulltextindex, tsearch is 300 times faster ;) > > Plus it doesn't require a separate table or complicated queries. > > I think we should strongly encourage people to use tsearch instead of > fulltextindex. I hope to commit some change to fulltextindex in the near > future, so I'll add a note to the readme then. > > Chris > > eg: > > australia=# explain analyse select food_id, category_id, description from > test_foods where not pending and fulltextidx ## 'baskin&fruit'; > NOTICE: QUERY PLAN: > > Index Scan using fulltextidx_idx on test_foods (cost=0.00..45.93 rows=11 > width=40) (actual time=0.22..1.53 rows=8 loops=1) > Total runtime: 1.70 msec > > EXPLAIN > australia=# explain analyze SELECT distinct(f.food_id), f.category_id, > f.description, f.brand FROM food_foods f, food_foods_fti f0, food_foods_fti > f1 WHERE NOT f.pending AND f0.id=f.oid AND f0.string ~ '^baskin' AND > f1.id=f.oid AND f1.string ~ '^fruit'; > NOTICE: QUERY PLAN: > > Unique (cost=12.10..12.11 rows=1 width=66) (actual time=532.11..532.25 > rows=8 loops=1) > -> Sort (cost=12.10..12.10 rows=1 width=66) (actual time=532.10..532.14 > rows=8 loops=1) > -> Nested Loop (cost=0.00..12.09 rows=1 width=66) (actual > time=292.41..531.89 rows=8 loops=1) > -> Nested Loop (cost=0.00..6.07 rows=1 width=8) (actual > time=292.35..531.35 rows=8 loops=1) > -> Index Scan using food_foods_fti_string_idx on > food_foods_fti f0 (cost=0.00..3.03 rows=1 width=4) (actual time=0.07..0.45 > rows=23 loops=1) > -> Index Scan using food_foods_fti_string_idx on > food_foods_fti f1 (cost=0.00..3.03 rows=1 width=4) (actual time=0.04..16.52 > rows=1092 loops=23) > -> Index Scan using food_foods_oid_idx on food_foods f > (cost=0.00..6.01 rows=1 width=58) (actual time=0.03..0.04 rows=1 loops=8) > Total runtime: 532.49 msec > > EXPLAIN > > > ---(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 > -- 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 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] [HACKERS] tsearch vs. fulltextindex
Well, I think it shouldn't disappear for a few releases yet... Chris > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Bruce Momjian > Sent: Wednesday, 14 August 2002 12:43 PM > To: Christopher Kings-Lynne > Cc: Hackers; [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: Re: [HACKERS] tsearch vs. fulltextindex > > > > Good point. Some said fulltextindex was better for certain queries, but > if no one can come up with such a case, we can remove it. > > -- > - > > Christopher Kings-Lynne wrote: > > Hi, > > > > I've just done some performance comparisons between > contrib/fulltextindex > > and contrib/tsearch. Even with every optimisation I can think of for > > fulltextindex, tsearch is 300 times faster ;) > > > > Plus it doesn't require a separate table or complicated queries. > > > > I think we should strongly encourage people to use tsearch instead of > > fulltextindex. I hope to commit some change to fulltextindex > in the near > > future, so I'll add a note to the readme then. > > > > Chris > > > > eg: > > > > australia=# explain analyse select food_id, category_id, > description from > > test_foods where not pending and fulltextidx ## 'baskin&fruit'; > > NOTICE: QUERY PLAN: > > > > Index Scan using fulltextidx_idx on test_foods > (cost=0.00..45.93 rows=11 > > width=40) (actual time=0.22..1.53 rows=8 loops=1) > > Total runtime: 1.70 msec > > > > EXPLAIN > > australia=# explain analyze SELECT distinct(f.food_id), f.category_id, > > f.description, f.brand FROM food_foods f, food_foods_fti f0, > food_foods_fti > > f1 WHERE NOT f.pending AND f0.id=f.oid AND f0.string ~ '^baskin' AND > > f1.id=f.oid AND f1.string ~ '^fruit'; > > NOTICE: QUERY PLAN: > > > > Unique (cost=12.10..12.11 rows=1 width=66) (actual time=532.11..532.25 > > rows=8 loops=1) > > -> Sort (cost=12.10..12.10 rows=1 width=66) (actual > time=532.10..532.14 > > rows=8 loops=1) > > -> Nested Loop (cost=0.00..12.09 rows=1 width=66) (actual > > time=292.41..531.89 rows=8 loops=1) > > -> Nested Loop (cost=0.00..6.07 rows=1 width=8) (actual > > time=292.35..531.35 rows=8 loops=1) > > -> Index Scan using food_foods_fti_string_idx on > > food_foods_fti f0 (cost=0.00..3.03 rows=1 width=4) (actual > time=0.07..0.45 > > rows=23 loops=1) > > -> Index Scan using food_foods_fti_string_idx on > > food_foods_fti f1 (cost=0.00..3.03 rows=1 width=4) (actual > time=0.04..16.52 > > rows=1092 loops=23) > > -> Index Scan using food_foods_oid_idx on food_foods f > > (cost=0.00..6.01 rows=1 width=58) (actual time=0.03..0.04 > rows=1 loops=8) > > Total runtime: 532.49 msec > > > > EXPLAIN > > > > > > ---(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 > > > > -- > 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 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
