[GENERAL] pgfoundry
I was wondering if anyone new how to get this package to install for 8.2.3? When I try to install it I get: ERROR: incompatible library /usr/lib/pgsql/uri.so: missing magic block HINT: Extension libraries are required to use the PG_MODULE_MAGIC macro. I read 8.2 now needs the MAGIC definition but C is rather foreign. Any help? http://www.postgresql.org/ftp/projects/pgFoundry/uri/ It hasn't been updated since 2005. Thanks - Looking for earth-friendly autos? Browse Top Cars by Green Rating at Yahoo! Autos' Green Center.
Re: [GENERAL] pgfoundry
Chad Wagner [EMAIL PROTECTED] wrote: On 2/24/07, Matthew Peter [EMAIL PROTECTED] wrote: I was wondering if anyone new how to get this package to install for 8.2.3? When I try to install it I get: ERROR: incompatible library /usr/lib/pgsql/uri.so: missing magic block HINT: Extension libraries are required to use the PG_MODULE_MAGIC macro. I read 8.2 now needs the MAGIC definition but C is rather foreign. Any help? http://www.postgresql.org/ftp/projects/pgFoundry/uri/ It hasn't been updated since 2005. Thanks This appears to be a trivial patch against the uri codebase: diff -wur uri.orig/uri.c uri/uri.c --- uri.orig/uri.c 2005-07-20 18:49:23.0 -0400 +++ uri/uri.c 2007-02-24 07:16:29.553130168 -0500 @@ -15,6 +15,10 @@ #include liburi/uri.h +#ifdef PG_MODULE_MAGIC +PG_MODULE_MAGIC; +#endif + /* * Commonly used code snippets */ Other than that it appears to still work with 8.2.3. -- Chad http://www.postgresqlforums.com/ That works. I tried it last night with the same patch from the 8.2 changefile. I must not have cleaned out the previous uri.so library. Thanks. - Get your own web address. Have a HUGE year through Yahoo! Small Business.
Re: [GENERAL] drive failre, corrupt data...
Wow. I just noticed I have the same problem today after a vacuum. As well as an degraded array. Musta been a time release Y2k7 bug. Hopefully didn't loose anything too important. Now that's room service! Choose from over 150,000 hotels in 45,000 destinations on Yahoo! Travel to find your fit. http://farechase.yahoo.com/promo-generic-14795097 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] named cache
--- Willy-Bas Loos [EMAIL PROTECTED] wrote: maybe you would find materialized views interesting. http://www.google.com/search?q=materialized+view+postgresql On 12/1/06, Matthew Peter [EMAIL PROTECTED] wrote: Is it possible to put an query result into memory? Like SELECT * from table WHERE [...] CACHE cache_name TIMEOUT '1 hour'::interval; So if cache_name exists with the same SQL statement, the result would be fetched from the cache, refreshing and updating the cache with fresh results when it expires? Reducing disk reads, query times, etc. That is basically the idea but talk about a headache. Too many functions and triggers to handle a single view none the less. Rather, why not write an function to use SELECT INTO and put the new tables in a schema named cache. Drop and recreate the schema cached tables of the views and wallah. Making this process cleanly abstracted into the background with 4 additional words would be a beautiful thing. eg, SELECT * from table WHERE [...] CACHE cache_name TIMEOUT interval; Since it's a cache, it doesn't need to be updated until the TIMEOUT expires and permissions can be inherited by the VIEW that creates it, etc. Or if that is that an SQL-spec no-no? Maybe... CREATE CACHED VIEW on view_name as view_name_cache TIMEOUT interval; Oh ya. Like CREATE UNIQUE INDEX. Now querying from view_name_cache will not effect querying from the original view view_name for fresh data! Internally implemented the cached views could be put in a schema like pg_cache, in RAM, etc. Doesn't really matter. Would just be nice to have something seamless, clean, upgrade agnostic, and easy! Thoughts? Do you Yahoo!? Everyone is raving about the all-new Yahoo! Mail beta. http://new.mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] named cache
--- Shane Ambler [EMAIL PROTECTED] wrote: Matthew Peter wrote: --- Willy-Bas Loos [EMAIL PROTECTED] wrote: maybe you would find materialized views interesting. http://www.google.com/search?q=materialized+view+postgresql On 12/1/06, Matthew Peter [EMAIL PROTECTED] wrote: Is it possible to put an query result into memory? Like SELECT * from table WHERE [...] CACHE cache_name TIMEOUT '1 hour'::interval; So if cache_name exists with the same SQL statement, the result would be fetched from the cache, refreshing and updating the cache with fresh results when it expires? Reducing disk reads, query times, etc. That is basically the idea but talk about a headache. Too many functions and triggers to handle a single view none the less. Rather, why not write an function to use SELECT INTO and put the new tables in a schema named cache. Drop and recreate the schema cached tables of the views and wallah. Making this process cleanly abstracted into the background with 4 additional words would be a beautiful thing. eg, SELECT * from table WHERE [...] CACHE cache_name TIMEOUT interval; Since it's a cache, it doesn't need to be updated until the TIMEOUT expires and permissions can be inherited by the VIEW that creates it, etc. Or if that is that an SQL-spec no-no? Maybe... CREATE CACHED VIEW on view_name as view_name_cache TIMEOUT interval; Oh ya. Like CREATE UNIQUE INDEX. Now querying from view_name_cache will not effect querying from the original view view_name for fresh data! Internally implemented the cached views could be put in a schema like pg_cache, in RAM, etc. Doesn't really matter. Would just be nice to have something seamless, clean, upgrade agnostic, and easy! Thoughts? There was a discussion on pgsql-hackers about a month and a half ago that went along these lines. The talk started with the idea of integrating pgmemcached into Postgres. The main result was that the current postgres cache and system cache would give the same results as using forced caching configuration. Yeah. I read throught some of that but didn't know if that was the official conclusion. The overhead of the client connection and sql parsing/planning would negate the benefits of specifying what is cached. One option that was brought up was to create a ram disk and then create a tablespace on that disk with tables to hold what you want to cache. Of course maintaining that between restarts becomes a hassle as well. And if you have enough ram to do that then you have enough for PostgreSQL to cache the data that is used in ram anyway. Wouldn't it work just like plpgsql functions? Where the first call caches the plan or whatever? Using pgmemcached outside of the pg client connection allows you to bypass the sql parsing and planning and get the speed improvements you are looking for but that is handled by the client not the server. Interesting. I really don't have any serious problems with performance actually. Postgresql runs fantastically. I was just curious about RAM caching, I have the typical 80/20 issue where I would like to free up disk IO for other stuff. I haven't read up to much on pgmemcached. Although I did read about memcache from dinga(?) a while back for livejournal. I'd still like it if pg could put a views results into RAM out of the box. Yahoo! Music Unlimited Access over 1 million songs. http://music.yahoo.com/unlimited ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] named cache
Is it possible to put an query result into memory? Like SELECT * from table WHERE [...] CACHE cache_name TIMEOUT '1 hour'::interval; So if cache_name exists with the same SQL statement, the result would be fetched from the cache, refreshing and updating the cache with fresh results when it expires? Reducing disk reads, query times, etc. Cheap talk? Check out Yahoo! Messenger's low PC-to-Phone call rates. http://voice.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] sequence help
I'll jump right in with an example
create sequence foo start with 1;
create view foobar as select *, nextval('foo') from bar;
The problem is I need a nextval()-like method that returns the iterations
without
updating the foo sequence. Therefore, maintaining the sequences original value
by
starting at 1 each time the foobar VIEW is invoked.
This is obviously a simplified example for an larger query dependent on row
order
integrity in a view.
On http://www.postgresql.org/docs/8.1/interactive/functions-sequence.html I did
not
find any way to avoid updating sequences (albeit they are supposed to update by
design and may need to update in order to pull the next in sequence). Nor was I
able
to maintain iteration in a nextval()-like function. Any ideas?
Matt
Cheap talk?
Check out Yahoo! Messenger's low PC-to-Phone call rates.
http://voice.yahoo.com
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] dblink / plpgsql - definition list cheats for record
--- Richard Huxton dev@archonet.com wrote: Matthew Peter wrote: Is there any way to use CREATE TYPE or an VIEW/TABLE defintion list instead of manually defining it? Yes, just define your function as returning that type rather than RECORD. That way the calling context will know what to expect. It's a dblink C function that returns RECORD so I just wanted to create a view or type to help it reference the return data as ( TYPE ) rather than typing a definition list as ( id integer, .. ) each time for each query. Want to start your own business? Learn how on Yahoo! Small Business. http://smallbusiness.yahoo.com/r-index ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] RULE - special variables?
--- Richard Huxton dev@archonet.com wrote: Matthew Peter wrote: Do rules get special variables like triggers? Can I set variables in them like triggers? You get NEW/OLD but they mean something subtly different. Rules act on a query-tree so they are more like placeholders. You can't set variables in triggers. You do so in a function. If your rule calls a function, that could have variables. You can set variables in triggers in the DECLARE block when plpgsql is used. Reading the docs they only show single-statement rules so it never said assigning could be done so here I am. Sometimes it's easier to write a quick rule than a trigger function and separate firing condition, especially when they function so similiarily in theory. Thanks. Cheap talk? Check out Yahoo! Messenger's low PC-to-Phone call rates. http://voice.yahoo.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] dblink / plpgsql - definition list cheats for record returning functions?
Is there any way to use CREATE TYPE or an VIEW/TABLE defintion list instead of manually defining it? Do you Yahoo!? Everyone is raving about the all-new Yahoo! Mail. http://new.mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] RULE - special variables?
Do rules get special variables like triggers? Can I set variables in them like triggers? Do you Yahoo!? Everyone is raving about the all-new Yahoo! Mail. http://new.mail.yahoo.com ---(end of broadcast)--- TIP 1: 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
[GENERAL] TRIGGERS - access sql query_string that called it?
Is it possible to access the entire sql query_string that called the trigger? Want to start your own business? Learn how on Yahoo! Small Business. http://smallbusiness.yahoo.com/r-index ---(end of broadcast)--- TIP 1: 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
[GENERAL] dblink / plpgsql - definition list cheats for record returning functions?
Is there any way to use CREATE TYPE/VIEW/TABLE defintion list instead of manually defining the result types in the calling sql? Thanks __ Sponsored Link Talk more and pay less. Vonage can save you up to $300 a year on your phone bill. Sign up now. http://www.vonage.com/startsavingnow/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] select result / functions from another database in plpgsql
I have DB1 and DB2 pg_databases... I then have a function in DB2 that wants some results from DB1, is it possible to query another db? Like how \! lets you hit the command line... but I need it in plpgsql if possible.Also, I know functions are local to db (unless it inserted into template1 prior to createdb) but is there any other way to call functions from another db? Thanks! Sponsored Link Free Uniden 5.8GHz Phone System with Packet8 Internet Phone Service
Re: [GENERAL] select result / functions from another database in plpgsql
--- Richard Huxton dev@archonet.com wrote: Matthew Peter wrote: I have DB1 and DB2 pg_databases... I then have a function in DB2 that wants some results from DB1, is it possible to query another db? Like how \! lets you hit the command line... but I need it in plpgsql if possible. Look into the db_link or dbi_link packages. These are exactly what you are after. -- Richard Huxton Archonet Ltd Perfect. Leave it to me not to check the contrib package first :) Thanks again Cheap talk? Check out Yahoo! Messenger's low PC-to-Phone call rates. http://voice.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] EXECUTE INSERT BUGS?
create table test (col text); create or replace function tester() RETURNS void AS $$ DECLARE cmd text; v_value text := null; -- ^^^ right here, NULL makes the querystring fail by setting cmd = null BEGIN cmd := 'INSERT INTO test ( col ) values ( ' || quote_literal(v_value) || ');'; EXECUTE cmd; END; $$ LANGUAGE plpgsql; test=# \i /tmp/test CREATE TABLE CREATE FUNCTION test=# select * from tester(); ERROR: cannot EXECUTE a null querystring CONTEXT: PL/pgSQL function tester line 12 at execute statement Also, if v_value is set to boolean then quote_literal(v_value) throws error Cheap talk? Check out Yahoo! Messenger's low PC-to-Phone call rates. http://voice.yahoo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Automatic locale detection?
Is it possible to automatically detect the language encoding of incoming data? For instance if Japanese is used, is there a way to know it is Japanese from a bit in the charset, a dictionary-based evaluation or otherwise? All-new Yahoo! Mail - Fire up a more powerful email and get things done faster.
[GENERAL] increment row number function question
Hello. I need a way to return an iterator result as a column eg,SELECT increment() as ii, some_col from some_tbl order by some_col desc limit 50;ii | some_col+-- 1 | zest 2 | test 3 | nest 4 | fest[...]How can I acheive those results? Thanks for any help. How low will we go? Check out Yahoo! Messengers low PC-to-Phone call rates.
Re: [GENERAL] increment row number function question
Thanks for the reply. <[EMAIL PROTECTED]><[EMAIL PROTECTED]>Even if such a function existed, it would almost certainly not do whatyou want in this query. Per SQL spec, the SELECT target list islogically supposed to be evaluated before the ORDER BY and LIMIT steps, which means you'd get numbers associated with the physical ordering of the rows not their some_col ordering.<[EMAIL PROTECTED]>Shucks. I wanted to return a set with an ordered iterator determined by the ORDER BY. Could that be done?<[EMAIL PROTECTED]><[EMAIL PROTECTED]> regards, tom laneThanks again. Yahoo! Messenger with Voice. Make PC-to-Phone Calls to the US (and 30+ countries) for 2¢/min or less.
Re: [GENERAL] Invoke diff from plpgsql?
George Pavlov [EMAIL PROTECTED] wrote: Wondering how to invoke a application like diff from plpgsql? Thanks!And don't forget that you are working with a database. Most diff-inguses can probably be handled by constructs like EXCEPT and INTERSECT tosay nothing of OUTER JOINs. Also, IS DISTINCT FROM is your friend if youwant a comparison that treats NULLs as "normal" data values.Thanks for the replies. What I wanted to do was just track the changes to a column... like svn does so I can get a history and incremental changes of a article per user handled by a trigger. Saving the output from diff seemed simple enough rather than the entire article. Is there a contrib modules or otherwise to enable tracking changes like that in plpgsql? Feel free to call! Free PC-to-PC calls. Low rates on PC-to-Phone. Get Yahoo! Messenger with Voice
[GENERAL] Invoke diff from plpgsql?
Wondering how to invoke a application like diff from plpgsql? Thanks! Blab-away for as little as 1¢/min. Make PC-to-Phone Calls using Yahoo! Messenger with Voice.
[GENERAL] column data size
wondering where to query for the physical size of a column (returned in bytes) in a row? i looked into the information_schema but i think that's more for getting triggers, views, etc. New Yahoo! Messenger with Voice. Call regular phones from your PC and save big.
Re: [GENERAL] sort a referenced list
I'll help clarify exactly what I am trying to accomplish. What I'm trying to do is create a function that accepts a list then sorts and groups the values (like in sql)... I will have an unique list I can convert it to an array later or leave it a listI'd like to keep it simple and manipulate the argument like...select $1 as list group by list order by list asc;Which doesn't work, but that is the functionality I need if possible. New Yahoo! Messenger with Voice. Call regular phones from your PC for low, low rates.
Re: [GENERAL] sort a referenced list
Tom Lane [EMAIL PROTECTED] wrote: Matthew Peter writes: What I'm trying to do is create a function that accepts a list then sorts and groups the values (like in sql)... I will have an unique list I can convert it to an array later or leave it a listThere is no "list" data structure in SQL. There are tables, and thereare arrays, but it's not especially easy to pass an arbitrary tablevalue to a function. So you almost certainly need to define yourproblem as "create a function that accepts an array then ...". regards, tom lane Originally I wanted to pass in text or varchar array, group it, sort it then do as I willed with it, but I couldn't figure out how. I saw some functions in the contrib for doing these operations, yet only with integer arrays. I could always try converting the array_sort, array_uniq int[] C function to accept text[], but I decided to ask and see if there was a simplier/standard way first since I don't know C all that well.Thanks Blab-away for as little as 1¢/min. Make PC-to-Phone Calls using Yahoo! Messenger with Voice.
[GENERAL] pl/pgsql uniq varchar[] sort?
Hello list. I'm trying to get a unique, sorted varchar array in pl/pgsql. Essentially a "group by" and "order by"'d varchar[]. Anyone got any ideas or point me in the right direction? Thanks. New Yahoo! Messenger with Voice. Call regular phones from your PC and save big.
Re: [GENERAL] plpgsql question
snip WHERE my_tbl_id = $1AND CASE WHEN $2 IS NULL THEN TRUE ELSE $2 = username ENDor WHERE my_tbl_id = $1 AND COALESCE($2 = username, TRUE)or WHERE my_tbl_id = $1 AND COALESCE($2, username) = usernameWith predicates such as these you wouldn't need to use EXECUTE andyou could write the query only once. That did work. Thanks. One other quick question, (figure it still applies to the subject line :) when returning a row from a function I'm trying to include an aggregate, but it's not showing up in the query result and I think it's because it's not included in the RETURN NEXT row;? How do I return it as part of the resultset...create or replace function getrecord(int,text) RETURNS SETOF my_tbl as $$DECLARE row my_tbl%rowtype;BEGINFOR row IN SELECT *, SUBSTR(title,1,25) as short_title FROM my_tblWHERE ...LOOPRETURN NEXT row;END LOOP;RETURN;END;$$ LANGUAGE plpgsql; Thanks Yahoo! Photos Got holiday prints? See all the ways to get quality prints in your hands ASAP.
[GENERAL]
is using IN to query a list of values in an function supported? or is there a work around for this type of query?e.g. ... SELECT * FROM tbl WHERE u_id IN (0$1) $$ LANGUAGE SQL; Yahoo! Photos Got holiday prints? See all the ways to get quality prints in your hands ASAP.
Re: [GENERAL]
How come when I pass in a list in it doesn't use it as a list of integers? Do I need to somehow make $1 be interpreted as a list of ints? Rather than just passing a text value that contains the list?CREATE TABLE my_tbl (u_id int);INSERT INTO my_tbl (u_id) values (1);INSERT INTO my_tbl (u_id) values (2);INSERT INTO my_tbl (u_id) values (3);CREATE OR REPLACE FUNCTION getlist(text) RETURNS SETOF my_tbl as $$ SELECT * FROM my_tbl WHERE u_id IN (0, $1);$$ LANGUAGE SQL;SELECT * from getlist('1,2,3'); (0 rows)I'm sure it's probably trival but I'm still learning how psql :) Thanks
Yahoo! Photos
Got holiday prints? See all the ways to get quality prints in your hands ASAP.
Re: [GENERAL] plpgsql question
Terminology point: you used the word "aggregate" but the function below doesn't have an aggregate. Aggregates are functions thatoperate on multiple rows, like count() and sum(); substr() doesn'tdo that so it's not an aggregate. ya. my mistake.[snip] 1. Create a composite type with the desired columns, declare the function to return SETOF that type, and declare row to be of that type.k. this is where i was confused. this is exactly what i wanted/neededThanks Yahoo! Photos Showcase holiday pictures in hardcover Photo Books. You design it and well bind it!
Re: [GENERAL] Passing a list of values to a function
You could write the function to take an array of integers instead of a text string:CREATE OR REPLACE FUNCTION getlist(integer[]) RETURNS SETOF my_tbl as $$SELECT * FROM my_tblWHERE u_id = 0 OR u_id = ANY($1)$$ LANGUAGE SQL STABLE STRICT;SELECT * FROM getlist('{1,2,3}');Another way would be to build a query string in a PL/pgSQL functionand use EXECUTE, but beware of embedding function arguments in querystrings without quoting.-- Michael Fuhr Hmm I suspected it was using it as a text string, seeing how that's how it way defined. I guess querying it as an array would work okay. Is there any way to blow out how the query was executed? Such as seeing all the WHERE joins and what not along with the explain output on the console? I think I read so
mewhere
about it showing in the logs or something?
Yahoo! Photos Showcase holiday pictures in hardcover
Photo Books. You design it and well bind it!
Re: [GENERAL] plpgsql question
Michael Fuhr [EMAIL PROTECTED] wrote: On Thu, Jan 05, 2006 at 12:50:34AM -0800, Matthew Peter wrote: Is it possible to skip the loop and just return all records in a single query and shove all those rows into a table variable?Not in PL/pgSQL -- you need to return each row with RETURN NEXT,generally from within a loop. Why do you want to avoid that?I was thinking it would be more efficient to pull all the records in one call rather than 50 calls. For all I know it probably executes 50 calls in the internals when translating the IN (IDs). * You could use an IF statement to execute the query you need. That's what I was trying to do, but I'm no t sure i was doing it in the right context, since it was IN the query, not testing after it. Figured I'd ask the list if I was trying something impossible or if I was close to help get me on track. * You could put the queries in separate functions. The query is so similiar (occasionally match on extra WHERE arg) it would be nice just to use a conditional to match if that extra argument is given as not null...rather than maintain two simliar functions if possible, while keeping it planned after the first run.Does using an IF predicate in the WHERE in the SQL call require EXECUTE since (I guess) I'm making the SQL statement somewhat dynamic? All I've been able to find is IF handling after the query, not in it.Thanks againMatt Yahoo! DSL Something to write home about. Just $16.99/mo. or less
Re: [GENERAL] plpgsql question
Michael Fuhr [EMAIL PROTECTED] wrote: On Fri, Jan 06, 2006 at 01:14:38AM -0800, Matthew Peter wrote: Michael Fuhr wrote: On Thu, Jan 05, 2006 at 12:50:34AM -0800, Matthew Peter wrote: Is it possible to skip the loop and just return all records in a single query and shove all those rows into a table variable?Not in PL/pgSQL -- you need to return each row with RETURN NEXT, generally from within a loop. Why do you want to avoid that? I was thinking it would be more efficient to pull all the records in one call rather than 50 calls. For all I know it probably executes 50 calls in the internals when translating the IN (IDs).I wouldn't worry about that unless you can demonstrate that it'scausing a performance problem. Even then you're stuck becausethat's how set-returning functions work. * You could use an IF statement to execute the query you need. That's what I was trying to do, but I'm not sure i was doing it in the right context, since it was IN the query, not testing after it. Figured I'd ask the list if I was trying something impossible or if I was close to help get me on track. The IF statement needs to be part of the PL/pgSQL logic, not partof the query string. However, you might be able to use CASE orCOALESCE in the query, as in WHERE my_tbl_id = $1AND CASE WHEN $2 IS NULL THEN TRUE ELSE $2 = username ENDor WHERE my_tbl_id = $1 AND COALESCE($2 = username, TRUE)or WHERE my_tbl_id = $1 AND COALESCE($2, username) = usernameWith predicates such as these you wouldn't need to use EXECUTE and< br>you could write the query only once.-- Michael Fuhr I'll try that out tomorrow. Thanks Micheal Yahoo! DSL Something to write home about. Just $16.99/mo. or less
Re: [GENERAL] plpgsql question
On 1/5/06, Matthew Peter wrote: I'm trying to do a simple SELECT * in plpgsql that returns a set of records as a row w/ columns, not a row into a variable, w/ some conditionals. The function below is semi-pseudo with what I'm trying to... If anyone could give me an example that works by returning it as a resultset maintaining the columns, that would be awesome and I could take it from there. I've read the pl/pgsql section of the docs and the Douglas book but I'm still confused on this issue... Thanks create or replace function getrecord(int,text) RETURNS SETOF records as $$DECLARE-- event := rows to return from the table below BEGINevent := SELECT * FROM my_tblWHERE 1 = 1and my_tbl_id IN (0$1) ||' IF $2 IS NOT NULL THEN' || and username = $2 || 'END IF;'; -- end sql statement RETURN event; END;$$ LANGUAGE plpgsql;Pandurangan R S [EMAIL PROTECTED] wrote: Assuming records is the name of a table...create or replace function getrecord(int,text) RETURNS SETOF records as $$DECLARErow records%rowtype;BEGINFOR row IN SELECT * FROM my_tblWHERE ...LOOPRETURN NEXT row;END LOOP;RETURN;END;$$ LANGUAGE plpgsql;Thanks for the reply. Is it possible to skip the loop and just return all records in a single query and shove all those rows into a table variable? Also, the WHERE part is also important cause I'm not sure i got that part right? Would this call for EXECUTE or will it be okay and be planned the first time by the query planner? Yahoo! Photos Ring in the New Year with Photo Calendars. Add photos, events, holidays, whatever.
[GENERAL] plpgsql question
I'm trying to do a simple SELECT * in plpgsql that returns a set of records as a row w/ columns, not a row into a variable, w/ some conditionals. The function below is semi-pseudo with what I'm trying to... If anyone could give me an example that works by returning it as a resultset maintaining the columns, that would be awesome and I could take it from there. I've read the pl/pgsql section of the docs and the Douglas book but I'm still confused on this issue... Thankscreate or replace function getrecord(int,text) RETURNS SETOF records as $$ DECLARE -- event := rows to return from the table belowBEGIN event := SELECT * FROM my_tbl WHERE 1 = 1 and my_tbl_id IN (0$1) || ' IF $2 IS NOT NULL THEN' || and username = $2 || 'END IF;' ; -- end sql statementRETURN event;END; $$ LANGUAGE plpgsql; Yahoo! DSL Something to write home about. Just $16.99/mo. or less
[GENERAL] inherits index
I have a parent table that is inherited by a couple others... Would it be best to set up an index on the children tables column that is inherited by the parent, or should I index the parent column that is inherited by the children? The children tables are the ones called frequently, not the parent. I just want to help the planner to use the most effective way by setting it up properly for parsing, especially if these tables get really big. Thanks, Matt Yahoo! Photos Ring in the New Year with Photo Calendars. Add photos, events, holidays, whatever.
[GENERAL] How may I keep prepended array items positive?
I would need to prepend a couple array items BUT I NEED them to be positive [1:13] instead of [-4:9] for instance. How may I keep prepended array items positive? __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] How may I keep prepended array items positive?
I want to use it like this...
UPDATE SET _array = {1,2,3} || _array;
Which if _array had {1} in it, I'd get something like
[-2:1]{1,1,2,3} as the range... I only want it to push
the existing values to the right so I'd have
[1:4]{1,1,2,3}
I don't have a pgsql on this box to show output..
--- Michael Fuhr [EMAIL PROTECTED] wrote:
On Mon, Oct 31, 2005 at 04:37:39PM -0800, Matthew
Peter wrote:
I would need to prepend a couple array items BUT I
NEED them to be positive [1:13] instead of [-4:9]
for
instance.
How may I keep prepended array items positive?
You could use array-to-array concatenation instead
of prepending
(aka element-to-array concatenation):
test= SELECT 99 || ARRAY[1, 2, 3]; -- unwanted
results
?column?
--
[0:3]={99,1,2,3}
(1 row)
test= SELECT ARRAY[99] || ARRAY[1, 2, 3]; --
desired results
?column?
{99,1,2,3}
(1 row)
--
Michael Fuhr
__
Yahoo! FareChase: Search multiple travel sites in one click.
http://farechase.yahoo.com
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] function example?
Could someone help me and give me a basic example of
how to write a similiar functional function to the one
below that would use a dynamic table and update a
column only if it held a value.
I've been reading around and can't seem to find the
answer I'm looking for. I just need a simple examle to
build upon. I would rather not use plpgsql if
possible. Tom mentioned the other pl's don't need
EXECUTE or something like that and would prefer clean
syntax if possible.
Thanks a bunch
CREATE OR REPLACE FUNCTION updatefoo(tbl
varchar, data mydata, myid bigint) RETURNS
boolean AS $$
DECLARE
BEGIN
update ${tbl} set
f1 = mydata.f1
IF mydata.f2 IS NOT NULL THEN
,f2 = mydata.f2
END IF;
WHERE id = myid;
IF NOT FOUND THEN
return false;
END IF;
return true;
END
$$ LANGUAGE ?;
__
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com
---(end of broadcast)---
TIP 1: 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: [GENERAL] function example?
Thanks I did read that when looking into EXECUTE, which I'd rather not use. As I said, I would rather not use plpgsql if possible. All I want to do is 1) pass in some variables, 2) reference them, and 3) have a working query which 4) supports SELECTs, and lastly 5) isn't plpgsql but pg_native syntax. --- A. Kretschmer [EMAIL PROTECTED] wrote: am 29.10.2005, um 20:39:23 -0700 mailte Matthew Peter folgendes: Could someone help me and give me a basic example of how to write a similiar functional function to the one below that would use a dynamic table and update a column only if it held a value. http://www.postgresql.org/docs/8.0/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] dynamic table naming in function
Thanks for pointing me in the right direction. I read about EXECUTE in the docs now. Most of the other PLs don't cache query plans at all, and so all queries are effectively EXECUTE'd and there's no issue. I'm not sure what you mean... Is there a more suitable LANGUAGE declaration you would recommend? If possible, I would also like to return results from SELECT statments from the function but EXECUTE doesn't return any results. I'm not tied to plpgsql, but would like to make a couple dynamic functions with whatever way is simplest or best tool for the job. --- Tom Lane [EMAIL PROTECTED] wrote: Matthew Peter [EMAIL PROTECTED] writes: Out of curiosity, I was wondering if it is possible to use dynamic table names in a function? In plpgsql, you can do this by building dynamic query strings and EXECUTE'ing them. Most of the other PLs don't cache query plans at all, and so all queries are effectively EXECUTE'd and there's no issue. You'll want to read up on quote_literal and quote_ident to help you in building correct query strings. regards, tom lane __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 1: 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: [GENERAL] searching array
Thanks for the reply. I'm using 8.0.3. I'm using
something similiar to the example you gave. My
postgresql install is on offline developement box and
I would have to type it all out longhand.
Shouldn't = also return Carols records since she
contains records GREATER THAN 1? This is the
problem I'm having, there's no errors, just no records
matching the (gt) part. Only exact matches.
How about using (gt) instead of = (gte)?
It doesn't return any records right? Shouldn't it
match Carols also since sal_emp is _int?
--- Joe Conway [EMAIL PROTECTED] wrote:
Matthew Peter wrote:
I was trying to search a array with a GTE to value
and
it doesn't work. Is this supported? Or am I
missing
something?
SELECT * FROM sal_emp WHERE 1 = ALL
(pay_by_quarter);
Works for me:
CREATE TABLE sal_emp (
name text,
pay_by_quarter integer[],
schedule text[][]
);
CREATE TABLE
INSERT INTO sal_emp VALUES (
'Bill',
'{1, 1, 1, 1}',
'{{meeting, lunch}, {training,
presentation}}'
);
INSERT 164825 1
INSERT INTO sal_emp VALUES (
'Carol',
'{2, 25000, 25000, 25000}',
'{{breakfast, consulting}, {meeting,
lunch}}'
);
INSERT 164826 1
SELECT * FROM sal_emp WHERE 1 = ALL
(pay_by_quarter);
name | pay_by_quarter |
schedule
--+---+---
Bill | {1,1,1,1} |
{{meeting,lunch},{training,presentation}}
(1 row)
Care to provide some more info? What version of
Postgres are you using,
what is the exact SQL that is failing, and what is
the error message
you're getting?
Joe
__
Yahoo! Music Unlimited
Access over 1 million songs. Try it free.
http://music.yahoo.com/unlimited/
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] searching array
Alright, what about with ANY? --- Joe Conway [EMAIL PROTECTED] wrote: Matthew Peter wrote: Shouldn't = also return Carols records since she contains records GREATER THAN 1? This is the problem I'm having, there's no errors, just no records matching the (gt) part. Only exact matches. Look again at your query: SELECT * FROM sal_emp WHERE 1 = ALL (pay_by_quarter); You are asking for matches where 1 is greater than or equal to all the elements in the array. In Carol's case 1 is less than all the elements -- hence no match. Joe __ Yahoo! Music Unlimited Access over 1 million songs. Try it free. http://music.yahoo.com/unlimited/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] searching array
Thanks. That syntax didn't look right to find values gte 1. But thanks everyone! --- Tom Lane [EMAIL PROTECTED] wrote: Matthew Peter [EMAIL PROTECTED] writes: Yes. I did read it wrong. I wanted to find all records that contained x where x = 1 Then flip it around: contain x where 1 = x 1 = ANY (array) For syntactic reasons, there's no ANY(array) = x construct, so you have to write it this way. regards, tom lane __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] searching array
Yes. I did read it wrong. I wanted to find all records
that contained x where x = 1
I am using ANY in my query on my test box I copied the
ALL from example query in the docs, which still isn't
working for me.
--- Tom Lane [EMAIL PROTECTED] wrote:
Matthew Peter [EMAIL PROTECTED] writes:
Shouldn't = also return Carols records since she
contains records GREATER THAN 1?
You seem to be reading the construct backwards.
x = ALL (array)
is true if x = every member of the array. This is
clearly false
for x = 1 and array = {2, 25000, 25000,
25000} ... in fact,
x isn't = any of those members.
regards, tom lane
__
Yahoo! Music Unlimited
Access over 1 million songs. Try it free.
http://music.yahoo.com/unlimited/
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] searching array
I have it backwards huh? Since the variables are
switched around in a ANY search
I want a SELECT * FROM table WHERE arrcol = 1
How do I write it to get those results?
--- Tom Lane [EMAIL PROTECTED] wrote:
Matthew Peter [EMAIL PROTECTED] writes:
Shouldn't = also return Carols records since she
contains records GREATER THAN 1?
You seem to be reading the construct backwards.
x = ALL (array)
is true if x = every member of the array. This is
clearly false
for x = 1 and array = {2, 25000, 25000,
25000} ... in fact,
x isn't = any of those members.
regards, tom lane
__
Yahoo! Music Unlimited
Access over 1 million songs. Try it free.
http://music.yahoo.com/unlimited/
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] searching array
Let me also say that I'm retarded. No excuses from me. I'm officially retarded. --- Matthew Peter [EMAIL PROTECTED] wrote: Thanks. That syntax didn't look right to find values gte 1. But thanks everyone! --- Tom Lane [EMAIL PROTECTED] wrote: Matthew Peter [EMAIL PROTECTED] writes: Yes. I did read it wrong. I wanted to find all records that contained x where x = 1 Then flip it around: contain x where 1 = x 1 = ANY (array) For syntactic reasons, there's no ANY(array) = x construct, so you have to write it this way. regards, tom lane __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org __ Yahoo! Music Unlimited Access over 1 million songs. Try it free. http://music.yahoo.com/unlimited/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] searching array
I was trying to search a array with a GTE to value and it doesn't work. Is this supported? Or am I missing something? SELECT * FROM sal_emp WHERE 1 = ALL (pay_by_quarter); __ Yahoo! Music Unlimited Access over 1 million songs. Try it free. http://music.yahoo.com/unlimited/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
Someone trying to stick microsoft yet another place they don't belong. --- Johan Wehtje [EMAIL PROTECTED] wrote: Very much a description of the Business I am in. For all the criticism leveled at it, I still think that as a rich Database Client that permits really rapid development of Database driven applications Access is unbeatable. Pair it with a good Database server and it is the perfect combination. __ Yahoo! Music Unlimited Access over 1 million songs. Try it free. http://music.yahoo.com/unlimited/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0? Access-like Query builder C++ Vector-based GUI binding
This thread should continue under the proper title since it's been hi-jacked . I didn't read your entire post. If you know how to join a pk and fk it's not difficult to build an effective diagram on paper and reuse the same schema for other applications. I think there really is a need for a rich DB client that allows Rapid development and is easy to link to an office Suite. To be useful to a business a database needs the applications built on top of it Ya I watched the videos on microsofts new Mail sparkle applications. Mail suprisingly uses a database backend to manage their files which may helped open eyes of the ways they could use them in other ares of the desktop. For instance, w/ sparkle you could write a simple program to do what you need as defining tables and relationships is easy. They also have a 3d engine so you can emerse yourself in the database and fly around the tables! Is it possible to bind vector interfaces to C++ apps w/ libs like (a href=http://www.linuxartist.org/2d.html;ZODIUS/a)? I'm not sure how ENLIGHTENMENT runs their engine on xorg but it's not vector based. If possible I'd like to know. I don't have the time now but in the near future I plan to find out. Maybe someone here already knows? It would be pretty neat to build desktop packages that scale and stretch any resolution or device. __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] query execution
There a way to watch queries execute on the pgsql server as queries come in from the network and are processed? Thanks MP __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] transaction toggling
Is there a way to disable transactions on certian queries? I read a while back that this wasn't possible, just wondering if it is in 8.1 or planned in the future? Does it even make a dent in the performance if a query is wrapped in a transaction instead of out? Such as a view counter, like... update views = views + 1 where x = 1;? Thanks MP __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] array_dims array_lower/upper distance
--- Guy Fraser [EMAIL PROTECTED] wrote: On Thu, 2005-22-09 at 21:52 -0500, Bruno Wolff III wrote: On Thu, Sep 22, 2005 at 14:16:48 -0600, Guy Fraser [EMAIL PROTECTED] wrote: On Thu, 2005-22-09 at 12:43 -0400, Greg Stark wrote: Guy Fraser [EMAIL PROTECTED] writes: So to answer his question he would likely want : SELECT array_upper(item,1) - array_upper(item,0) + 1 as elements FROM arraytest ; Note that this doesn't work for empty arrays. It will return NULL instead of 0. Your response was not at all helpfull, I would like to encourage you to expand on what I put off the top of my head. I have not used array_upper() before, and the question was how to return the total number of elements, not how to handle NULL and empty arrays. I think his point was that your example was going to give the wrong answer for empty arrays, which is relevant to your question. The normal way around that is to use the COALESCE function. OK what I jotted down was totally wrong. This is slightly more correct : SELECT array_upper(item,1) - array_lower(item,1) + 1 as elements FROM arraytest ; Without do a tonne of research, I can not refine this to handle all circumstances. Can someone point me to documentation that explains the function better than : Dimensions can also be retrieved with array_upper and array_lower, which return the upper and lower bound of a specified array dimension, respectively. The table Table 9-36. array Functions does not explain how empty and null arrays are handled either. How do array_upper() and array_lower() respond to : 1) NULL 2) Empty Array 3) Nonexistent requested dimension Also is there a function that specifies how many dimensions the array has? That was exactly the answer I was looking for when I posted the question. Now if there was a function to delete a position in the array ie set array1 = array_delete_at(array1,5) where 5 is the position to delete __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] array_dims array_lower/upper distance
--- Guy Fraser [EMAIL PROTECTED] wrote: On Fri, 2005-23-09 at 09:48 -0700, Matthew Peter wrote: --- Guy Fraser [EMAIL PROTECTED] wrote: ...snip... OK what I jotted down was totally wrong. This is slightly more correct : SELECT array_upper(item,1) - array_lower(item,1) + 1 as elements FROM arraytest ; Without do a tonne of research, I can not refine this to handle all circumstances. Can someone point me to documentation that explains the function better than : Dimensions can also be retrieved with array_upper and array_lower, which return the upper and lower bound of a specified array dimension, respectively. The table Table 9-36. array Functions does not explain how empty and null arrays are handled either. How do array_upper() and array_lower() respond to : 1) NULL 2) Empty Array 3) Nonexistent requested dimension Also is there a function that specifies how many dimensions the array has? That was exactly the answer I was looking for when I posted the question. Now if there was a function to delete a position in the array ie set array1 = array_delete_at(array1,5) where 5 is the position to delete I hope someone else can answer that, the best I can do is provide a link to the docs : http://www.postgresql.org/docs/current/static/functions-array.html My best guess is that you need to walk the array and drop the element you don't want. The way I currently use arrays is I read the whole array into my application the modify the array then update the whole array. Unfortunately the arrays in PG are not associative and the elements must be sequential. I only use arrays in limited ways in PG because of earlier constraints, and have not needed to investigate the newer features. Good luck. Ya. I read the docs and the (limitedly useful) Douglas book. I'm just playing around with arrays. Michael Fuhr suggested a intarray_del_elem() function. You may want to take a look at it too? MP __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 1: 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
[GENERAL] array_dims array_lower/upper distance
Wondering if there's a way for postgres to return how many elements are in a array as a single integer? For instance, returning 10 (items in array) instead of [-5:4] Also, is there a way to return the position of an item in a array? __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] back references using regex
How about this then, I didn't retain that information from the doc. ;) I sometimes glaze over important gems every now and then. It happens. I'm not a robot, yet. At least I know the answer to my question is now retained. You were a big help too. Thank you very much. I appreciate it. Speaking of data manipulation in a table... I was thinking about storing and manipulating a list in a column... Is it possible to append and delete (unknown location) items in a list? Or is another way more efficient? I'm relatively new so sorry if I'm asking too many questions. If possible, I will read about it if there's docs for it so I don't trouble anyone with my questions. It would be sweet to do that at the database level. Thanks again, MP Posgresql convert --- Michael Fuhr [EMAIL PROTECTED] wrote: On Sat, Sep 10, 2005 at 10:27:13AM -0700, Matthew Peter wrote: ah I swear I never came across any of these gems of information in the docs. It was these subtle differences that were throwing me. From Regular Expression Escapes in the Pattern Matching section of the manual: A back reference (\n) matches the same string matched by the previous parenthesized subexpression specified by the number n (see Table 9-18). For example, ([bc])\1 matches bb or cc but not bc or cb. The subexpression must entirely precede the back reference in the RE. Subexpressions are numbered in the order of their leading parentheses. Non-capturing parentheses do not define subexpressions. http://www.postgresql.org/docs/8.0/static/functions-matching.html#POSIX-ESCAPE-SEQUENCES -- Michael Fuhr ---(end of broadcast)--- TIP 1: 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 __ Yahoo! for Good Watch the Hurricane Katrina Shelter From The Storm concert http://advision.webevents.yahoo.com/shelter ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] list manipulation at column level
Is it possible to append and delete (unknown location) items in a list stored in a column? For instance, a column with 'some,values,in,a,list,12,34'; Could I [ap|pre]pend and or delete items in this list through pgsql? __ Yahoo! for Good Watch the Hurricane Katrina Shelter From The Storm concert http://advision.webevents.yahoo.com/shelter ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] list manipulation at column level
I was reading the concatenation operator earlier.
Postgresql is very neat.
What about deleting an item in the array without
knowing it's position? For example, deleting the item
12 in the array? Is that possible? Like using
UPDATE array_delete_at(array_find(a,'12'));
where a is the column and 12 is the value to find
or would a select be in order to find the position?
from the docs
-
SELECT * FROM sal_emp WHERE 1 = ALL
(pay_by_quarter);
Tip: Arrays are not sets; searching for specific
array elements may be a sign of database misdesign.
Consider using a separate table with a row for each
item that would be an array element. This will be
easier to search, and is likely to scale up better to
large numbers of elements.
-
Lastly, what's considered a large number of elements?
--- Michael Fuhr [EMAIL PROTECTED] wrote:
On Sun, Sep 11, 2005 at 04:02:24PM -0700, Chris
Travers wrote:
Matthew Peter wrote:
Is it possible to append and delete (unknown
location)
items in a list stored in a column? For instance,
a column with 'some,values,in,a,list,12,34';
Could I [ap|pre]pend and or delete items in this
list
through pgsql?
prepend:
'value' || ',' || column
append
column || ',' ||'value'
Or use an array type and perform array operations.
http://www.postgresql.org/docs/8.0/interactive/arrays.html
http://www.postgresql.org/docs/8.0/interactive/functions-array.html
CREATE TABLE foo (a text[]);
INSERT INTO foo VALUES
('{some,values,in,a,list,12,34}');
SELECT array_prepend('foo', a) FROM foo;
array_prepend
-
[0:7]={foo,some,values,in,a,list,12,34}
(1 row)
SELECT array_append(a, 'foo') FROM foo;
array_append
---
{some,values,in,a,list,12,34,foo}
(1 row)
SELECT array_cat(a[1:2], a[6:7]) FROM foo;
array_cat
-
{some,values,12,34}
(1 row)
--
Michael Fuhr
__
Yahoo! for Good
Watch the Hurricane Katrina Shelter From The Storm concert
http://advision.webevents.yahoo.com/shelter
---(end of broadcast)---
TIP 1: 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: [GENERAL] back references using regex
ah I swear I never came across any of these
gems of information in the docs. It was these subtle
differences that were throwing me.
I didn't originally catch that regex's were based on
grep/sed/awk syntax which I haven't studied throughly
yet. I've only used some basic operations in bash
scripts. I'll read up more on those. Thanks.
MP
--- Douglas McNaught [EMAIL PROTECTED] wrote:
Matthew Peter [EMAIL PROTECTED] writes:
One other thing, when I wrote back I actually used
34.31.29.20 (random), not 12.00.00.34 like i
showed in
the example, which is why i said it didn't work on
digits.
SELECT substring('34.31.29.20' FROM
$$((\w+)\.\2)$$);
substring
---
(1 row)
little did i know writing it with 12.00.00.34
would
return 00.00... so yes, that did suprise me.
Apparently only using the identical values returns
a
value. so it's saying x+ one more of the same
value
separated by a period... where shouldn't it be any
letter, number or underscore followed by any
letter, number or underscore?
Backreferences match the exact string matched by the
corresponding set
of parentheses. It's not the equivalent of
substituting in the
parenthesized part of the regex and testing that for
a match. The
behavior above is as expected. If you want it as
any followed by
any you shold write the regex as '((\w+)\.(\w+))'
-- then the two
parts can differ.
-Doug
__
Click here to donate to the Hurricane Katrina relief effort.
http://store.yahoo.com/redcross-donate3/
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] back references using regex
One other thing, when I wrote back I actually used
34.31.29.20 (random), not 12.00.00.34 like i showed in
the example, which is why i said it didn't work on
digits.
SELECT substring('34.31.29.20' FROM $$((\w+)\.\2)$$);
substring
---
(1 row)
little did i know writing it with 12.00.00.34 would
return 00.00... so yes, that did suprise me.
Apparently only using the identical values returns a
value. so it's saying x+ one more of the same value
separated by a period... where shouldn't it be any
letter, number or underscore followed by any
letter, number or underscore?
--- Michael Fuhr [EMAIL PROTECTED] wrote:
On Thu, Sep 08, 2005 at 01:52:35PM -0700, Matthew
Peter wrote:
It's not a complex regex as I have wrote one that
does
what I want, yet not at the database level. The
docs
didn't help clarify anything. I'm still not clear
on
how it determines where the back reference comes
from
in the previous example you gave. And why digits
wouldn't work.
Back references work as they usually do in regular
expressions:
they refer to the matched value of a previous
parenthesized
subexpression. If you have multiple open
parentheses then you
need to refer to the correct subexpression to get
what you want.
Example:
CREATE TABLE foo (t text);
INSERT INTO foo VALUES ('abc.foo.foo.xyz');
INSERT INTO foo VALUES ('12.00.00.34');
INSERT INTO foo VALUES ('abc.def.ghi');
SELECT t FROM foo WHERE t ~ $$(\w+)\.\1$$;
t
-
abc.foo.foo.xyz
12.00.00.34
(2 rows)
In the above query the regular expression has only
one set of
parentheses, so the back reference refers to \1.
The result set
contains the two rows that have one or more word
characters followed
by a dot followed by the same set of characters.
In the following query, note the difference between
the regular
expression in the select list and the one in the
where clause:
SELECT substring(t FROM $$((\w+)\.\2)$$)
FROM foo
WHERE t ~ $$(\w+)\.\1$$;
substring
---
foo.foo
00.00
(2 rows)
In the regular expression in the select list, we use
the outermost
set of parentheses for grouping, so the back
reference needs to
refer to the subexpression that begins with the
second open
parenthesis (i.e., we must use \2). In the regular
expression
in the where clause, we have only one set of
parentheses so the
back reference is \1.
Regarding digits, you didn't post any output in your
example, so
we don't know if it really doesn't work or if it
just doesn't do
what you were expecting. Here's what I get from
your examples:
SELECT substring('12.00.00.34' FROM
$$((\d+)\.\2)$$);
substring
---
00.00
(1 row)
SELECT substring('12.00.00.34' FROM
$$((\w+)\.\2)$$);
substring
---
00.00
(1 row)
SELECT substring('abc.foo.foo.xyz' FROM
$$((\w+)\.\2)$$);
substring
---
foo.foo
(1 row)
Do you get different results, or do these results
surprise you?
They all appear to be correct.
What I basically want to do is have a slice
function
like Python, where I can slice out items from a
\s, \.
or \n\n separated list. Where I'll just change the
delimiter for the query that it applies.
Where I could start it at a certain point and end
it
at another. Like slicing out paragraph 3-6
(delimiter
\n\n) or the 2nd-6th sentence in a article
(delimiter
\.). That is what I am trying to do.
You can use split_part() to get a single item or
string_to_array()
to build an array from which you can extract
multiple items.
CREATE TABLE foo (t text);
INSERT INTO foo VALUES
('one.two.three.four.five.six.');
SELECT (string_to_array(t, '.'))[3:5] FROM foo;
string_to_array
---
{three,four,five}
(1 row)
SELECT array_to_string((string_to_array(t,
'.'))[3:5], '.') FROM foo;
array_to_string
-
three.four.five
(1 row)
Is that what you're looking for?
--
Michael Fuhr
---(end of
broadcast)---
TIP 2: Don't 'kill -9' the postmaster
__
Click here to donate to the Hurricane Katrina relief effort.
http://store.yahoo.com/redcross-donate3/
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] back references using regex
That doesn't seem to work with digits
SELECT substring('12.00.00.34' FROM $$((\d+)\.\2)$$);
or
SELECT substring('12.00.00.34' FROM $$((\w+)\.\2)$$);
but works with strings
SELECT substring('abc.foo.foo.xyz' FROM
$$((\w+)\.\2)$$);
What I basically want to do is have a slice function
like Python, where I can slice out items from a \s, \.
or \n\n separated list.
Where I could start it at a certain point and end it
at another. Like slicing out paragraph 3-6 (delimiter
\n\n) or the 2nd-6th sentence in a article (delimiter
\.). That is what I am trying to do. I know if I can
figure how to get that working I can figure out how to
deal with extending it/handling misc. characters/etc
in pgsql. I only need a working vanilla example.
Thanks for all you help.
--- Michael Fuhr [EMAIL PROTECTED] wrote:
SELECT substring('abc.foo.foo.xyz' FROM
'(([[:alpha:]]+)\\.\\2)');
substring
---
foo.foo
(1 row)
That is, one or more alphabetic characters followed
by a dot followed
by the same set of characters (this is a simplistic
example: it would
also match 'foo.oog' and return 'oo.oo').
Note that the back reference is \2 because it refers
to the inner
set of parentheses (i.e., the subexpression with the
second opening
parenthesis); the outer set is used here for
capturing. And again,
note the escaped backslashes because we're using
ordinary quotes.
With dollar quotes the above query would be:
SELECT substring('abc.foo.foo.xyz' FROM
$$(([[:alpha:]]+)\.\2)$$);
--
Michael Fuhr
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
---(end of broadcast)---
TIP 1: 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: [GENERAL] back references using regex
Ya, but I'd have to recompile to get python in. Plus,
I don't want to use Python. I want to use and learn
more pgsql. Keep things clean and lean if possible...
I just got a postgres book yesterday for additional
reading which it only had 2 pages on regex's in the
index :(
--- Peter Fein [EMAIL PROTECTED] wrote:
Matthew Peter wrote:
That doesn't seem to work with digits
SELECT substring('12.00.00.34' FROM
$$((\d+)\.\2)$$);
or
SELECT substring('12.00.00.34' FROM
$$((\w+)\.\2)$$);
but works with strings
SELECT substring('abc.foo.foo.xyz' FROM
$$((\w+)\.\2)$$);
What I basically want to do is have a slice
function
like Python, where I can slice out items from a
\s, \.
or \n\n separated list.
You could always just write it in pl/python...
--
Peter Fein [EMAIL PROTECTED]
773-575-0694
Basically, if you're not a utopianist, you're a
schmuck. -J. Feldman
__
Click here to donate to the Hurricane Katrina relief effort.
http://store.yahoo.com/redcross-donate3/
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] back references using regex
I knew I should never have said Python. I know regular expressions, just not how postgresql handles them. The fact of the matter is I don't want to use Python, it was an example of the functionality I'm interested in accomplishing with pgsql. Plus, I would like to use other regex's once I figure out how they are used. I only need a regular expression in the substring of a where cluase. Not entire language support for a single function. It's not a complex regex as I have wrote one that does what I want, yet not at the database level. The docs didn't help clarify anything. I'm still not clear on how it determines where the back reference comes from in the previous example you gave. And why digits wouldn't work. I would like a basic example that accomplishes what I'm trying to do if at all possible? My original message/problem... What I basically want to do is have a slice function like Python, where I can slice out items from a \s, \. or \n\n separated list. Where I'll just change the delimiter for the query that it applies. Where I could start it at a certain point and end it at another. Like slicing out paragraph 3-6 (delimiter \n\n) or the 2nd-6th sentence in a article (delimiter \.). That is what I am trying to do. I know if I can figure how to get that working I can figure out how to deal with extending it/handling misc. characters/etc in pgsql. I only need a working vanilla example. Thanks for all your help. --- Michael Fuhr [EMAIL PROTECTED] wrote: On Thu, Sep 08, 2005 at 12:45:40PM -0700, Matthew Peter wrote: Ya, but I'd have to recompile to get python in. Recompiling to add support for another procedural language is a one-time operation and it's easy to do, so that's not a good argument. Plus, I don't want to use Python. I want to use and learn more pgsql. Keep things clean and lean if possible... Clean and lean suggests using the right tool for the job. Languages like Perl and Python are better at string manipulation than PL/pgSQL, and one of PostgreSQL's strengths is that it allows you to write server-side functions in those languages. Exploit such strengths when it makes sense. I just got a postgres book yesterday for additional reading which it only had 2 pages on regex's in the index :( Regular expressions aren't specific to PostgreSQL; there's ample material covering them elsewhere. See for example _Mastering Regular Expressions_ by Jeffrey Friedl. I'm sure a search engine would yield many free tutorials on the subject. -- Michael Fuhr __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] back references using regex
Thank you for your patience and such a complete
answer. I'm not on the pgbox right now but those
examples did help clarify how to reference the back
references, which was my problem.
I wasn't aware the 1st parenthesis must be counted as
part of the regex, I assumed it was a wrapper. Thanks
for helping me out and putting up with me. :)
matt
--- Michael Fuhr [EMAIL PROTECTED] wrote:
On Thu, Sep 08, 2005 at 01:52:35PM -0700, Matthew
Peter wrote:
It's not a complex regex as I have wrote one that
does
what I want, yet not at the database level. The
docs
didn't help clarify anything. I'm still not clear
on
how it determines where the back reference comes
from
in the previous example you gave. And why digits
wouldn't work.
Back references work as they usually do in regular
expressions:
they refer to the matched value of a previous
parenthesized
subexpression. If you have multiple open
parentheses then you
need to refer to the correct subexpression to get
what you want.
Example:
CREATE TABLE foo (t text);
INSERT INTO foo VALUES ('abc.foo.foo.xyz');
INSERT INTO foo VALUES ('12.00.00.34');
INSERT INTO foo VALUES ('abc.def.ghi');
SELECT t FROM foo WHERE t ~ $$(\w+)\.\1$$;
t
-
abc.foo.foo.xyz
12.00.00.34
(2 rows)
In the above query the regular expression has only
one set of
parentheses, so the back reference refers to \1.
The result set
contains the two rows that have one or more word
characters followed
by a dot followed by the same set of characters.
In the following query, note the difference between
the regular
expression in the select list and the one in the
where clause:
SELECT substring(t FROM $$((\w+)\.\2)$$)
FROM foo
WHERE t ~ $$(\w+)\.\1$$;
substring
---
foo.foo
00.00
(2 rows)
In the regular expression in the select list, we use
the outermost
set of parentheses for grouping, so the back
reference needs to
refer to the subexpression that begins with the
second open
parenthesis (i.e., we must use \2). In the regular
expression
in the where clause, we have only one set of
parentheses so the
back reference is \1.
Regarding digits, you didn't post any output in your
example, so
we don't know if it really doesn't work or if it
just doesn't do
what you were expecting. Here's what I get from
your examples:
SELECT substring('12.00.00.34' FROM
$$((\d+)\.\2)$$);
substring
---
00.00
(1 row)
SELECT substring('12.00.00.34' FROM
$$((\w+)\.\2)$$);
substring
---
00.00
(1 row)
SELECT substring('abc.foo.foo.xyz' FROM
$$((\w+)\.\2)$$);
substring
---
foo.foo
(1 row)
Do you get different results, or do these results
surprise you?
They all appear to be correct.
What I basically want to do is have a slice
function
like Python, where I can slice out items from a
\s, \.
or \n\n separated list. Where I'll just change the
delimiter for the query that it applies.
Where I could start it at a certain point and end
it
at another. Like slicing out paragraph 3-6
(delimiter
\n\n) or the 2nd-6th sentence in a article
(delimiter
\.). That is what I am trying to do.
You can use split_part() to get a single item or
string_to_array()
to build an array from which you can extract
multiple items.
CREATE TABLE foo (t text);
INSERT INTO foo VALUES
('one.two.three.four.five.six.');
SELECT (string_to_array(t, '.'))[3:5] FROM foo;
string_to_array
---
{three,four,five}
(1 row)
SELECT array_to_string((string_to_array(t,
'.'))[3:5], '.') FROM foo;
array_to_string
-
three.four.five
(1 row)
Is that what you're looking for?
--
Michael Fuhr
---(end of
broadcast)---
TIP 2: Don't 'kill -9' the postmaster
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] back references using regex
Hi. I'm trying to do a slice directly from a table so I can get a brief preview of the articles content by counting \s (spaces), not new paragraphs. Anyone know how it could be done using regular expressions natively? I read the doc but it didn't help me much. Many thanks. MP __ Click here to donate to the Hurricane Katrina relief effort. http://store.yahoo.com/redcross-donate3/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] table size performace
How many rows does it take for select performance on a table to degrade? I hope this question isn't to ambiguous (ie lollipop licks). But seriously, 100,000? 1,000,000? 10,000,000? With just a regular lookup on an unique index. Nothing crazy or aggregate. EX: select * from bigtable where id = 123456789 __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] same size VARCHAR or INT IX faster?
I assumed as much. Now's the time for me to optimize so I'd rather know and make optimizations accordingly, than step blindly. Thanks for the reply. As always, your a big help. --- Richard Huxton dev@archonet.com wrote: Matthew Peter wrote: same size VARCHAR or INT IX faster? i assume INT. The reason I ask is I was wondering what (if any) is the avg delay from one over the other? And benefit of one over the other? Thanks. If you want numbers, use INT. If you want text use a VARCHAR. It's probably difficult to come up with speed comparisons for the same size since varchar will have an overhead for the field-length as well as the number of characters. Even then, you'd have to account for client language and application overheads. In any case, optimising at this level is unlikely to be a good use of your time unless you really have reached the practical limits of available hardware. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] same size VARCHAR or INT IX faster?
same size VARCHAR or INT IX faster? i assume INT. The reason I ask is I was wondering what (if any) is the avg delay from one over the other? And benefit of one over the other? Thanks. __ Yahoo! Mail Stay connected, organized, and protected. Take the tour: http://tour.mail.yahoo.com/mailtour.html ---(end of broadcast)--- TIP 1: 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: [GENERAL] optimum settings for dedicated box
Hmmm. I was thinking of a more comprehensive solution or document resource. I would like to know what does what. Why tweak that or why not to ya know? Searching gets me such fragmented results I chose to ask the ones whom are more familiar with this fabulous piece of software and used it in real world situations. Does anyone know of a some good docs on the subject of dedicated db optimization for postgresql 8.0.3? Is 8.1 to early to use in a production environment? With just the regular old 8.0.3 stuff? Thanks for the tips too. I always appreciate tips. :) Thanks again, Matthew A. Peter --- Jim C. Nasby [EMAIL PROTECTED] wrote: On Tue, Aug 30, 2005 at 09:43:19PM -0700, Ian Harding wrote: Mine in similar, and the only thing I have changed from defaults is work_mem. It made certain complex queries go from taking forever to taking seconds. I have a database connection pool limited to 10 connections, so I set it to 10MB. That means (to me, anyway) that work_mem will never gobble more then 100MB. Seems OK since I have 1GB. That's not totally true. A single query can use work_mem for multiple steps, so if work_mem is 10MB a single query could end up using 20MB, 30MB, or even more. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com 512-569-9461 ---(end of broadcast)--- TIP 1: 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 __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 1: 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
