[SQL] psql question
Hello, I've been searching off-and-on for an answer to this question, but I haven't found anything but fleeting references. Here's the problem/question: On machines where I've installed PostgreSQL 7.0.2 from RPM, psql allows use of the up arrow key for history and the escape/tab key for command completion, but on my remote web host (webpipe.net) those keys don't work. What do I need to do to get these features working on my remote web host? I've seen reference to .psqlrc in the psql man file, but not much else (no syntax, etc). Thanks in advance! Joe
Re: [SQL] psql question
> > On machines where I've installed PostgreSQL 7.0.2 from RPM, psql allows use > > of the up arrow key for history and the escape/tab key for command > > completion, but on my remote web host (webpipe.net) those keys don't work. > > What do I need to do to get these features working on my remote web host? > > I've seen reference to .psqlrc in the psql man file, but not much else (no > > syntax, etc). > > If you compiled from source, make sure you had the readline-devel package > installed. > Thanks for your responses, Peter & Mathijs. Actually, since the issue is on a hosted system, I don't have access to recompile. I was hoping that maybe there would be something I could configure from my own (non-root and non-postgres) account. My question actaully took several days just to make it to the list, so in the meantime I found my own work-around -- I copied local copies of psql and libpq up to my account on the web host. Then I put my own account folder to the front of the PATH and added a LD_LIBRARY_PATH. Now when I run 'psql' it works just like on my own machine! Thanks again for the response. Joe
[SQL] Fw: Optimization recommendations request
Well, this message never made it through, but I managed to answer my own question -- I never ran vacuum analyze which caused a table scan instead of an index scan. After running vacuum analyze the query returns immediately. I would still be interested in any hints to optimize performance for very large tables (>10M tuples). Thanks, Joe > Hello, > > I'm working on an application where I need to design for one table to grow > to an extremely large size. I'm already planning to partition the data into > multiple tables, and even possibly multiple servers, but even so each table > may need to grow to the 10 - 15 million tuple range. This table will be used > for a keyed lookup and it is very important that the query return in well > under a second. I've done a small test using a dual ppro 200 server with 512 > MB RAM, running RedHat Linux 6.1, and a late November 7.1 pre-beta snapshot > of PostgreSQL (PGOPTS="-D/opt/pgsql/data -i -B 25000"). I used a test table > with about 5 million tuples. > > Details: > > CREATE TABLE foo( > guid varchar(20) not null, > ks varchar(20) not null > ); > > --> insert 5,000,000 rows, both guid and ks are 20 byte sha-1 hashes > -- tried this first > -- create index foo_idx1 on foo(guid); > -- then tried > create index foo_idx1 on foo using HASH (guid); > > SELECT ks FROM foo WHERE guid = 'f9ee1930f4010ba34cc5ca6efd27d988eb4f921d'; > > The query currently takes in excess of 40 seconds. I would appreciate any > suggestions for optimizing to bring this down substantially. > > Thanks in advance, > > Joe Conway >
[SQL] Optimization recommendations request
Hello, I'm working on an application where I need to design for one table to grow to an extremely large size. I'm already planning to partition the data into multiple tables, and even possibly multiple servers, but even so each table may need to grow to the 10 - 15 million tuple range. This table will be used for a keyed lookup and it is very important that the query return in well under a second. I've done a small test using a dual ppro 200 server with 512 MB RAM, running RedHat Linux 6.1, and a late November 7.1 pre-beta snapshot of PostgreSQL (PGOPTS="-D/opt/pgsql/data -i -B 25000"). I used a test table with about 5 million tuples. Details: CREATE TABLE foo( guid varchar(20) not null, ks varchar(20) not null ); --> insert 5,000,000 rows, both guid and ks are 20 byte sha-1 hashes -- tried this first -- create index foo_idx1 on foo(guid); -- then tried create index foo_idx1 on foo using HASH (guid); SELECT ks FROM foo WHERE guid = 'f9ee1930f4010ba34cc5ca6efd27d988eb4f921d'; The query currently takes in excess of 40 seconds. I would appreciate any suggestions for optimizing to bring this down substantially. Thanks in advance, Joe Conway
Re: [SQL] Optimization recommendations request
Subject: Re: [SQL] Optimization recommendations request > > What does explain show for your query? > I sent this a week ago using the wrong (i.e. not the one I signed up with) reply-to address, so it didn't make it to the list until just now. In the meantime I ran explain and noticed that the index wasn't being used. So I ran vacuum analyze and now I'm getting the expected performance (and explain shows the index being used). If I understand the logged statistics correctly, I'm getting results returned in anywhere from about 3 to 45 ms, depending on cache hit rate. I also repeated my test with 15 million records with similar results. Not bad at all! I am still interested in any generic optimization tips for very large tables. Thanks for taking the time to reply! Joe
[SQL] single byte unsigned integer datatype
Hello, I was looking for a datatype to represent a single byte unsigned integer. The closest thing I can find looking through the online manual is a one byte char. Are there any side-effects of using a char datatype for this purpose? Is there a better datatype to use? Thanks in advance, Joe
[SQL] current host and dbname info
Hi, I've been searching the docs and been unable to find the answer to this -- is there a way to get the current database server host tcpip address, postmaster port, and database name from a SQL query? I'd like to access those from within a plpgsql function without having to create and populate some sort of identification table. Thanks, Joe
[SQL] Fw: C function for use from PLpgSQL trigger
Hello all, I posted this (see below) Friday to the interfaces list with no response. Does anyone know if what I'm trying to do is possible, or should I just write the entire thing in a C function trigger? The latter would be unfortunate because I think it would be nice to be able to extend PLpgSQL using C functions like this. Anyway, any help or advice will be much appreciated! Thanks, Joe > Hi, > > I'm trying to create a C function that I can call from within a PLpgSQL > trigger function which will return a list of all the values in the NEW > record formatted suitably for an insert statement. I can't come up with a > way to do this directly from within PLpgSQL (i.e. iterate through an > arbitrary number of NEW.attribute). > > Can anyone tell me how I can either pass the NEW record to the C function > (this produces an error message 'NEW used in a non-rule query') or gain > access to the trigger tuple from within my C function? It seems that > although PLpgSQL is called as a trigger, the C function is called as a > regular function (makes sense) and thus has no access to the trigger tuple > (my problem). > > Any help or guidance is greatly appreciated! > > Thanks, > > Joe Conway >
Re: [SQL] RE: C function for use from PLpgSQL trigger
> You could send the column name directly into your c function. For example: > c_function_name(NEW.col1, NEW.col2, NEW.col3). Otherwise I am not sure how > to send NEW into a C function. You could try declaring NEW in your C > function as a tuple. Thanks for your reply. I was hoping that I could avoid hardcoding NEW.col1, etc, so that the function could be used for multiple relations. I've also tried to declare the input parameter to the function as a tuple, but PLpgSQL never gets that far -- it doesn't seem to support passing NEW as a parameter. Oh, well. I will probably just write all of my logic into a C function and skip PLpgSQL entirely. That's too bad because it would be far simpler (and preferrable IMHO) to write a generic trigger function in PLpgSQL and call C functions for only certain operations that PLpgSQL does not directly support. Joe
Re: [SQL] Oracle to PostgreSQL help: What is (+) in Oracle select?
> A select is done across two tables, however when joining the foreign > key, the right hand side of the equallity has (+) appended > > SELECT o.* from one o, two t where o.key = t.key(+) > > Does anyone know what this does and how I can reproduce the select in > PostgreSQL? Hi Chris, The (+) in Oracle is for an outer join. See http://www.postgresql.org/devel-corner/docs/postgres/sql-select.html , in the join-type description, left outer join. Outer joins are only available in PostgreSQL 7.1, which is currently in the late stages of beta testing. Hope this helps, Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Help
> I am a user postgresql. I want to update a table > automatically when we reach monthend. i.e i want to > update some table on 31 of every month automatically > without any user attention. I want to know how to do > this. If anyone knows how to do this please mail me. i > will be ever thankfull to him Probably the easiest way to do this is to write a script and run it from cron. For example, if your update query is in a file called $HOME/bin/monthend.sql: insert into mymonthendtable(f1, f2, f3) values(123,'03/31/2001',12345.67); your script (call it $HOME/bin/monthend.sh) might look like: #!/bin/sh psql -U postgres mydatabasename < $HOME/bin/monthend.sql then run (see "man 5 crontab" for more on cron) crontab -e and add an entry like # run at 2:15 AM on the 30th of every month 15 2 30 * * $HOME/bin/monthend.sh Hope this helps, Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Help
> Thanks for your valuable information. I tried the > cron. i typed >cron -e > and entereed into the input area. but i don't know how > to save the cron file. I pressed ctrl+z and came out > from cron. but i edit the cron file i found nothing on > it.(i.e using pico filename.) Please tell me some > description how to save the file in cron and to achive > this. I will be thankful to you. > Instead of "ctrl+z", press ":wq" (colon for command mode, w for write, q for quit). This assumes that vi is your default editor. Joe ---(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] Problems handling errors in PL/pgSQL
> However, not all types of errors are so trapped. The most problematic > un-trapped error is referential integrity: if an INSERT or UPDATE fails > because of a referential integrity violation, the PL/pgSQL function will > still see the statement as a success and not error out. Example: > I'm not sure if this is what you're looking for, but in 7.1 you can do something like: INSERT INTO bar(barpk,foopk) VALUES(barpkval,foopkval); GET DIAGNOSTICS rows = ROW_COUNT; -- do something based on rows -- See "24.2.5.4. Obtaining other results status" at http://postgresql.readysetnet.com/users-lounge/docs/7.1/postgres/plpgsql-des cription.html. Hope this helps, Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] How can we match a condition among 2 diff. tables?
> > Now, i need to get the details of all employees who did > > receive NONE of the salesorders. ie.. i wish to select the > > records of table 'employee' whose 'emp_id' are not > > there in table 'salesorder'. > > > > I need to accompolish in a single query! This should work: select e.emp_id from employee as e left join salesorder as s on e.emp_id = s.emp_id where s.emp_id is null; -- Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Big table - using wrong index - why?
> phones=# \d i_pl_pseq > Index "i_pl_pseq" > Attribute | Type > ---+-- > entity| character varying(3) > pseq | bigint > btree > > phones=# explain select * from phonelog where entity = '001' and pseq >= > 9120 and pseq <= 9123; > NOTICE: QUERY PLAN: > > Index Scan using i_pl_loadtimestamp on phonelog (cost=0.00..209247.39 > rows=607 width=137) > > EXPLAIN > > phones=# \d i_pl_loadtimestamp > Index "i_pl_loadtimestamp" > Attribute | Type > ---+-- > entity| character varying(3) > loaddate | date > loadtime | time > btree Just a guess, but what happens if you build i_pl_pseq(pseq, entity), i.e. reverse the key fields? Also, has the table been vacuum analyzed? -- Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Fuzzy matching?
> And the fuzzy_match should return True if the two phrases are no more
> than that number of characters different. Thus, we should get:
>
> fuzzy_match('Thornton','Tornton',1) = TRUE
> fuzzy_match('Thornton','Torntin',1) = FALSE
> fuzzy_match('Thornton','Torntin',2) = TRUE
>
> Unfortunately, I cannot think of a way to make this happen in a function
> without cycling through all the possible permutations of characters for
> both words or doing some character-by-character comparison with
> elaborate logic for placement. Either of these approaches would be very
> slow, and completely unsuitable for column comparisons on large tables.
>
> Can anyone suggest some shortcuts here? Perhaps using pl/perl or
> something similar?
Sounds like you want something along the lines of soundex or metaphone? I
don't see either function in PostgreSQL, but take a look at the PHP manual
to see examples: http://www.php.net/manual/en/function.soundex.php ,
http://www.php.net/manual/en/function.metaphone.php
I looked at the soundex function in the PHP source, and it looks like it
would be fairly easy to port to a Postgres C function. The algorithm itself
comes from Donald Knuth in "The Art Of Computer Programming, vol. 3: Sorting
And Searching", Addison-Wesley (1973), pp. 391-392.
HTH,
-- Joe
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Fuzzy matching?
> > Sounds like you want something along the lines of soundex or metaphone? I > > don't see either function in PostgreSQL, but take a look at the PHP manual > > to see examples: http://www.php.net/manual/en/function.soundex.php , > > http://www.php.net/manual/en/function.metaphone.php > > > > See /contrib/soundex. Sorry, missed that -- I only looked in the Documentation :( I guess it's not there because it is a contrib. FWIW, both Oracle and MSSQL have a built-in soundex function. In any case, metaphone is reportedly more accurate (at least for English words) than soundex, and levenshtein offers an entirely different and interesting approach. Any interest in having all three of these in the backend? -- Joe ---(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] Fuzzy matching?
> > > > Actually, this may even be closer to what you want: > > http://www.php.net/manual/en/function.levenshtein.php > > Hey, that's terrific! I didn't know that those programs existed > outside fo expensive proprietary software. > > Now, who can I talk into porting them (metaphone, levenstein) to > Postgres? Hey, GreatBridge folks? (this would be a significant value > enhancement for Postgres) > > -Josh I wouldn't mind doing it if the core team agrees. It will probably be a couple of weeks before I can get to it though -- not sure if that's soon enough to make it into 7.2. Should it be a contrib, or in the backend? -- Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Outer Join Syntax
Subject: [SQL] Outer Join Syntax > I'm doing a feasability study on porting our flagship product to Postgres > (from MS_SQL). I have run across a few snags, the largest of which is the > outer join syntax. MS has some nice syntactical sugar with the *=/=* > operators that Postgres dosen't seem to support. I am confused on how > to replicate the behavior however. We often link together many tables via See http://www.postgresql.org/idocs/index.php?queries.html You also might want to take a look at http://www.postgresql.org/idocs/index.php?explicit-joins.html HTH, --Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Name Alike Challenge
> The "Name Alike" PL/pgSQL function has been posted on Roberto Mello's > cookbook: > > http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?recipe_id=96 7 > > This function requires Joe Conway's port of the Metaphone and > Levenshtein functions to PostgreSQL, available from /contrib on CVS as > well as another site where /contrib stuff is available (anyone know > where this is?). Thanks again, Joe! I sent it as a tgz to Justin for techdocs.postgresql.org. Here's the link: http://techdocs.postgresql.org/source.php#ffuzzy Note that the lastest source in cvs has soundex included, which this tar file does not, but soundex is not needed for Josh's function and it was in previous contribs anyway. -- Joe ---(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]
> im new in postgresql (actually came from SQL Server) and i was trying a > script like this > > insert into table1(field1,field2) values (select field1, field2 from table > 2); > > i dont know if this is possible (inserting a set of entries via resultset > from a select stmt in one command). If anyone has any answers, or > workarounds pls do email me Well, that syntax doesn't work on SQL Server either. I think what you want is: insert into table1(field1,field2) select field1, field2 from table2; HTH, -- Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] How to use BYTEA type?
Christopher Sawtell wrote:
> Greetings folks,
>
> Please could a kind soul tell be how to extract data from a BYTEA type of
> field into a file, or better still tell me where I can find some doco?
Bruce has already written some minimal documentation which is in the 7.2
beta. I'm hoping to add to that prior to the 7.2 release
>
> This is the table:-
>
> create table fax_info ( id serial, comment text, image bytea) ;
>
> This appears to work ( no erorr messages ):-
>
> chris=# insert into fax_info ( comment, image ) values
> ( 'Faking it with a computer', byteain ('picture.pgm.gz'));
> INSERT 18772 1
>
> Is this correct?
No. You'll end up with literally the text 'picture.pgm.gz' in image.
What you need to do is escape 3 special characters in your application,
and then insert the escaped string directly. How exactly you do that
will vary depending on your application layer language. For example, in
PHP, you would do something like:
$image_name = "/tmp/myimage.jpg";
$fp = fopen($image_name,"r");
$image = fread($fp, filesize($image_name));
fclose($fp);
$esc_string = bytea_esc($image);
$sql = "insert into fax_info(comment,image) ";
$sql .= "values ('my image comment','$esc_string')";
$rs = pg_exec($conn, $sql);
where bytea_esc() is the function that escapes the special characters.
The three are ASCII 0, ASCII 39 (single quote), and ASCII 92 (single
backslash). In 7.2 there is a libpq function which can be called from
your C program to do the escaping, but for now, and in other programming
environments you may have to write your own. I have seen posts
indicating that the Perl DBI library for Postgres does have this
function already.
The escaping is a little tricky, and again varies depending on your
programming environment. When the string *reaches PostgreSQL*, it needs
to be escaped like this:
ASCII 0 ==> \\000
ASCII 39 ==>\' or \\047
ASCII 92 ==>or \\134
So an input string like 'helloworld' would wind up being
inserted like (where is a single 0 byte):
insert into foo(mybytea) values('hello\\000world');
As I said, the escaped string in your programming environment may need
to be different. In PHP for example, one set of backslashes is striped
by the PHP language parser (so \\ becomes \), so the actual function I
use looks like:
function bytea_esc($ct)
{
$buf = "";
for ($i = 0; $i < strlen($ct); $i++)
{
if (ord($ct[$i]) == 0)
$buf .= "000";
else if (ord($ct[$i]) == 39)
$buf .= "047";
else if (ord($ct[$i]) == 92)
$buf .= "134";
else
$buf .= $ct[$i];
}
return $buf;
}
>
> Now, how do I get my picture out again?
>
To get it back out, you query it out the same as any other field. The
catch is that all "non-printable" characters (which is quite a few more
than the three above) are returned to you escaped, i.e. ASCII 255 will
be returned as '\377'. So again you need to unescape the returned string
using your application programming language. In PHP there is a native
function which works great: stripcslashes(). So to complete the PHP example:
$sql = "select image from fax_info ";
$sql .= "where serial = 1";
$rs = pg_exec($conn, $sql);
$image = stripcslashes(pg_result($rs,0,0));
header("content-type: image/jpeg");
echo $image;
Hope this helps,
Joe
---(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] Search by longitude/latitude
> Hi all, > > I need to implement "Find all hospitals in a 5 mile radius". Say I have all > the coordinates on them stored in a table with the fields longitude and > latitude. Has anybody some sample code for that? > > Best regards, > Chris > Here's a plpgsql function that will do what you need. You might also look at the earthdistance code in contrib if you'd rather have a C function. HTH, Joe geodist.sql Description: Binary data ---(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] ROUND function ??
> select ROUND(0.5) ; returns 0 > select ROUND(1.5) ; returns 2; > select ROUND(2.5) ; returns 2; > select ROUND(3.5) ; returns 4;so on . > I'm sure you would have figured out what's happening !!! Why ?? > How do I get to approximate any number x.5 as x+1 ?? Looks like a bug to me: test=# select * from pg_proc where proname = 'round'; proname | proowner | prolang | proisinh | proistrusted | proiscachable | proisstrict | pronargs | proretset | prorettype | proargtypes | probyte_pct | properbyte_cpu | propercall_cpu | prooutin_ratio | prosrc | probin -+--+-+--+--+---+--- --+--+---++-+-+- ---++++- --- round |1 | 12 | f| t| t | t |1 | f |701 | 701 | 100 | 0 | 0 |100 | dround | - round |1 | 14 | f| t| t | t |1 | f | 1700 |1700 | 100 | 0 | 0 |100 | select round($1,0) | - round |1 | 12 | f| t| t | t |2 | f | 1700 | 1700 23 | 100 | 0 | 0 |100 | numeric_round | - (3 rows) test=# select round(2.5); round --- 2 (1 row) test=# select round(2.5,0); round --- 3 (1 row) test=# select round(2.5::numeric); round --- 3 (1 row) When doing "select round(2.5)" the 2.5 gets cast as a float and the "dround" function is used. When doing "select round(2.5,0)", or "select round(2.5::numeric)", the 2.5 gets cast as a numeric and the function "numeric_round" is used, producing a different result. It looks like "dround" simply calls the rint system function, so I'd guess the issue is really there (and maybe platform dependent?). I do recall at least one interpretation of rounding that calls for rounding a 5 to the even digit (ASTM), so the rint behavior may not be strictly speaking a bug -- but certainly having two different interpretations is. In any case, use "select round(2.5,0)" for now. Hope this helps, Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Please help me out on this insert error
Vernon Wu wrote:
> Command:
>
> Insert into profile (userid, haveChildren)values('id98', 'No');
>
> Error:
>
> ERROR: Relation 'profile' does not have attribute 'havaChildren'
^^^
From the error message, looks like you spelled haveChildren wrong.
HTH,
Joe
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] SQL Challenge: Skip Weekends
Josh Berkus wrote:
> Folks,
>
> Hey, I need to write a date calculation function that calculates the
> date after a number of *workdays* from a specific date. I pretty
> much have the "skip holidays" part nailed down, but I don't have a
> really good way to skip all weekends in the caluclation. Here's the
> ideas I've come up with:
How about this (a bit ugly, but I think it does what you want -- minus
the holidays, which you said you already have figured out):
create or replace function
get_future_work_day(timestamp, int)
returns timestamp as '
select
case when extract(dow from future_work_date) = 6
then future_work_date + ''2 days''
when extract(dow from future_work_date) = 0
then future_work_date + ''1 day''
else
future_work_date
end
from
(
select $1
+ (($2 / 5)::text || '' weeks'')
+ (($2 % 5)::text || '' days'')
as future_work_date
) as t1
' language sql;
CREATE
testslv=# select get_future_work_day('2002-06-20',27);
get_future_work_day
2002-07-29 00:00:00-07
(1 row)
HTH,
Joe
---(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] Returning rows from functions
David Durst wrote: > I was wondering if there was a way of returning a complete row from a > function, in reading the documentation of CREATE FUNCTION. I was under the > impression that you could return a row by using setof, but this does not > seem to be true. > Can anyone help? The short answer is "yes, but..."; see the thread at: http://archives.postgresql.org/pgsql-interfaces/2002-06/msg00042.php for a recent discussion about this. HTH, Joe ---(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] contrib/dblink suggestion
Bhuvan A wrote: > I am using postgresql 7.2.1. > I badly require to interconnect between databases. contrib/dblink seems to > be handy and ofcourse it well suits my requirement. But while browsing > across, i heard that it is not advicable to use it. So i wish to know > someone's experience in using dblink and how handy it is. > > Will contrib/dblink be available with future postgresql releases? Valuable > suggestions are very welcome. > I've heard of at least two people who seem to be using dblink fairly heavily without problems. One recently reported something like 500 million records transferred without error. I use it myself, but not in what I'd call heavy use. If you are aware of any specific problems, please point me to them, and I'll fix them before the next release. Thanks, Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] VACUUM not doing its job?
Kristian Eide wrote: > This is not good as the database is in use 24/7, and without the indexes > everything comes to a screeching halt. This means I probably will have to > stop the programs using the database for the time it takes to re-create the > indexes; this is better than having to dump/restore everything however :) Try REINDEX. I don't think that precludes (at least) read access. http://www.postgresql.org/idocs/index.php?sql-reindex.html You should also search the archives for threads on free space maps. You most likely need to increase yours. In particular, see: http://archives.postgresql.org/pgsql-general/2002-07/msg00972.php HTH, Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] VACUUM not doing its job?
Kristian Eide wrote: > Thanks, very helpful, although there does not seem to be much description of > what the two free space map options in postgresql.conf actually do. Doing a > VACUUM ANALYZE VERBOSE on my largest table gives me: > > NOTICE: Pages 43681: Changed 208, Empty 0; Tup 1921387: Vac 61669, Keep 0, > UnUsed 1362341. > > I will try to increase the 'max_fsm_pages' option from 1 to 50 and > see if that helps. > Note that you'll need to do a vacuum full *first* to recover the lost space, since the free space map is populated as the tuples are actually freed, I believe. After that you can adjust 'max_fsm_pages' and your vacuum frequency to achieve an equilibrium. Joe ---(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] Hardware performance for large updates
Josh Berkus wrote: > The problem: The update series (done as a function) takes 10-15 > minutes. During this time, the CPU is never more than 31% busy, only > 256mb of 512 is in use, and the disk channel is only 25% - 50% > saturated.As such, is seems like we could run things faster. > > What does everybody suggest tweaking? > I think we'd need more information to be of any help -- schema, functions, explain output, etc. I do think you probably could increase Shared Buffers, as 256 is pretty small. There's been a lot of debate over the best setting. The usual guidance is start at 25% of physical RAM (16384 == 128MB if you have 512MB RAM), then tweak to optimize performance for your application and hardware. You might also bump sort mem up a bit (maybe to 2048). Again, I would test using my app and hardware to get the best value. Are you on a Linux server -- if so I found that fdatasync works better than (the default) fsync for wal_sync_method. HTH, Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Hardware performance for large updates
Josh Berkus wrote: > Particularly, the difficulty is that this application gets many small > requests during the day (100 simultaneous uses) and shares a server > with Apache. So I have to be concerned about how much memory each > connection soaks up, during the day. At night, the maintainence tasks > run a few, really massive procedures. > > So I should probably restart Postgres with different settings at night, > hey? Actually, if you can afford the twice daily changes, it sounds like a great idea. I think you can get new conf settings to take by sending a SIGHUP to the postmaster, so you don't even really need any downtime to do it. Yup, here it is: http://www.postgresql.org/idocs/index.php?runtime-config.html >>I do think you probably could increase Shared Buffers, as 256 is >>pretty small. There's been a lot of debate over the best setting. The >>usual guidance is start at 25% of physical RAM (16384 == 128MB if you >>have 512MB RAM), then tweak to optimize performance for your >>application and hardware. > > > Hmmm... how big is a shared buffer, anyway? I'm having trouble > finding actual numbers in the docs. By default it is 8K. It's mentioned here: http://www.postgresql.org/idocs/index.php?kernel-resources.html So, as I mentioned above, Shared Buffers of 16384 == 128MB if you have a default 8K block size. >>Are you on a Linux server -- if so I found that >>fdatasync works better than (the default) fsync for wal_sync_method. > > Yes, I am. Any particular reason why fdatasync works better? I can't remember the technical reason (although I've seen one on the list before), but I have determined it empirically true, at least for my setup. Ahh, here we go: http://archives.postgresql.org/pgsql-hackers/1998-04/msg00326.php Joe ---(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] writing a function to mimic mysql last_insert_id
Beth Gatewood wrote:
> well, I know that I will have only a single sequence that will generate the
> primary key per table. So basically, this type of function, for me needs
> only to return the value of the primary key.
>
> I believe I mentioned in one of my posts the motivation behind not wanting
> to use currval()...which was to trying to avoid having the developers make a
> lot of sql revisions to their application.
Maybe you could use the *same* sequence for the primary key of all the tables,
say "my_global_seq" (it is bigint as of 7.2 I think), and then wrap a
last_insert_id() (or whatever it is called) function around a call to
currval('my_global_seq').
HTH,
Joe
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] [GENERAL] Latitude / Longitude
Tim Perdue wrote:
> Hi Folks -
>
> I'm working on a global weather database for a client and have hit an issue
> which I'm sure has been solved before. Unfortunately, the site search doesn't
> pull up anything.
>
> Essentially, I've got two tables, one with city/county/lat/long and another
> with lat/long/weather data.
>
> None of the lat/longs in the two tables match up directly, so I can't do a
> simple join of the two tables. I need to join on closest proximity on the
> lat/long fields.
>
> Any suggestions? It seems to me this will be pretty expensive on CPU resources
> unless there's a really elegant trick uncovered.
I see you've gotten some other help, but in case you're interested, I'll give
you another alternative. Here's a plpgsql function I wrote a while ago based
on the Haversine formula:
CREATE FUNCTION "zipdist" (float8,float8,float8,float8 ) RETURNS float8 AS '
DECLARE
lat1 ALIAS FOR $1;
lon1 ALIAS FOR $2;
lat2 ALIAS FOR $3;
lon2 ALIAS FOR $4;
dist float8;
BEGIN
dist := 0.621 * 6371.2 * 2 *
atan2( sqrt(abs(0 + pow(sin(radians(lat2)/2 -
radians(lat1)/2),2) + cos(radians(lat1)) * cos(radians(lat2)) *
pow(sin(radians(lon2)/2 - radians(lon1)/2),2))),sqrt(abs(1 -
pow(sin(radians(lat2)/2 - radians(lat1)/2),2) + cos(radians(lat1)) *
cos(radians(lat2)) * pow(sin(radians(lon2)/2 - radians(lon1)/2),2;
return dist;
END;
' LANGUAGE 'plpgsql';
I used the following PHP code to start looking for a match in a small circle,
and then expand it if no matches were found:
$dist = INIT_DIST;
$cnt = 0;
$cntr = 0;
do {
if ((! $zip == "") && (! $dist <= 0)) {
$sql = get_zip_sql($lon1d,$lat1d,$dist,$numtoshow);
$rs = connexec($conn,$sql);
$rsf = rsfetchrs($rs);
$dist *= 2;
$cntr++;
} else {
$cntr = 10;
}
} while (count($rsf) < $numadvisorstoshow && $cntr < 10);
Hopefully you get the idea.
As was suggested, you can narrow the results using a box to make the query
perform better, and then sort by distance to get the closest alternative.
Here's the related part of get_zip_sql():
function get_zip_sql($lon1d,$lat1d,$dist,$numtoshow)
{
$sql = "
SELECT DISTINCT
FROM tbl_a AS a
,tbl_d AS d
,tbl_a_zipcodes AS az
,tbl_zipcodes as z
WHERE
abs(z.lat - $lat1d) * 60 * 1.15078 <= $dist
and abs(z.long - $lon1d) * 60 * 1.15078 <= $dist
and zipdist($lat1d,$lon1d,lat,long) <= $dist
and z.zip = az.zipcode
ORDER BY
LIMIT $numtoshow;
";
return $sql;
}
The "X * 60 * 1.15078" converts differences in degrees lat/long into rough
distances in miles. The zipdist function returns a more-or-less exact distance
using the Haversine formula.
Hope this helps. Let me know if you want/need more explanation of any of this.
Joe
---(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] function return multiply rows
Jeroen Olthof wrote: > What is going wrong here? > > An example of what I'm trying to do. > > vw_teams is a view but same problem when trying it on a single table > CREATE FUNCTION test() RETURNS SETOF vw_teams AS 'select * from vw_teams;' > LANGUAGE 'sql'; > > SELECT test(); > > results in > >test > --- > 137789256 > 137789256 > (2 rows) The capability to return composite types (multi-column rows) is limited in <= PostgreSQL 7.2.x. What you are seeing are pointers to the rows, not the rows themselves. Version 7.3, in beta testing now, will do what you are looking for. If you can, please give it a try. See: http://developer.postgresql.org/docs/postgres/xfunc-tablefunctions.html for more info and examples. HTH, Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] split function for pl/pgsql
Frederic Logier wrote: > Great ! have you some example for call a pl/perl function from a > pl/pgsql function ? I don't use PL/Perl, but I think you should be able to do: SELECT INTO var my_plperl_func(text_to_split); from within your PL/pgSQL function. > And could i use an int array in pl/pgsql returned by the pl/perl > function ? I don't know if it is possible to construct a PostgreSQL array in pl/perl, but I would imagine that should work. Any pl/perl users out there? Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Stored Procedures
[EMAIL PROTECTED] wrote: > Ok, if this does not apply to versions prior to 7.3beta > then what do I need to do if I am running 7.2.1? When I > try to use the SETOF to retrun a row set, I only get > one column. First, prior to 7.3 there is no SCHEMA support in Postgres. Everything lives in essentially one and the same schema. In 7.2.x and before, returning a composite type (i.e. multiple columns) gives you back one column of pointers (large integer values) to the actual row of data. You can access the individual columns, but it's ugly: test=# CREATE TABLE foo (fooid int, foosubid int, fooname text); CREATE test=# INSERT INTO foo VALUES(1,1,'Joe'); INSERT 304822 1 test=# CREATE FUNCTION getfoo(int) RETURNS foo AS ' test'# SELECT * FROM foo WHERE fooid = $1; test'# ' LANGUAGE SQL; CREATE test=# select fooid(getfoo(1)), foosubid(getfoo(1)), fooname(getfoo(1)); fooid | foosubid | fooname ---+--+- 1 |1 | Joe (1 row) Joe ---(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] split function for pl/pgsql
Frederic Logier wrote:
> hi,
>
> i'm looking for a split function, like perl or php.
> I need doing a pl/pgsql function with a split return an array.
> I must do some loop with this array for mass inserting.
>
> I think of doing it with pl / perl but I need to do insert and I don't
> find example with pl / perl and sql.
There is no split function built in to PostgreSQL currently. You could write
it yourself in PL/Perl and use it in the PL/pgSQL function.
In 7.3 (currently in beta) there is a split_part() function which returns just
one element. I will most likely write a split function for 7.4 to return an
array, similar to perl and php. In 7.3, the following will do what you want (I
think):
CREATE OR REPLACE FUNCTION split(text, text)
RETURNS text[] AS '
DECLARE
i int := 0;
word text;
result text := ''{'';
result_arr text[];
BEGIN
LOOP
i := i + 1;
SELECT INTO word split_part($1, $2, i);
IF word = THEN
EXIT;
END IF;
IF i > 1 THEN
result := result || '',"'' || word || ''"'';
ELSE
result := result || ''"'' || word || ''"'';
END IF;
END LOOP;
result := result || ''}'';
result_arr := result;
RETURN result_arr;
END
' LANGUAGE 'plpgsql';
test=# select split('a,b,c',',');
split
-
{a,b,c}
(1 row)
test=# select a[1] from (select split('a,b,c',',') as a) as t;
a
---
a
(1 row)
HTH,
Joe
---(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] Stored Procedures
david williams wrote: > Also, > > the table definition MUST be in the Public Schema. I use my own schema > names but in order for the table to be found by the function it ( the > table ) must be in the public schema. Although it can be empty. (Note: this discussion does not apply to PostgreSQL releases prior to 7.3 beta) Not true. You need to be sure the schema the table is in is in your search path, or you need to fully qualify the table reference. See below for an example: -- create a new schema CREATE SCHEMA s1; CREATE SCHEMA -- change to the new schema SET search_path='s1','$user','public'; SET select current_schema(); current_schema s1 (1 row) -- create the table CREATE TABLE foo (fooid int, foosubid int, fooname text); CREATE TABLE INSERT INTO foo VALUES(1,1,'Joe'); INSERT 794076 1 -- change back to public schema, but leave s1 in the search path SET search_path='$user','public','s1'; SET select current_schema(); current_schema public (1 row) \dt List of relations Schema | Name | Type | Owner +--+---+-- s1 | foo | table | postgres (1 row) CREATE FUNCTION getfoo(int) RETURNS foo AS ' SELECT * FROM foo WHERE fooid = $1; ' LANGUAGE SQL; CREATE FUNCTION \df getfoo List of functions Result data type | Schema | Name | Argument data types --+++- foo | public | getfoo | integer (1 row) -- this will work SELECT *, upper(fooname) FROM getfoo(1) AS t1; fooid | foosubid | fooname | upper ---+--+-+--- 1 |1 | Joe | JOE (1 row) -- now try again with table name qualified in the function DROP FUNCTION getfoo(int); DROP FUNCTION -- remove s1 from the search path SET search_path='$user','public'; SET select current_schema(); current_schema public (1 row) \dt No relations found. CREATE FUNCTION getfoo(int) RETURNS s1.foo AS ' SELECT * FROM s1.foo WHERE fooid = $1; ' LANGUAGE SQL; CREATE FUNCTION \df getfoo List of functions Result data type | Schema | Name | Argument data types --+++- s1.foo | public | getfoo | integer (1 row) -- this will work SELECT *, upper(fooname) FROM getfoo(1) AS t1; fooid | foosubid | fooname | upper ---+--+-+--- 1 |1 | Joe | JOE (1 row) HTH, Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] rows in order
Camila Rocha wrote:
> Is there a way to put in order the rows in a table? the problem is that i w=
> ant to keep a "tree" in the db, but the leaves must be ordered...
> does someone have an idea?
If you don't mind trying 7.3 beta, there is a function called connectby() in
contrib/tablefunc. It works like this:
CREATE TABLE connectby_tree(keyid text, parent_keyid text);
INSERT INTO connectby_tree VALUES('row1',NULL);
INSERT INTO connectby_tree VALUES('row2','row1');
INSERT INTO connectby_tree VALUES('row3','row1');
INSERT INTO connectby_tree VALUES('row4','row2');
INSERT INTO connectby_tree VALUES('row5','row2');
INSERT INTO connectby_tree VALUES('row6','row4');
INSERT INTO connectby_tree VALUES('row7','row3');
INSERT INTO connectby_tree VALUES('row8','row6');
INSERT INTO connectby_tree VALUES('row9','row5');
SELECT * FROM
connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '~')
AS t(keyid text, parent_keyid text, level int, branch text);
keyid | parent_keyid | level | branch
---+--+---+-
row2 | | 0 | row2
row4 | row2 | 1 | row2~row4
row6 | row4 | 2 | row2~row4~row6
row8 | row6 | 3 | row2~row4~row6~row8
row5 | row2 | 1 | row2~row5
row9 | row5 | 2 | row2~row5~row9
(6 rows)
This allows completely dynamically generated trees.
There is also a contrib/ltree, which I believe creates a persistent structure
for the tree information, and gives you tools to manipulate it (but I have
never used it, so my discription may not be completely accurate).
HTH,
Joe
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Suggestion: md5/crypt functions in sql
Aasmund Midttun Godal wrote: > It would be very usefull to have these in sql, so that it is even easier to create >tables with encrypted passwords. > See contrib/pgcrypto Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Get A Tree from a table
Scott Yaung wrote:
> I like to do something like this:(build a tree from relationship)
[snip]
> How can i make it by sql , and sql functions
> Thanks lot and regards.
There have been quite a few discussions of this topic in the past, so I would
suggest you search through the archives.
In 7.3 (currently in beta) you can use contrib/ltree or contrib/tablefunc.
Here's an example of using the connectby() function from contrib/tablefunc:
CREATE TABLE nodes(parentid varchar(20), parenttype varchar(20), childid
varchar(20), childtype varchar(20));
INSERT INTO nodes values('A1', 'A', 'B1', 'B');
INSERT INTO nodes values('A2', 'A', 'B2', 'B');
INSERT INTO nodes values('A1', 'A', 'B3', 'B');
INSERT INTO nodes values('B1', 'B', 'C1', 'C');
INSERT INTO nodes values('B1', 'B', 'C2', 'C');
INSERT INTO nodes values('C1', 'C', 'D1', 'D');
INSERT INTO nodes values('A1', 'A', 'B4', 'B');
INSERT INTO nodes values('B1', 'B', 'C5', 'C');
test=# SELECT * FROM connectby('nodes','childid','parentid','A1',0,'~') AS
t(childid varchar, parentid varchar, level int, branch text);
childid | parentid | level | branch
-+--+---+-
A1 | | 0 | A1
B1 | A1 | 1 | A1~B1
C1 | B1 | 2 | A1~B1~C1
D1 | C1 | 3 | A1~B1~C1~D1
C2 | B1 | 2 | A1~B1~C2
C5 | B1 | 2 | A1~B1~C5
B3 | A1 | 1 | A1~B3
B4 | A1 | 1 | A1~B4
(8 rows)
test=# SELECT * FROM connectby('nodes','childid','parentid','B1',0,'~') AS
t(childid varchar, parentid varchar, level int, branch text);
childid | parentid | level | branch
-+--+---+--
B1 | | 0 | B1
C1 | B1 | 1 | B1~C1
D1 | C1 | 2 | B1~C1~D1
C2 | B1 | 1 | B1~C2
C5 | B1 | 1 | B1~C5
(5 rows)
HTH,
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Multiple Databases
Josh Berkus wrote: Is it possible to create a view using tables from two different postgresql servers? No. It isn't possible with plain old SQL, but it is possible (albeit ugly) using contrib/dblink in PostgreSQL 7.2.x. See README.dblink for documentation and examples. In PostgreSQL 7.3 (in beta testing now) the syntax and usability of contrib/dblink is greatly improved. Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Generating a cross tab (pivot table)
Richard Huxton wrote: On Thursday 07 Nov 2002 11:47 am, Christoph Haller wrote: Regarding to Terry's request on multiple aggregates and Shahbaz's request for generating a cross tab ( pivot table ) in September, I've found an excellent example on a german inet page http://www.itrain.de/ I've translated it and think it's useful for many who subscribed (I hope so, maybe it was told before, but I couldn't find anything about this topic in the techdecs). Very useful. Also note there are some examples of how to produce crosstab results in the table-functions contrib directory in 7.3 Just to amplify a bit, in contrib/tablefunc there is a family of functions called crosstabN(), where N is 2, 3 and 4. These are meant as examples -- you could, for example, create a function crosstab5() if you need it. There is also a function called crosstab(), which returns type RECORD and thus requires the column definition to be specified in the query. See contrib/tablefunc/README.tablefunc for more details and examples. These were done as relatively crude examples and therefore have some limitations which may or may not be a problem for you. If people find the functions useful and provide suggestions for improvement in functionality I'll try to upgrade them for 7.4. Thanks, Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Proposal of hierarchical queries, a la Oracle
Josh Berkus wrote: Evgren, I want to propose the patch for adding the hierarchical queries posibility. It allows to construct queries a la Oracle for ex: SELECT a,b FROM t CONNECT BY a PRIOR b START WITH cond; You'll be thrilled to know that this has already been implemented in PostgreSQL 7.3, as a /contrib module, by Joe Conway. Download 7.3b5 now if you can't wait; Joe would like more people to test his module, anyway. I have it on my personal TODO to approach this for 7.4, but I'll be happy to focus on other things if you'd like to take this on. The connectby() function in contrib/tablefunc could be a good starting point for you. Joe ---(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] execute a query in an other db
LR wrote: Thank you for your answer. Do you know the name of this package ? Thx Lilian see contrib/dblink. I'd highly recommend using 7.3 (finishing beta, soon to be a release candidate) if you can. It is much better than the one in 7.2 and the syntax has changed some. Joe ---(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] Proposal of hierarchical queries, a la Oracle
Evgen Potemkin wrote: Joe, i've made it already,as send first 'Proposal ...' message, but found a small bug. within nearest days i'll fix it, and post the patch to pgsql-patches. Please note that there was no patch attached to your initial proposal (assuming this is the message you are referring to): http://archives.postgresql.org/pgsql-sql/2002-11/msg00226.php -- I think that's why people proposed so many alternatives to you. In any case, we'll look forward to your patch! Regards, Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Trees: maintaining pathnames
Dan Langille wrote:
Given that I'm considering adding a new field path_name to the tree,
I can't see the ltree package will give me anything more than I can
get from like. My main reason for adding path_name was doing queries
such as:
select * from tree where path_name like '/path/to/parent/%'
which will return me all the descendants of a give node (in this case
'/path/to/parent/'.[2]
FWIW, you could also do this with connectby() in contrib/tablefunc (new in
7.3; see the README for syntax details):
test=# SELECT t.* FROM connectby('tree', 'id', 'parent_id', '1', 0, '~') AS
c(id int, parent_id int, level int, branch text), tree t WHERE t.id = c.id;
id | parent_id |name
+---+
1 | | Top
2 | 1 | Science
3 | 2 | Astronomy
4 | 3 | Astrophysics
5 | 3 | Cosmology
6 | 1 | Hobbies
7 | 6 | Amateurs_Astronomy
8 | 1 | Collections
9 | 8 | Pictures
10 | 9 | Astronomy
11 |10 | Stars
12 |10 | Galaxies
13 |10 | Astronauts
(13 rows)
test=# SELECT t.* FROM connectby('tree', 'id', 'parent_id', '6', 0, '~') AS
c(id int, parent_id int, level int, branch text), tree t WHERE t.id = c.id;
id | parent_id |name
+---+
6 | 1 | Hobbies
7 | 6 | Amateurs_Astronomy
(2 rows)
test=# SELECT t.* FROM connectby('tree', 'id', 'parent_id', '8', 0, '~') AS
c(id int, parent_id int, level int, branch text), tree t WHERE t.id = c.id;
id | parent_id |name
+---+-
8 | 1 | Collections
9 | 8 | Pictures
10 | 9 | Astronomy
11 |10 | Stars
12 |10 | Galaxies
13 |10 | Astronauts
You could also do:
CREATE OR REPLACE FUNCTION node_id(text) returns int as 'select id from tree
where name = $1' language 'sql';
test=# SELECT t.* FROM connectby('tree', 'id', 'parent_id',
node_id('Science'), 0) AS c(id int, parent_id int, level int), tree t WHERE
t.id = c.id;
id | parent_id | name
+---+--
2 | 1 | Science
3 | 2 | Astronomy
4 | 3 | Astrophysics
5 | 3 | Cosmology
(4 rows)
I have discussed [offlist] the option of using a secondary table to
store the pathname (i.e. a cach table) which would be updated using a
loop in the tigger instead of using cascading triggers. I would
prefer to keep the pathname in the same table.
In my application, I have about 120,000 nodes in the tree. I am
using PL/pgSQL quite a lot. Perhaps moving the triggers to C at a
later date may provide a speed increase if the tree expands
considerably.
I've tested connectby() on a table with about 220,000 nodes. It is pretty fast
(about 1 sec to return a branch with 3500 nodes), and is entirely dynamic
(requires no triggers).
Joe
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] SQL syntax for concating values in different rows together
Elizabeth O'Neill's Office Mail wrote: I have two tables in my database a complaint table and a resolution table. One complaint may have several resolutions. I am trying to build a report that will give me the complaint details and all the resolution descriptions for a complaint in one text area/row (concated together). At the moment it is repeating the complaint details for each resolution. As someone else mentioned I think, you can use a plpgsql function. Here is a contrived example: create table complaint(cid int, descr text); insert into complaint values(1,'my #1 complaint'); insert into complaint values(2,'my #2 complaint'); create table resolution(rid int, cid int, res text); insert into resolution values (1,1,'fixed it'); insert into resolution values (2,1,'really fixed it!'); insert into resolution values (3,2,'pbkbac again'); create or replace function my_concat(int) returns text as' declare ret text; comp text; rec record; cntr int = 0; begin select into comp descr from complaint where cid = $1; ret := ''Comp = '' || comp; for rec in select res from resolution where cid = $1 loop cntr := cntr + 1; ret := ret || '': Res# '' || cntr::text || '' = '' || rec.res; end loop; return ret; end; ' language 'plpgsql'; regression=# select my_concat(cid) from complaint; my_concat -- Comp = my #1 complaint: Res# 1 = fixed it: Res# 2 = really fixed it! Comp = my #2 complaint: Res# 1 = pbkbac again (2 rows) In the past I think I remember someone trying to solve this kind of problem with a custom aggregate, but the plpgsql approach is probably simpler. HTH, Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] DB2 to Postgresql conversion help.
John Pauley wrote: All, Any suggestions on a Postgresql equivalent to the following DB2 sql query: SELECT * FROM (SELECT ID,NAME,CITY,STATE,PROGRAM,CUST_NAME,CUST_ID,ROWNUMBER() OVER (ORDER BY ID ASC) AS RN FROM MERCHANT_BROWSER_VIEW WHERE CUST_ID = 'A51B8CA2' ) AS RESULT WHERE RN BETWEEN 1 AND 20 I'm not familiar with DB2, but I'd guess something like: SELECT ID,NAME,CITY,STATE,PROGRAM,CUST_NAME,CUST_ID FROM MERCHANT_BROWSER_VIEW WHERE CUST_ID = 'A51B8CA2' ORDER BY ID LIMIT 20; HTH, Joe ---(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] [GENERAL] 7.3.1 function problem: ERROR: cache lookup failed
Achilleus Mantzios wrote: On Tue, 7 Jan 2003, Tom Lane wrote: Does your function know about filling in the elemtype field that was recently added to struct ArrayType? She has no clue :) Any pointers would be great. See construct_array() in src/backend/utils/adt/arrayfuncs.c. HTH, Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Could someone help me fix my array_list function?
Guy Fraser wrote:
This is what I want to do:
select attribute,array_list(values,1,sizeof(values)) as value from av_list;
Turn :
attr6 | {val3,val7,val4,val5}
Into :
attr6 | val3
attr6 | val7
attr6 | val4
attr6 | val5
You didn't mention the version of PostgreSQL. If you're using < 7.3, good luck
;-). If you are using 7.3, the following works:
DROP TABLE mail_aliases;
CREATE TABLE mail_aliases(
a_mailbox text,
a_destination text[]
);
INSERT INTO mail_aliases VALUES ('alias1', '{dest1}');
INSERT INTO mail_aliases VALUES ('alias2', '{dest2,dest1}');
INSERT INTO mail_aliases VALUES ('alias3', '{dest3,dest4}');
INSERT INTO mail_aliases VALUES ('alias4', '{dest3,dest4,dest5}');
INSERT INTO mail_aliases VALUES ('alias5', '{dest6,dest7}');
INSERT INTO mail_aliases VALUES ('alias6', '{dest3,dest7,dest4,dest5}');
CREATE TYPE mail_aliases_list_type AS (a_mailbox text, a_destination_el text);
CREATE OR REPLACE FUNCTION mail_aliases_list() RETURNS SETOF
mail_aliases_list_type AS '
DECLARE
rec record;
retrec record;
low int;
high int;
BEGIN
FOR rec IN SELECT a_mailbox, a_destination FROM mail_aliases LOOP
SELECT INTO low
replace(split_part(array_dims(rec.a_destination),'':'',1),''['',)::int;
SELECT INTO high
replace(split_part(array_dims(rec.a_destination),'':'',2),'']'',)::int;
FOR i IN low..high LOOP
SELECT INTO retrec rec.a_mailbox, rec.a_destination[i];
RETURN NEXT retrec;
END LOOP;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';
regression=# SELECT a_mailbox, a_destination_el FROM mail_aliases_list();
a_mailbox | a_destination_el
---+--
alias1| dest1
alias2| dest2
alias2| dest1
alias3| dest3
alias3| dest4
alias4| dest3
alias4| dest4
alias4| dest5
alias5| dest6
alias5| dest7
alias6| dest3
alias6| dest7
alias6| dest4
alias6| dest5
(14 rows)
HTH,
Joe
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Could someone help me fix my array_list function?
Michiel Lange wrote:
Would the same work for pg_user and pg_group?
See:
http://www.brasileiro.net:8080/postgres/cookbook/view-one-recipe.adp?recipe_id=11378
With these groups:
regression=# select * from pg_group;
groname | grosysid |grolist
-+--+---
grp1| 100 | {100,101,102}
grp2| 101 | {100,102}
(2 rows)
Output looks like:
regression=# select * from groupview;
grosysid | groname | usesysid | usename
--+-+--+-
100 | grp1| 100 | user1
100 | grp1| 101 | user2
100 | grp1| 102 | user3
101 | grp2| 100 | user1
101 | grp2| 102 | user3
(5 rows)
Joe
---(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] Postgres MD5 Function
Larry Rosenman wrote:
--On Friday, January 31, 2003 01:34:42 -0800 David Durst
<[EMAIL PROTECTED]> wrote:
Does there exsist a MD5 Function I can call???
look at /contrib/pgcrypto in the source distribution.
Also worth noting is that 7.4 will have (and cvs HEAD has) a builtin md5 function:
regression=# select md5('Joe');
md5
--
3a368818b7341d48660e8dd6c5a77dbe
(1 row)
HTH,
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Postgres MD5 Function
David Durst wrote: Is there anywhere I can get these in binary? Or is my only option to compile Postgres from source?? Depends on the type of binary. E.g. there is a postgresql-contrib-7.3.1-1PGDG.i386.rpm binary available here: ftp://ftp.us.postgresql.org/binary/v7.3.1/RPMS/redhat-7.3/ Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] plpgsql + dblink() question
Frankie wrote:
The case is when I call a function (from Server 1 at psql prompt) that will
call dblink to do some operation
on another server(it will take certain time), i.e. Server 2, and meanwhile I
just unplug the network cable to Server 2.
The consequence is that the function will never return except I plug the
cable into it again, moreover I cannot even cancel
the query and stop the postgresql server (have to 'kill -9'.)
dblink just uses libpq to handle the communication, so you can use the
connect_timeout connection parameter. It defaults to infinite if not
explicitly set. Something like this should set a 5 second timeout:
select * into tmp from dblink(''host=linux dbname=twins connect_timeout=5'',
''select mysleep();'') as (retval text);
See:
http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/libpq-connect.html
HTH,
Joe
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] generic return for functions
Danny Su wrote:
I am currently converting everything from SQL Server to PostgreSQL.
This is for an application that is going to support Oracle, SQL Server
and PostgreSQL at the same time. I have done a lot of the conversion
already but I am stuck on functions that returns parts of views or tables.
[...snip description of SQL Server 2000 table-valued UDFs...]
I know "returns setof record" and "returns setof my_own_datatype" work,
but I would like to know if there is a better way? Something that's like
"returns setof record" but without having to define all the columns when
I call the function? {i.e. without the need to do: select * from
myfunction() as (column1 type1, column2...);}
If there is such method? It will allow me to maintain the application
much easier and makes the conversion task much easier :)
Sorry -- the answer is no. But I don't think Oracle will support what
you want either.
Joe
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] "Join" on delimeter aggregate query
Michael A Nachbaur wrote: Source Target [EMAIL PROTECTED] [EMAIL PROTECTED],[EMAIL PROTECTED], It isn't clear from your description what you want (to me, anyway), but I'd guess something like this: regression=# select * from src2tgt; source| target -+-- [EMAIL PROTECTED] | [EMAIL PROTECTED] [EMAIL PROTECTED] | [EMAIL PROTECTED] [EMAIL PROTECTED] | [EMAIL PROTECTED] [EMAIL PROTECTED] | [EMAIL PROTECTED] (4 rows) create or replace function mylist(text,text) returns text as ' declare result text; begin if $1 = then result := $2; else result := $1 || '','' || $2; end if; return result; end; ' language 'plpgsql'; create aggregate tgtlist ( BASETYPE = text, SFUNC = mylist, STYPE = text, INITCOND = '' ); regression=# select source, tgtlist(target) from src2tgt group by source; source| tgtlist -+--- [EMAIL PROTECTED] | [EMAIL PROTECTED],[EMAIL PROTECTED] [EMAIL PROTECTED] | [EMAIL PROTECTED],[EMAIL PROTECTED] (2 rows) HTH, Joe ---(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] Using a RETURN NEXT
Mr Weinbach, Larry wrote: But at execution time I am getting thi error : WARNING: Error occurred while executing PL/pgSQL function word_case WARNING: line 5 at return next ERROR: Set-valued function called in context that cannot accept a set You didn't show the execution time SQL statement, but the error you are getting usually indicates you did something like: SELECT word_case(); but you should have done: (define the function to return setof record) SELECT * FROM word_case() AS (message text); or CREATE TYPE word_case_type AS (message text); (define the function to return setof word_case_type) SELECT * FROM word_case(); HTH, Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Catching DML exceptions in PL/pgSQL
Radu-Adrian Popescu wrote: . begin _res.code:=1; select id into iid from log where id=_id; if not found then begin _res.msg:=''insert''; *insert into log (log, data) values (_log, _data); if not found* then begin _res.msg:=_res.msg || '' error''; _res.code:=-1; end; end if; end; else begin . The thing is if _data (parameter) is null and table has a (data <> null) check, the insert would fail and abort the function before my "if not found" test. You could test for _data is null, and if so check attnotnull in pg_attribute. E.g. something like: declare iattnotnull bool [...] begin if _data is null then select into iattnotnull attnotnull from pg_catalog.pg_attribute where attrelid = 'log'::regclass and attname = 'data'; if iattnotnull then _res.code := -1; [...] Is there anything I can do to make sure the function always returns _res ? Something along the lines of Oracle's exception handling, or the @@error trick in mssql ? There is currently no way to "catch" the exception in PL/pgSQL, but maybe the above would work for you. HTH, Joe ---(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] LEAST and GREATEST functions?
Greg Stark wrote:
SELECT greatest(a,b) FROM bar
would return one tuple for every record in the table with a single value
representing the greater of bar.a and bar.b.
You could define your own functions to do this but it would be tiresome to
define one for every datatype.
In 7.4devel (just starting beta) you can do this:
create or replace function greatest(anyelement, anyelement) returns
anyelement as 'select case when $1 > $2 then $1 else $2 end' language 'sql';
regression=# select greatest(1, 2);
greatest
--
2
(1 row)
regression=# select greatest('b'::text, 'a');
greatest
--
b
(1 row)
regression=# select greatest(now(), 'yesterday');
greatest
---
2003-07-01 13:21:56.506106-07
(1 row)
The cast to text is needed because 'a' and 'b' are really typed as
unknown, and with polymorphic functions, you need a well defined data type.
So if you had a table:
create table g(f1 text, f2 text);
insert into g values ('a','b');
insert into g values ('c','b');
regression=# select greatest(f1, f2) from g;
greatest
--
b
c
(2 rows)
Doesn't help for 7.3.x, but at least you know help is on the way ;-)
Of course, you could always just use the case statement.
Joe
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] LEAST and GREATEST functions?
Joe Conway wrote: In 7.4devel (just starting beta) you can do this: Actually to correct myself, we just started "feature freeze" for 7.4, with beta planned to start on or about July 15th. Sorry for any confusion caused. Joe ---(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] LEAST and GREATEST functions?
Josh Berkus wrote: create or replace function greatest(anyelement, anyelement) returns anyelement as 'select case when $1 > $2 then $1 else $2 end' language 'sql'; Way cool. I'd have to imagine that it would blow up if you did this, though: select greatest ( 512, now() ); With an "Operator is not defined" error, hey? It errors out with a type mismatch error: regression=# select greatest (512, now()); ERROR: Function greatest(integer, timestamp with time zone) does not exist Unable to identify a function that satisfies the given argument types You may need to add explicit typecasts Of course none of this is documented yet (because I still owe the documentation ;-), but that can be done during feature freeze/beta), but the concept of the anyelement data type is that, although it can mean literally any data type, any arguments (or return type) so defined have to match each other at function call time. So with: greatest(anyelement, anyelement) returns anyelement when it gets called, the two arguments *must* be the same data type, and the function will return the same type. Any arguments declared with a specific datatype (say integer) don't participate in the runtime resolution of the polymorphic arguments. Similarly there is an anyarray data type that is constrained at runtime to be an array of anything that was defined as anyelement; e.g.: create or replace function myelement(anyarray, int) returns anyelement as 'select $1[$2]' language 'sql'; regression=# select myelement(array[11,22,33,44,55], 2); myelement --- 22 (1 row) Joe ---(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] passing a record as a function argument in pl/pgsql
Alon Noy wrote: From what I tried it is possible to create such a function but it is not possible to call it ?! Can anyone provide an example? create table foo (f1 int, f2 text); insert into foo values(1,'a'); insert into foo values(2,'b'); insert into foo values(3,'c'); create or replace function get_foo(int) returns foo as 'select * from foo where f1 = $1' language 'sql'; create or replace function use_foo(foo) returns text as ' declare v_foo alias for $1; begin return v_foo.f2; end; ' language 'plpgsql'; regression=# select use_foo(get_foo(2)); use_foo - b (1 row) HTH, Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] LEAST and GREATEST functions?
Ang Chin Han wrote: Tom Lane wrote: create function greatest(anyelement, anyelement) returns anyelement as 'select case when $1>$2 then $1 else $2 end' language sql; Any chance of this making it into 7.4's contrib? Maybe with enough documentation to make it a tutorial for PostgreSQL's user functions? > >> You do have to create several greatest() functions for different >> numbers of arguments, but not one for each datatype you want to >> handle. > > Insignificant, compared with the flexiblity. I don't think it's worth putting in contrib, but for the archives: create or replace function make_greatest() returns text as ' declare v_args int := 32; v_first text := ''create or replace function greatest(anyelement, anyelement) returns anyelement as select case when $1 > $2 then $1 else $2 end language sql''; v_part1 text := ''create or replace function greatest(anyelement''; v_part2 text := '') returns anyelement as select greatest($1, greatest($2''; v_part3 text := '')) language sql''; v_sql text; begin execute v_first; for i in 3 .. v_args loop v_sql := v_part1; for j in 2 .. i loop v_sql := v_sql || '',anyelement''; end loop; v_sql := v_sql || v_part2; for j in 3 .. i loop v_sql := v_sql || '',$'' || j::text; end loop; v_sql := v_sql || v_part3; execute v_sql; end loop; return ''OK''; end; ' language 'plpgsql'; select make_greatest(); Now you should have 31 "greatest" functions, accepting from 2 to 32 arguments. *Not* heavily tested, but seemed to work for me. regression=# select greatest(112,2,3,4,5,6,7,8,9,10,1234,2,3,4,5,66,7,8,9,10,1,27,3,4,5,6,347,8,9,10,1,2); greatest -- 1234 (1 row) I'll leave "least()" as an exercise for the reader ;-) HTH, Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Database Upgrade scripts (AKA Conditional SQL execution)
Richard Rowell wrote: Can I accomplish this with postgresql without involving an external process (like say perl)? I guess I could put the upgrade stuff into PL/SQL functions and just drop the functions when I'm done, but I was hoping for something a little "cleaner". There is no way (currently at least) to do this without some kind of function. I've done it in the past with PL/pgSQL. We've had brief discussions in the past about how this could be supported, but nothing conclusive, and no one has cared enough to scratch this particular itch. Joe ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Setuid functions
Luis Sousa wrote:
How can I set a function as setuid ?
I take a look at the documetation, on Reference Manual and the only
reference I saw to it was on SET SESSION AUTHORIZATION.
See:
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-createfunction.html
CREATE [ OR REPLACE ] FUNCTION name ( [ argtype [, ...] ] )
RETURNS rettype
{ LANGUAGE langname
| IMMUTABLE | STABLE | VOLATILE
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
| [EXTERNAL] SECURITY INVOKER | [EXTERNAL] SECURITY DEFINER
| AS 'definition'
| AS 'obj_file', 'link_symbol'
} ...
[ WITH ( attribute [, ...] ) ]
[EXTERNAL] SECURITY INVOKER
[EXTERNAL] SECURITY DEFINER
SECURITY INVOKER indicates that the function is to be executed with
the privileges of the user that calls it. That is the default. SECURITY
DEFINER specifies that the function is to be executed with the
privileges of the user that created it.
The key word EXTERNAL is present for SQL compatibility but is
optional since, unlike in SQL, this feature does not only apply to
external functions.
HTH,
Joe
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] Recursive request ...
Benoît Bournon wrote: I have to make a function that returns a tree with title and link of a table. Recursively, a information depends on a parent information. It is to organise a menu with parent dependance. How is it possible and faster ? in C ? pl/pgsql or other ? On 7.3 and later: see contrib/tablefunc. The function is called connectby(). HTH, Joe ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Replacing a simple nested query?
Steve Wampler wrote: I've got a simple nested query: select * from attributes where id in (select id from attributes where (name='obsid') and (value='oid00066')); that performs abysmally. I've heard this described as the 'classic WHERE IN' problem. I may be missing something, but why can't you just do: select * from attributes where name='obsid' and value='oid00066'; ? Joe ---(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] Functional Indexes
David Olbersen wrote:
Now the question: is there a single index I can create that will be
used when my WHERE clause contains either urlhost or urltld? I could
create two functional indexes, but that seems a bit silly to me.
I can't think of how to do only one index in 7.3.x and earlier, but
FWIW, this works in 7.4devel (which should be in beta next Monday):
create or replace function tld(text) returns text as '
select split_part(split_part(substr($1,8,length($1)),''/'',1),''.'',3)
' language 'sql' STRICT IMMUTABLE;
regression=# select tld('http://www.foobar.com/really/long/path/to/a/file');
tld
-
com
(1 row)
create or replace function sld(text) returns text as '
select split_part(split_part(substr($1,8,length($1)),''/'',1),''.'',2)
' language 'sql' STRICT IMMUTABLE;
regression=# select sld('http://www.foobar.com/really/long/path/to/a/file');
sld
foobar
(1 row)
create table urls(f1 text);
insert into urls values('http://www.foobar.com/really/long/path/to/a/file');
create index urls_idx1 on urls(tld(f1),sld(f1));
-- just to see index usage on toy table
set enable_seqscan to off;
regression=# explain analyze select * from urls where tld(f1) = 'com';
QUERY PLAN
--
Index Scan using urls_idx1 on urls (cost=0.00..4.69 rows=1 width=32)
(actual time=0.07..0.07 rows=1 loops=1)
Index Cond: (split_part(split_part(substr(f1, 8, length(f1)),
'/'::text, 1), '.'::text, 3) = 'com'::text)
Total runtime: 0.18 msec
(3 rows)
regression=# explain analyze select * from urls where tld(f1) = 'com'
and sld(f1) = 'foobar';
QUERY PLAN
-
Index Scan using urls_idx1 on urls (cost=0.00..4.70 rows=1 width=32)
(actual time=0.08..0.09 rows=1 loops=1)
Index Cond: ((split_part(split_part(substr(f1, 8, length(f1)),
'/'::text, 1), '.'::text, 3) = 'com'::text) AND
(split_part(split_part(substr(f1, 8, length(f1)), '/'::text, 1),
'.'::text, 2) = 'foobar'::text))
Total runtime: 0.21 msec
(3 rows)
Joe
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] Recursive request ...
BenLaKnet wrote: I see connect by in Oracle ??? is there an equivalent in PostgreSQL or not ?? Someone is working on the SQL99 equivalent, but it isn't done yet. Perhaps for 7.5. Joe ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] casting to arrays
Mike Rylander wrote:
I have a rather odd table structure that I would like to simplify to be a view
(for some definition of simplify). The current idea I have is to shovel
values from multiple rows in one table into an array in the view. The tables
look something like this:
Is anything like this possible? I know this may not be good form, but
unfortunately (or perhaps fortunately, since it means I have a job) there are
business reasons for this, supporting old apps and such.
Not possible in current releases, but it will be in 7.4 (about to start
beta). It looks like this:
create table person (id integer, name varchar);
insert into person values(1,'Bob');
insert into person values(2,'Sue');
create table stuff (person_id integer, stuff_name text);
insert into stuff values(1,'chair');
insert into stuff values(1,'couch');
insert into stuff values(1,'lamp');
insert into stuff values(2,'table');
insert into stuff values(2,'shirt');
create or replace view person_with_stuff as select p.id as id, p.name as
name, ARRAY(select s.stuff_name from stuff s where s.person_id = p.id)
as stuff from person p;
regression=# select * from person_with_stuff;
id | name | stuff
+--+
1 | Bob | {chair,couch,lamp}
2 | Sue | {table,shirt}
(2 rows)
HTH,
Joe
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] casting to arrays
Mike Rylander wrote: Thank you! This is great news. Is there a projected release date for 7.4? Not exactly an officially projected date, but in the past IIRC beta/RC has lasted 2 to 3 months, so I'd start looking for a 7.4 release in October. Also, is there a published roadmap, or should I just get on the developers list? The closest thing is the TODO list: http://developer.postgresql.org/todo.php But if you want to closely monitor the work actually getting done, subscribe to the HACKERS list. Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How access to array component
Cristian Cappo wrote:
>>> select __function(10::int2, 20::int2)[1]
^^^ parsing error.
Try:
create or replace function foo(int2, int2 ) returns _varchar as '
select ''{1,2}''::_varchar
' language 'sql';
regression=# select (foo(10::int2, 20::int2))[1];
foo
-
1
(1 row)
HTH,
Joe
---(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] How access to array component
Cristian Cappo A. wrote: Tried, but... >> select (foo(10::int2,20::int2))[1]; >> ERROR: parser: parse error at or near "[" at character 32 I'm using the version 7.3.3 Sorry, it works on 7.4devel, so I thought it might on 7.3 as well. In any case, this works on 7.3.3: test=# select f1[1] from (select foo(10::int2, 20::int2) as f1) as ss; f1 1 (1 row) HTH, Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] function returning setof performance question
Mark Bronnimann wrote: I was hoping to eliminate the parse call on the view because I was doing the where clause on the view instead of putting the where in the view. In all, I was hoping to keep a single view called from multiple functions with different where clauses. Yep... I shoulda known better... It sounds like you're using a sql function, not a plpgsql function (although I don't think you said either way). If you write the function in plpgsql it will get parsed and cached on the first call in a particular backend session, which *might* give you improved performance on subsequent calls, if there are any; are you using persistent connections? Alternatively, it might work to use a prepared query. Joe ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] [PERFORM] EXTERNAL storage and substring on long strings
Scott Cain wrote: On Mon, 2003-08-04 at 11:53, Tom Lane wrote: I find it really, really hard to believe that a crude reimplementation in plpgsql of the TOAST concept could beat the built-in implementation at all, let alone beat it by two orders of magnitude. Either there's something unrealistic about your testing of the dna_string function, or your original tests are not causing TOAST to be invoked in the expected way, or there's a bug we need to fix. I'd really like to see some profiling of the poor-performing external-storage case, so we can figure out what's going on. I was really hoping for a "Good job and glad to hear it" from you :-) I don't think there is anything unrealistic about my function or its testing, as it is very much along the lines of the types of things we do now. I will really try to do some profiling this week to help figure out what is going on. Is there a sample table schema and dataset available (external-storage case) that we can play with? Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] [PERFORM] EXTERNAL storage and substring on long strings
Scott Cain wrote: Oh, and I forgot to mention: it's highly compressed (bzip2 -9) and is 109M. Thanks. I'll grab a copy from home later today and see if I can find some time to poke at it. Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Comparing arrays
Bertrand Petit wrote:
In two table I store "objects" and their attributes. The
attributes, which are not meant to be searched, are stored as unbound
arrays of varchars. I have a query that needs to use those attributes
on both sides of an EXCEPT statement:
SELECT left.id, left.attribs FROM left
EXCEPT SELECT right.id, right.attribs FROM right;
That query can't be planed because of the following error: "Unable to
identify an ordering operator '<' for type 'character varying[]'".
This should work on 7.4 beta.
create table myleft (id int, attribs varchar[]);
insert into myleft values(1,'{a,b}');
insert into myleft values(2,'{c,d}');
insert into myleft values(3,'{e,f}');
create table myright (id int, attribs varchar[]);
insert into myright values(2,'{c,d}');
regression=# SELECT myleft.id, myleft.attribs FROM myleft EXCEPT SELECT
myright.id, myright.attribs FROM myright;
id | attribs
+-
1 | {a,b}
3 | {e,f}
(2 rows)
I thought that I could build such an operator using PL/pgSQL,
unfortunately this language can't receive arguments of type ANYARRAY.
This also works on 7.4 beta.
So this led me to the creation of a new ATTRIBUTES data type, the
should be acceptable as an argument to a PL/pgSQL procedure.
Why do that -- I thought your data was in varchar[]? PL/pgSQL can have
varchar[] as an argument in 7.3.
test=# create or replace function testplpgsql(varchar[], int) returns
varchar as 'begin return $1[$2]; end;' language plpgsql;
CREATE FUNCTION
test=# select testplpgsql('{a}'::varchar[],1);
testplpgsql
-
a
(1 row)
test=# select testplpgsql('{a,b}'::varchar[],2);
testplpgsql
-
b
(1 row)
test=# select version();
version
-
PostgreSQL 7.3.3 on i686-redhat-linux-gnu, compiled by GCC 2.96
(1 row)
In any case, 7.3 and before have many issues with effective use of
arrays. For instance, even if you got all this working, you'd find
working with arrays in PL/pgSQL painful if not impossible.
If you really depend on arrays, I'd strongly suggest testing on 7.4 beta
and moving to 7.4 as soon as it is released.
HTH
Joe
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] "SELECT IN" Still Broken in 7.4b
Mike Winter wrote: On Wed, 20 Aug 2003, Rod Taylor wrote: Ensure your IN list is unique. You might find better times by through an indexed temp table. That is what I ended up doing, but it's not a very elegant solution. MySQL does queries of this type orders of magnitudes faster than Postgres on large value lists, although I have no specific algorithmic solutions to offer for how to make it faster. I don't believe making the IN lists unique has any affect on performance. I have no idea whether it will be better or worse performance, but in 7.4 you could do: select blah from foo where id = any (ARRAY[list_of_literals]); Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Table conversion query...
George McQuade wrote: date tran glamt 08/20/03 1001 3010 -30.00 08/20/03 1001 1030 -300.00 08/20/03 1001 1060 +330.00 08/20/03 1002 ...next transaction ... and I need to convert to: date glcr gldb amt 08/20/03 1060 3010 30.00 08/20/03 1060 1030 300.00 in other words, the negative gl's go into gldb and they make up the total for the positive gl. is there a way to accomplish this in postgresql? or should I implement it inside the java app? There's no simple way to do this in Postgres. You could do it with a PL/pgSQL table function, or for better performance a C function. There are a couple of questions yet to be answered though: 1) Can there ever be more than one credit account, e.g. -30, -300, +150, +180? 2) What happens if sum(neg values) != sum(pos values)? Throw an error? Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Table conversion query...
George McQuade wrote:
Interesting, my C is gone a long time ago. Would the
table function be fairly complex for someone who's
never done one?
I'm tempted by the java option, but initial jdbc speed
tests don't look very promising (the avg file has
1/2 million records).
Well this is a fairly complex problem regardless of the language. You
need to be able to accumulate output rows for an arbitrary number of
different debit gls, flush them out whenever the transaction id changes,
and be sure that they reconcile with the credit.
I'm actually not sure you can do this in plpgsql in Postgres 7.3.x -- in
7.4 you could use arrays to accumulate the debit data. Here is a lightly
tested (no warranty, might not work correctly, use at your own risk etc,
etc ;-) ) plpgsql function which seems to work as you want it. Consider
it a starting point, but only if you can use 7.4beta:
create table gl (transdate date, tran int, gl int, amt numeric(9,2));
insert into gl values('08/20/03',1001,3010,-30.00);
insert into gl values('08/20/03',1001,1030,-300.00);
insert into gl values('08/20/03',1001,1060,330.00);
insert into gl values('08/21/03',1002,3010,-30.00);
insert into gl values('08/21/03',1002,1030,-200.00);
insert into gl values('08/21/03',1002,3010,-100.00);
insert into gl values('08/21/03',1002,1060,330.00);
create type reconcile_type as (transdate date, glcr int, gldb int, amt
numeric(9,2));
create or replace function reconcile(text) returns setof reconcile_type as '
declare
v_crit alias for $1;
v_sql text;
v_last_transdate date;
v_last_tran int := 0;
v_last_glcr int := 0;
v_last_glcr_amt numeric(9,2) := 0;
v_last_gldb int[] := ''{}'';
v_last_gldb_amt numeric(9,2)[] := ''{}'';
v_sum_debit numeric(9,2) := 0;
v_glcr_found bool := false;
rec record;
result reconcile_type%rowtype;
i int;
ub int;
begin
if v_crit is not null then
v_sql := ''select transdate, tran, gl, sum(amt) as amt from gl where ''
|| v_crit ||
'' group by transdate, tran, gl order by 2,4 desc,1,3'';
else
v_sql := ''select transdate, tran, gl, sum(amt) as amt from gl '' ||
''group by transdate, tran, gl order by 2,4 desc,1,3'';
end if;
for rec in execute v_sql loop
if rec.tran != v_last_tran then
-- starting a new tran
if v_glcr_found = true then
-- time to flush rows, but did we reconcile
if v_sum_debit != -(v_last_glcr_amt) then
RAISE EXCEPTION ''credits and debits do not reconcile'';
end if;
-- flush accumulated results, looping over gldb arrays
ub := array_upper(v_last_gldb, 1);
for i in 1..ub loop
result.transdate := v_last_transdate;
result.glcr := v_last_glcr;
result.gldb := v_last_gldb[i];
result.amt := v_last_gldb_amt[i];
return next result;
end loop;
end if;
-- first pass for this tran -- it better be a credit
if rec.amt > 0 then
v_glcr_found := true;
else
RAISE EXCEPTION ''no credit found for transaction: %'', rec.tran;
end if;
v_last_tran := rec.tran;
v_last_transdate := rec.transdate;
v_last_glcr := rec.gl;
v_last_glcr_amt := rec.amt;
v_last_gldb := ''{}'';
v_last_gldb_amt := ''{}'';
v_sum_debit := 0;
else
-- not a new tran
if rec.amt > 0 then
-- if we have already visited the credit, and we see another,
cry foul
RAISE EXCEPTION ''Two credits found for transaction: %'', rec.tran;
else
-- otherwise accumulate the debit
v_last_gldb := v_last_gldb || rec.gl;
v_last_gldb_amt := v_last_gldb_amt || rec.amt;
v_sum_debit := v_sum_debit + rec.amt;
end if;
end if;
end loop;
-- need this to get the last (or only) trans
if v_glcr_found = true then
-- time to flush rows, but did we reconcile
if v_sum_debit != -(v_last_glcr_amt) then
RAISE EXCEPTION ''credits and debits do not reconcile'';
end if;
-- flush accumulated results, looping over gldb arrays
ub := array_upper(v_last_gldb, 1);
for i in 1..ub loop
result.transdate := v_last_transdate;
result.glcr := v_last_glcr;
result.gldb := v_last_gldb[i];
result.amt := v_last_gldb_amt[i];
return next result;
end loop;
end if;
return;
end;
' language plpgsql;
regression=# select * from reconcile(null);
transdate | glcr | gldb | amt
+--+--+-
2003-08-20 | 1060 | 3010 | -30.00
2003-08-20 | 1060 | 1030 | -300.00
2003-08-21 | 1060 | 3010 | -130.00
2003-08-21 | 1060 | 1030 | -200.00
(4 rows)
You could do similar a C function in 7.3.x. I'm not sure how you'd write
this in 7.3.x plpgsql though :(
HTH,
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] How to return a record set from function.
Kumar wrote: Create table t1 (c1 int, c2 varchar, c3 varchar); Create or Replace function sel_t1 () returns setof records as ' select c1, c2, c3 from t1; ' Language SQL; It was fine and created a function. while i execute it as select sel_t1; I got the following error. ERROR: Cannot display a value of type RECORD I see three problems. 1) you need parenthesis on the function call, i.e. "sel_t1()" as compared with "sel_t1" 2) when returning setof record, the "sel_t1()" must be in the FROM clause of the statement 3) when the function is declared as returning "record" as compared to a named complex type such as "t1", you need to include a column definition list in the SQL statement So, putting it all together, try something like this: SELECT f1, f2, f3 FROM sel_t1() AS (f1 int, f2 varchar, f3 varchar); See: http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=xfunc-tablefunctions.html and http://techdocs.postgresql.org/guides/SetReturningFunctions HTH, Joe ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] length of array
Chris Faulkner wrote: Is there a function in postgres to return the length of an array field ? I have seen array_dims(array) but this returns a character value. Ideally, I'd like something numeric returned. Afraid not in 7.3.x or earlier. In 7.3.x (but not earlier) you *can* do this (for a one-dimensional array at least): SELECT replace(split_part(array_dims(array_fld),':',1),'[','')::int as low FROM tbl; SELECT replace(split_part(array_dims(array_fld),':',2),']','')::int as high FROM tbl; In 7.4 (now in beta) there are two new functions, array_lower() and array_upper() that do what you're looking for: regression=# select array_lower(array_fld, 1) from tbl; array_lower - 1 (1 row) regression=# select array_upper(array_fld, 1) from tbl; array_upper - 2 (1 row) See the following links for more on 7.4's array support: http://developer.postgresql.org/docs/postgres/arrays.html http://developer.postgresql.org/docs/postgres/functions-array.html http://developer.postgresql.org/docs/postgres/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS HTH, Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] length of array
Chris Faulkner wrote:
I would like to do it like this in Oracle
select field from table N where [conditions]
and NVL(N.level[1],0) = 0
and NVL(N.level[2],0) = 0
and NVL(N.level[3],0) = 0
and NVL(N.level[4],0) = 0
So if a row only has two elements in the array, but the first two both had
values "0", then the row would return. At the moment, I have this :
and N.level[1] = 0
and N.level[2] = 0
and N.level[3] = 0
and N.level[4] = 0
but my row with 2 elements in the array won't be returned with this
condition.
Is this what you're looking for?
regression=# select * from t1;
id |f1
+---
1 | {1,2}
2 | {0,0,0}
3 | {0,0,0,0}
3 | {1,2,3,0}
(4 rows)
regression=# SELECT * FROM t1 WHERE COALESCE(f1[1],0) = 0 and
COALESCE(f1[2],0) = 0 and COALESCE(f1[3],0) = 0 and COALESCE(f1[4],0) = 0;
id |f1
+---
2 | {0,0,0}
3 | {0,0,0,0}
(2 rows)
Joe
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Oracle 'connect by prior' now eaiser in 7.3?
Merrall, Graeme wrote: Am I right in thinking that recursive procedures and procs returning row sets would allow us to better emulate this behaviour? As anyone looked at it yet? See connectby() in contrib/tablefunc. Someone was working on SQL99 recursive queries but it didn't get done for 7.4 -- perhaps it will be in 7.5. In the meantime, connectby() is in 7.3 and might work for you. HTH, Joe ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Mystery function error
Richard Sydney-Smith wrote: CREATE OR REPLACE FUNCTION public.locate(bpchar, bpchar) RETURNS int4 AS ' -- search for the position of $2 in $1 declare srcstr alias for $1; searchstr alias for $2; begin return position(searchstr in srcstr); ' LANGUAGE 'plpgsql' VOLATILE; You are missing the "end" keyword in there. Also, I'd think this function is IMMUTABLE not VOLATILE. CREATE OR REPLACE FUNCTION public.locate(bpchar, bpchar) RETURNS int4 AS ' -- search for the position of $2 in $1 declare srcstr alias for $1; searchstr alias for $2; begin return position(searchstr in srcstr); end; ' LANGUAGE 'plpgsql' IMMUTABLE; This could also be done as: CREATE OR REPLACE FUNCTION public.locate(bpchar, bpchar) RETURNS int4 AS ' select position($2 in $1) ' LANGUAGE 'sql'; HTH, Joe ---(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] Mystery function error
Tom Lane wrote:
Josh Berkus <[EMAIL PROTECTED]> writes:
Also, this will run faster if you do it as a SQL function:
CREATE FUNCTION locate ( text, text ) RETURNS INT AS '
SELECT POSITION($2, $1);
' LANGUAGE SQL IMMUTABLE STRICT;
This is definitely the solution I'd recommend for 7.4 (because 7.4 would
inline the SQL function definition, resulting in zero runtime overhead).
In 7.3 I suspect the plpgsql version might be a tad faster, or anyway
comparable. Has anyone done any head-to-head performance comparisons
on such simple functions?
I did a quick check last night on 7.3.4 and found that plpgsql was faster:
CREATE OR REPLACE FUNCTION public.locate(bpchar, bpchar) RETURNS int4 AS '
-- search for the position of $2 in $1
declare
srcstr alias for $1;
searchstr alias for $2;
begin
return position(searchstr in srcstr);
end;
' LANGUAGE 'plpgsql' IMMUTABLE;
regression=# explain analyze select locate('abc','b');
QUERY PLAN
--
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.01..0.01
rows=1 loops=1)
Total runtime: 0.03 msec
(2 rows)
DROP FUNCTION public.locate(bpchar, bpchar);
CREATE OR REPLACE FUNCTION public.locate(bpchar, bpchar) RETURNS int4 AS '
select position($2 in $1)
' LANGUAGE 'sql';
regression=# explain analyze select locate('abc','b');
QUERY PLAN
--
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.24..0.24
rows=1 loops=1)
Total runtime: 0.26 msec
(2 rows)
On 7.4 (different hardware), I get this:
plpgsql
regression=# explain analyze select locate('abc','b');
QUERY PLAN
--
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.00..0.00
rows=1 loops=1)
Total runtime: 0.05 msec
(2 rows)
sql
regression=# explain analyze select locate('abc','b');
QUERY PLAN
--
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.01..0.01
rows=1 loops=1)
Total runtime: 0.03 msec
(2 rows)
Joe
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Oracle 'connect by prior' now eaiser in 7.3?
Thomas Wegner wrote:
SELECT "ID_WINUSER" FROM connectby('"WINUSER"', '"ID_WINUSER"',
'"REF_ID_WINUSER"', 4, 0, '~') AS t("ID_WINUSER" integer)
and get this error:
ERROR: Query-specified return tuple not valid for Connectby: wrong number
of columns
Please see the documentation (README.tablefunc). You need to properly
specify the column definitions in the FROM clause, i.e. (untested):
SELECT "ID_WINUSER" FROM
connectby('"WINUSER"', '"ID_WINUSER"','"REF_ID_WINUSER"', 4, 0, '~')
AS t("ID_WINUSER" integer,
"REF_ID_WINUSER" integer,
level integer,
branch text);
HTH,
Joe
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Which is faster SQL or PL/PGSQL
Richard Huxton wrote: So - gain by not re-planning on every call, but maybe lose because your plan is not so precise. Of course, any queries you build dynamically and run via EXECUTE will have to be planned each time. This question gets even more complex in 7.4, where many simple SQL functions will get inlined, and library preloading is available to speed that first PL/pgSQL call. I think the best way to answer this question for any particular function is to try it both ways (if the function *can* be written as a SQL function) and see for yourself. And if you do this in 7.3, redo it when you upgrade to 7.4. HTH, Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Which is faster SQL or PL/PGSQL
Richard Huxton wrote: On Monday 20 October 2003 18:24, Joe Conway wrote: This question gets even more complex in 7.4, where many simple SQL functions will get inlined, and library preloading is available to speed that first PL/pgSQL call. What will be the effects of inlining? Does it mean the planner merges the function's plan into the larger query? Yes, I believe so (well, actually the optimizer). An inlined SQL function ends up behaving like a macro that expands at run time and is therefore quite fast -- no function call overhead at all. Here is the comment from the source (src/backend/optimizer/util/clauses.c): /* * inline_function: try to expand a function call inline * * If the function is a sufficiently simple SQL-language function * (just "SELECT expression"), then we can inline it and avoid the * rather high per-call overhead of SQL functions. Furthermore, this * can expose opportunities for constant-folding within the function * expression. * * We have to beware of some special cases however. A directly or * indirectly recursive function would cause us to recurse forever, * so we keep track of which functions we are already expanding and * do not re-expand them. Also, if a parameter is used more than once * in the SQL-function body, we require it not to contain any volatile * functions (volatiles might deliver inconsistent answers) nor to be * unreasonably expensive to evaluate. The expensiveness check not only * prevents us from doing multiple evaluations of an expensive parameter * at runtime, but is a safety value to limit growth of an expression * due to repeated inlining. * * We must also beware of changing the volatility or strictness status * of functions by inlining them. * * Returns a simplified expression if successful, or NULL if cannot * simplify the function. */ Joe ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Which is faster SQL or PL/PGSQL
Josh Berkus wrote: Yes, I believe so (well, actually the optimizer). An inlined SQL function ends up behaving like a macro that expands at run time and is therefore quite fast -- no function call overhead at all. ... but only in 7.4. In 7.2 and I think in 7.3 this was not implemented. Yeah, that's what my original post said ;-) While we're on the topic, anyone know any good ways to speed up EXECUTE statements in PL/pgSQL functions? Never tried it, but is it possible to use a prepared statement inside a PL/pgSQL function? In any case, you can in other PLs. And with library preloading (starting in 7.4), the first call to other PLs is similar to that of PL/pgSQL. See: http://archives.postgresql.org/pgsql-patches/2003-07/msg00239.php Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Crosstab question
Adam Witney wrote:
The missing values seemed to have been ignored and so the data is being
shifted to the left and so put in the wrong columns.
Am I using this function correctly? What is supposed to happen with missing
values?
Yeah, that's a limitation of the version of crosstab distributed with
7.3. And (believe it or not) I've actually found cases where that
behavior is useful. There is another version of crosstab ("hashed"
crosstab) that treats missing values the way you expect. You can grab a
copy backported for 7.3 here:
http://www.joeconway.com/
You want the one that says: "contrib/tablefunc backported for PostgreSQL
7.3.x -- sync'd with CVS HEAD 02-Oct-2003"
HTH,
Joe
---(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] Referring to derived column name in a RECORD
David B wrote: SELECT to_char( created_timestamp, 'DDMMYY' ) AS "joined_on", r_app.joined_on ; -- HOW do I reference this value?...this does not work Try either making that first line: ... AS joined_on, (i.e. without the double quotes) or make the second one: r_app."joined_on"; (i.e. with double quotes) HTH, Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] connectby
BenLaKnet wrote: I use postgresql 7.2.3 How can I use connectby ?? Must I install files ? or packages ? or it is recommanded to upgrade dataserver ? You need to upgrade. Either install 7.3.4 or wait a few weeks and install 7.4 when it is released. Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Query Problem
Abdul Wahab Dahalan wrote:
If I've a table like below.
kk kjpngkvote
01 02 c 10
01 02 b 5
How do I make a query so that I can get a result
like this?
kk kjpngkvote
01 02c,b 15
create or replace function accum_text(text, text) returns text as
'select case when $1 = then $2 else $1 || '','' || $2 end' language
sql;
CREATE AGGREGATE concat(BASETYPE = text, SFUNC = accum_text, STYPE =
text, INITCOND = '');
create table t(kk text, kj text, pngk text, vote int);
insert into t values('01','02','c',10);
insert into t values('01','02','b',5);
regression=# select kk, kj, concat(pngk), sum(vote) from t group by kk, kj;
kk | kj | concat | sum
+++-
01 | 02 | c,b| 15
(1 row)
HTH,
Joe
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Equivalent of Reverse() functions
Kumar wrote:
I am migrating an SQL Server 2000 database to Postgres 7.3.4 running
on RH Linux 7.2. While migrating I encounter SQL Server REVERSE( )
function, seems no such functions at Postgres.
Is there a equivalent function available at Postgres? Please shed
some light
How about:
create or replace function reverse(text) returns text as '
return reverse($_[0]);
' language plperl;
regression=# select reverse('abcdef');
reverse
-
fedcba
(1 row)
Joe
---(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] Scaler forms as function arguments
Andreas Tille wrote: test=# select PLpgSQLDepartmentSalaries() ; This should be: regression=# select * from PLpgSQLDepartmentSalaries(); departmentid | totalsalary --+- 1 | 8 2 | 6 (2 rows) HTH, Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Scaler forms as function arguments
Richard Huxton wrote: On Wednesday 26 November 2003 15:40, Andreas Tille wrote: I want to write a function of the following type CREATE FUNCTION test ( ) RETURNS setof MyTable AS 'SELECT * FROM MyTable WHERE id IN $1' LANGUAGE 'SQL' ; Not as you've done it. You could pass in text "(1,2,3)", build your query and use EXECUTE to execute it. Alternatively, you might be able to do it with an array parameter (sorry, I don't use arrays, so I can't be sure). In 7.4 you could use an array. It would look like this: CREATE TABLE mytable (id int, idval text); INSERT INTO mytable VALUES (1,'a'); INSERT INTO mytable VALUES (2,'b'); INSERT INTO mytable VALUES (3,'c'); CREATE FUNCTION test (int[]) RETURNS setof MyTable AS ' SELECT * FROM mytable WHERE id = ANY ($1) ' LANGUAGE 'SQL' ; regression=# SELECT * FROM test(ARRAY[1,3]); id | idval +--- 1 | a 3 | c (2 rows) HTH, Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Seq Scans when index expected to be used
jasiek wrote: On Sat, 29 Nov 2003 08:49:24 -0800 (PST), ow wrote explain select b, c, a from test group by b, c, a having count(*) > 1 Why would you expect this to use an index scan when it needs to read the entire table? If you read the whole table (or even a significant fraction of it), a seq scan is faster. Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
