Re: [SQL] function replace doesnt exist
Are you looking for this ?
available on
http://www.brasileiro.net:8080/postgres/cookbook/view-one-recipe.adp?recipe_id=23
regds
mallah.
View One Recipe
Home -> Postgres -> CookBook Home -> View One Recipe Submitted on: 03-16-2001
Description:
mimic oracle's replace function. versions in pltcl and plpgsql.
Code:
-- by Jonathan Ellis ([EMAIL PROTECTED])
-- licensed under the GPL
-- emailing me improvements is appreciated but not required
-- args: string substring replacement_substring
create function replace (varchar, varchar, varchar) returns varchar as '
-- escape out characters that regsub would treat as special
regsub -all {&} "$3" {\\\&} 3
regsub -all {\\[0-9]} "$3" {\\\0} 3
eval "regsub -all \{$2\} \{$1\} \{$3\} rval"
return $rval
' language 'pltcl';
-- plpgsql version so we don't have to jump through hoops to call it from other
functions
create function replace (varchar, varchar, varchar) returns varchar as '
declare
string alias for $1;
sub alias for $2;
replacement alias for $3;
-- xxx[MATCH]
-- | end_before
-- | start_after
match integer;
end_before integer;
start_after integer;
string_replaced varchar;
string_remainder varchar;
begin
string_remainder := string;
string_replaced := ;
match := position(sub in string_remainder);
while match > 0 loop
end_before := match - 1;
start_after := match + length(sub);
string_replaced := string_replaced || substr(string_remainder, 1, end_b
efore) || replacement;
string_remainder := substr(string_remainder, start_after);
match := position(sub in string_remainder);
end loop;
string_replaced := string_replaced || string_remainder;
return string_replaced;
end;
' LANGUAGE 'plpgsql';
On Thursday 12 December 2002 10:04 pm, Andy Morrow wrote:
> Hi
>
> im trying to execute an update command on a postgresql DB table using
> pgAdmin II
>
> im using the following statement
>
> UPDATE commandlist SET command = REPLACE (command,'A','B')
>
>
> commandlist is the table name
> command is the column
> and i want to change the value A to B
>
>
> but it's giving me the following error message
>
>
> an error has occured in pgAdmin II:frmSQLInput.cmdExecute_Click:
>
> Number: -2147467259
> Description: Error while executing the query;
> ERROR: Function'replace(varchar, unknown, unknown)' does not exist
> Unable to identify a function that satisfies the given argument types
> You may need to add explicit typecasts
>
>
> ---(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
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Stored Procedure Problem
In 7.3 you can , in follwoing steps, 1. do a CREATE TYPE (i would recommend to use a sperate schema for storing user defined types) 2. in plpgsql declare the RECORD of that type . 3. populate the record varible according to your business logic and return the RECORD using RETURN statements. hope it will help , if not please revert back. regds mallah. On Thursday 12 December 2002 08:21 pm, Tomasz Myrta wrote: > Atul wrote: > > CREATE FUNCTION b_function() RETURNS varchar AS ' > > > > DECLARE > > > > an_integer int4; > > > > an_namevarchar; > > > > BEGIN > > > > select into an_integer emp_id,an_name emp_name from employee; > > > > return an_integer,an_name; > > > > END; > > > > ' > > First: select into an_integer,an_name emp_id,emp_name... > Second: you can't return 2 variables from plpgsql function. > > Tomasz Myrta > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] order by and limit with multi-column index, bug?
as a workaround for min()/max() on indexed columns forcing an index-scan I tried "order by" with "limit 1". Works fine for the first record but fails for the last. I don't know why. Here's the setup: A table "journal" containing several million records with an index "CREATE INDEX journal_kblattsoll ON journal (sollkontoid,waehrungid,periode,belegdatum,journalnr)" with periode being an INT, belegdatum DATE, the rest is INT8. As a replacement for "select min(periode) from journal where sollkontoid=266122::int8 and waehrungid=17::int8" which for some reason in the design of postgres scans the whole index, I tried: "select sollkontoid,waehrungid,periode,belegdatum,journalnr from journal where sollkontoid=266122::int8 and waehrungid=17::int8 order by sollkontoid,waehrungid,periode,belegdatum,journalnr asc limit 1;" this yields: sollkontoid | waehrungid | periode | belegdatum | journalnr -++-++--- 266122 | 17 | 0 | 2002-01-01 | 411 which is correct and works in a fraction of a second as expected. now, doing the same with "desc" instead of "asc" should return "periode = 12" (see below) for the last record, but it doesn't! After a fairly long time I get: sollkontoid | waehrungid | periode | belegdatum | journalnr -++-++--- 266122 | 17 | 0 | 2002-01-01 | 2783 ooops???! periode = 0??? Query plan: Limit (cost=491999.72..491999.73 rows=1 width=32) -> Sort (cost=491999.72..492309.30 rows=123828 width=32) Sort Key: sollkontoid, waehrungid, periode, belegdatum, journalnr -> Index Scan using journal_kblattsoll on journal (cost=0.00..481525.10 rows=123828 width=32) Index Cond: ((sollkontoid = 266122::bigint) AND (waehrungid = 17::bigint)) Surprisingly enough, reducing the fields in the order-by clause returns the correct value for "periode": select sollkontoid,waehrungid,periode,belegdatum,journalnr from journal where sollkontoid=266122::int8 and waehrungid=17::int8 order by sollkontoid,waehrungid,periode desc limit 1; sollkontoid | waehrungid | periode | belegdatum | journalnr -++-++--- 266122 | 17 | 12 | 2002-12-09 |303609 min/max-checks: select max(periode)from journal where sollkontoid=266122::int8 and waehrungid=17::int8; returns 12. select max(belegdatum) from journal where sollkontoid=266122::int8 and waehrungid=17::int8 and periode=12; returns "2002-12-10" select max(journalnr) from journal where sollkontoid=266122::int8 and waehrungid=17::int8 and periode=12 and belegdatum='2002-12-10'; returns 305098. Consequently, the last record according to the order by clause should be: sollkontoid | waehrungid | periode | belegdatum | journalnr -++-++--- 266122 | 17 | 12 | 2002-12-10 |305098 questions: - what's this??? - why does it take that long? - is "ORDER BY ... DESC" broken? (even after dropping the index I get the same results) - am I missing something (friday, 13th, ...) ? thanx for any answer, harald. (postgres 7.3 on redhat 8.0) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] order by and limit with multi-column index, bug?
hi, could it be that "order by col1, col2 desc" is different from "order by col1 desc, col2 desc" ? these are different and it's correct. i'm not sure if this is your problem since i haven't digged into it... sorry... but check it to be sure... hth, kuba On Fri, 13 Dec 2002, Harald Krake wrote: > as a workaround for min()/max() on indexed columns forcing an index-scan > I tried "order by" with "limit 1". Works fine for the first record > but fails for the last. I don't know why. > > Here's the setup: > > A table "journal" containing several million records with an index > > "CREATE INDEX journal_kblattsoll ON journal > (sollkontoid,waehrungid,periode,belegdatum,journalnr)" > > with periode being an INT, belegdatum DATE, the rest is INT8. > > As a replacement for > > "select min(periode) from journal > where sollkontoid=266122::int8 and waehrungid=17::int8" > > which for some reason in the design of postgres scans the whole index, > I tried: > > "select sollkontoid,waehrungid,periode,belegdatum,journalnr from journal > where sollkontoid=266122::int8 and waehrungid=17::int8 > order by sollkontoid,waehrungid,periode,belegdatum,journalnr asc limit 1;" > > this yields: > sollkontoid | waehrungid | periode | belegdatum | journalnr > -++-++--- > 266122 | 17 | 0 | 2002-01-01 | 411 > > which is correct and works in a fraction of a second as expected. > > now, doing the same with "desc" instead of "asc" should return > "periode = 12" (see below) for the last record, but it doesn't! > > After a fairly long time I get: > > sollkontoid | waehrungid | periode | belegdatum | journalnr > -++-++--- > 266122 | 17 | 0 | 2002-01-01 | 2783 > > ooops???! periode = 0??? > > Query plan: > Limit (cost=491999.72..491999.73 rows=1 width=32) >-> Sort (cost=491999.72..492309.30 rows=123828 width=32) > Sort Key: sollkontoid, waehrungid, periode, belegdatum, journalnr > -> Index Scan using journal_kblattsoll on journal > (cost=0.00..481525.10 rows=123828 width=32) >Index Cond: ((sollkontoid = 266122::bigint) AND (waehrungid = > 17::bigint)) > > > Surprisingly enough, reducing the fields in the order-by clause > returns the correct value for "periode": > > select sollkontoid,waehrungid,periode,belegdatum,journalnr from journal > where sollkontoid=266122::int8 and waehrungid=17::int8 > order by sollkontoid,waehrungid,periode desc limit 1; > > sollkontoid | waehrungid | periode | belegdatum | journalnr > -++-++--- > 266122 | 17 | 12 | 2002-12-09 |303609 > > > > min/max-checks: > > select max(periode)from journal where sollkontoid=266122::int8 and > waehrungid=17::int8; > > returns 12. > > select max(belegdatum) from journal where sollkontoid=266122::int8 and > waehrungid=17::int8 and periode=12; > > returns "2002-12-10" > > select max(journalnr) from journal where sollkontoid=266122::int8 and > waehrungid=17::int8 and periode=12 and belegdatum='2002-12-10'; > > returns 305098. > > Consequently, the last record according to the order by clause should be: > > sollkontoid | waehrungid | periode | belegdatum | journalnr > -++-++--- > 266122 | 17 | 12 | 2002-12-10 |305098 > > > > > questions: > > - what's this??? > - why does it take that long? > - is "ORDER BY ... DESC" broken? (even after dropping the index I get > the same results) > - am I missing something (friday, 13th, ...) ? > > thanx for any answer, > harald. > > (postgres 7.3 on redhat 8.0) > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] order by and limit with multi-column index, bug?
On Friday 13 December 2002 03:47 pm, Jakub Ouhrabka wrote: > hi, > > could it be that "order by col1, col2 desc" is different from "order by > col1 desc, col2 desc" ? these are different and it's correct. > > i'm not sure if this is your problem since i haven't digged into it... > sorry... but check it to be sure... > > hth, > > kuba bingo! good morning thanx a lot! ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] UPDATE comparing dates and non-dates data
Hi; I've got date data, extracted from rain gauge stations. The date of a row in my data are structured in three integer fields, and as a result of a query I can get the following (what is quite good for my): cod_station | year | month | day | rain -+--+---+-+-- 7250| 1933 | 8 | 1 |45 7250| 1933 | 8 | 2 |3 7250| 1933 | 8 | 3 |0 ... and this for several rain station for about forty years. My problem is that I need to find missing data (a missing data here is a non-existent row). I think I could prepare a table with a complete series of date field, and ALTER it to ADD aditional fields for every station I need to add: date | stat_7250 | stat_7237 ... ++---+--... 1/11/1999 2/12/1999 ... , and make an UPDATE that in someway, will compare the "date" of this table with an extracted date from "year", "day" and "rain" from the aforementioned SELECT, leaving the non-coincident dates in blank and filling the rain data of the coincident dates. Is this possible? How? Please help. Thank you for any possible advice -- A. Javier García Water and Soil Conservation Department CEBAS-CSIC Apartado 4195 30080 Murcia Spain Tel.: +34 968 39 63 90 Fax: +34 968 39 62 13 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] A PL/PgSQL Question
Hi: I have the following PL/PgSQL code blocks: a) IF (condition) THEN RAISE EXCEPTION ''Cannot Insert''; END IF; b) IF (condition) THEN RAISE NOTICE ''Cannot Insert''; RETURN NULL; END IF; What is the difference between the first and second chunk PL/PgSQL INSERT/UPDATE trigger function code aside from the fact that first scenario will result in an "ABORT" state? Are there any instances where a NOTICE and a RETURN NULL statement is a much better than a RAISE EXCEPTION statement? Thank you very much, ludwig lim __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] A Costly function + LIMIT
PostgreSQL 7.2.3 I have a function that is quite costly to run on 1000's of records... Let's call it "widget". I have a query (SELECT name, address, city, state, zip, widget(name, address, city, state, zip) FROM eastern_usa ORDER BY state, city, zip, name LIMIT 5000) UNION ALL (SELECT name, address, city, state, zip, widget(name, address, city, state, zip) FROM western_usa ORDER BY state, city, zip, name LIMIT 5000) ORDER BY 4, 3, 5, 1 LIMIT 100 OFFSET 0; It runs "widget" on 1 records. The damage would be negligible if it could run on the 100... Any ideas? CG __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com ---(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] A Costly function + LIMIT
Chris Gamache <[EMAIL PROTECTED]> writes: > It runs "widget" on 1 records. The damage would be negligible if it could > run on the 100... So do the function calculation outside the UNION/ORDER BY. SELECT name, address, city, state, zip, widget(name, address, city, state, zip) FROM ((SELECT * FROM eastern_usa ORDER BY state, city, zip, name LIMIT 5000 UNION ALL SELECT * FROM western_usa ORDER BY state, city, zip, name LIMIT 5000) ORDER BY 4, 3, 5, 1 LIMIT 100 OFFSET 0) ss; You might have to fool with the parenthesization a little to get it to parse ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] A PL/PgSQL Question
Ludwig, >What is the difference between the first and second > chunk PL/PgSQL INSERT/UPDATE trigger function code > aside from the fact that first scenario will result in > an "ABORT" state? > > Are there any instances where a NOTICE and a RETURN > NULL statement is a much better than a RAISE EXCEPTION > statement? Not that I have encountered, but YMMV. -- -Josh Berkus Aglio Database Solutions San Francisco ---(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 comparing dates and non-dates data
On Friday 13 December 2002 09:43, javier garcia wrote: > Hi; > I've got date data, extracted from rain gauge stations. The date of a row in > my data are structured in three integer fields, and as a result of a query I > can get the following (what is quite good for my): > cod_station | year | month | day | rain > -+--+---+-+-- > 7250| 1933 | 8 | 1 |45 > 7250| 1933 | 8 | 2 |3 > 7250| 1933 | 8 | 3 |0 > ... > and this for several rain station for about forty years. My problem is that I > need to find missing data (a missing data here is a non-existent row). > > I think I could prepare a table with a complete series of date field, and > ALTER it to ADD aditional fields for every station I need to add: > > date | stat_7250 | stat_7237 ... > ++---+--... > 1/11/1999 > 2/12/1999 > ... > , and make an UPDATE that in someway, will compare the "date" of this table > with an extracted date from "year", "day" and "rain" from the aforementioned > SELECT, leaving the non-coincident dates in blank and filling the rain data > of the coincident dates. Is this possible? How? I don't think this is the best method to find missing dates. However, I'm a little confused as to what you consider a "missing" date. Could you be more explicit? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] UPDATE comparing dates and non-dates data
On Fri, 2002-12-13 at 11:43, javier garcia wrote: > Hi; > I've got date data, extracted from rain gauge stations. The date of a row in > my data are structured in three integer fields, and as a result of a query I > can get the following (what is quite good for my): > cod_station | year | month | day | rain > -+--+---+-+-- > 7250| 1933 | 8 | 1 |45 > 7250| 1933 | 8 | 2 |3 > 7250| 1933 | 8 | 3 |0 > ... > and this for several rain station for about forty years. My problem is that I > need to find missing data (a missing data here is a non-existent row). > > I think I could prepare a table with a complete series of date field, and > ALTER it to ADD aditional fields for every station I need to add: > > date | stat_7250 | stat_7237 ... > ++---+--... > 1/11/1999 > 2/12/1999 > ... > , and make an UPDATE that in someway, will compare the "date" of this table > with an extracted date from "year", "day" and "rain" from the aforementioned > SELECT, leaving the non-coincident dates in blank and filling the rain data > of the coincident dates. Is this possible? How? > > Please help. > Thank you for any possible advice > seems like a big job. not knowing how many rain guages there are (I am assuming they are mainly in the plain) this is probably a fair amount of data, roughly 25,000 data points per gauge? I think I would approach this differently based on what I knew about the data but it may change after I think about it for a minute or two. I am sort of interested in this issue since I am right now scheming on the most effcient way to sum integer counts of events in seperate rows by 3 columns and where dates are contiguous. Anyway, are there tens, hundreds or thousands of guages? do most guages have some missing data? Is diskspace an issue for duplicating the data in an easier to use fashion? you could approach it as you were talking about but I think I would first try to identify the missing data. that seems pretty straight forward to code in the language of you choice I would probably do it in perl but that is just because that is what I am most familier with. buzz the table with a select * order by cod_station,year, month, day initialize the comparison values to the first row and and then for each row compare the station and if it is the same compare the date and see if it is equal to the old date + 1 day if not write it out to a file or inster it into a badentry table as a bad cod_station/date combo you will need to get the dates into a format you can add a day to without having to worry about the month year wrapping stuff if the station is different reinitialize all the variables and keep going if the station is the same and the date check worked then set the vars to the current values and keep on cruising. This can probably be done in PL/psql or what ever the postgres language is but I nave not gotten that far yet. then you can do allsorts of stuff to the table based on the dates that are found bad. if all stations are supposed to current then an additional check would have to be made to see if the last date for a station is the current date and create entries in the bad file/table for each of the missing days. Bret What I have been dreaming of is a select max(date) min(date) where dates are contiguous and blah blah. I can't seem to find it in the docs though :) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] server terminated by a query in 7.3
Tom, I've a perl script, which has been used for a long time, it works well, however, after I upgrade my postgresql from 7.2 to 7.3, one query always makes server terminated, could you give me a solution for it? Thanks. Jie Liang SELECT urlinfo.id,url,iprism_map,iprism_map_sg,level,domid, CASE WHEN ratedon > '2002-11-24' AND ratedby NOT LIKE '%jurl' THEN 1 ELSE 0 END as trunc INTO TEMP filter96512 FROM urlinfo,ratings_by_serial,cid_code96512 WHERE pidwsr<=0 AND urlinfo.id=ratings_by_serial.id AND ratings_by_serial.cid=cid_code96512.cid; CREATE index filter_temp_id ON filter96512(id); VACUUM ANALYZE filter96512; SELECT id,url,9 as iprism_map,max(level) as level,domid,trunc INTO TEMP filter0 FROM filter96512 WHERE iprism_map!~'^[14]' GROUP BY id,url,domid,trunc; SELECT distinct on(id) id,url,1 as iprism_map,99 as level,domid,trunc INTO TEMP filter_0 FROM filter96512 WHERE iprism_map='1'; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. FROM LOG: Dec 13 09:21:38 beijing postgres[699]: [30-1] LOG: query: SELECT distinct on(id) id,url,1 as iprism_map,99 as level,domid,trunc INTO TEMP filter_0 FROM filter96512 Dec 13 09:21:38 beijing postgres[699]: [30-2] WHERE iprism_map='1' Dec 13 09:22:34 beijing postgres[141]: [9] LOG: server process (pid 699) was terminated by signal 11 Dec 13 09:22:34 beijing postgres[141]: [10] LOG: terminating any other active server processes Dec 13 09:22:34 beijing postgres[141]: [11] LOG: all server processes terminated; reinitializing shared memory and semaphores Dec 13 09:22:34 beijing postgres[1844]: [12] LOG: connection received: host=[local] Dec 13 09:22:34 beijing postgres[1844]: [13] FATAL: The database system is starting up Dec 13 09:22:35 beijing postgres[1843]: [12] LOG: database system was interrupted at 2002-12-13 09:08:04 PST Dec 13 09:22:35 beijing postgres[1843]: [13] LOG: checkpoint record is at 1/D654B54 Dec 13 09:22:35 beijing postgres[1843]: [14] LOG: redo record is at 1/D654B54; undo record is at 0/0; shutdown FALSE Dec 13 09:22:35 beijing postgres[1843]: [15] LOG: next transaction id: 5377; next oid: 35529333 Dec 13 09:22:35 beijing postgres[1843]: [16] LOG: database system was not properly shut down; automatic recovery in progress Dec 13 09:22:35 beijing postgres[1843]: [17] LOG: redo starts at 1/D654B94 Dec 13 09:22:35 beijing postgres[1843]: [18] LOG: ReadRecord: record with zero length at 1/D6819E8 Dec 13 09:22:35 beijing postgres[1843]: [19] LOG: redo done at 1/D6819A4 Dec 13 09:22:38 beijing postgres[1843]: [20] LOG: database system is ready ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] server terminated by a query in 7.3
Jie Liang <[EMAIL PROTECTED]> writes: > however, after > I upgrade my postgresql from 7.2 to 7.3, one query always makes server > terminated, could > you give me a solution for it? I cannot reproduce this with the information you gave. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] server terminated by a query in 7.3
Maybe you did a patch in localbuff.c to fix that vacuuming temp table? Jie liang -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: Friday, December 13, 2002 10:12 AM To: Jie Liang Cc: [EMAIL PROTECTED]; '[EMAIL PROTECTED]' Subject: Re: server terminated by a query in 7.3 Jie Liang <[EMAIL PROTECTED]> writes: > however, after > I upgrade my postgresql from 7.2 to 7.3, one query always makes server > terminated, could > you give me a solution for it? I cannot reproduce this with the information you gave. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
